dbWriteTable, dbGetQuery will close other recordsets created by dbSendQuery

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Open a result set by dbSendQuery():

     res <- dbSendQuery(con, "SELECT * FROM junk");
     a <- fetch(con, 1);
        # returns the first row of the result set

2. Process the record, then save it in a table via dbGetQuery or dbWriteTable:
    aa <- ....
    dbWriteTable(con, aa)

3. Try reading the next record from the result set

    a <- fetch(con,1);

This returns an error:

Error in postgresqlFetch(res, n, ...) : 
  RS-DBI driver: (internal error in RS_DBI_getResultSet: could not find resultSet in connection)

Clearly the resultSet has been closed by the dbWriteTable().  The same problem 
occurs if you call dbGetQuery() on the same connection as the dbSendQuery() 
used.

The workaround is to use dbConnect to create separate connections for 
dbSendQuery() and for dbWriteTable/dbGetQuery.

What is the expected output? What do you see instead?

I would have expected to be able to use a single connection for a loop which 
reads batches of data via dbSendQuery(), then writes the output of that batch 
via dbGetQuery()

What version of the product are you using? On what operating system?

Windows 7, R 2.9.2, RPostgreSQL 0.4, Eclipse IDE 4.2.1 with StatET

Original issue reported on code.google.com by bmusi...@aptecgroup.com on 24 May 2013 at 12:28

[GoogleCodeExporter]

Examining the source code, the dbGetQuery() command closes all open result sets 
before it executes the SQL command.  I can't understand why this should be 
necessary.

Original comment by bmusi...@aptecgroup.com on 24 May 2013 at 3:44

[GoogleCodeExporter]

It is just simple. Similar to PQsendQuery, you have to pull all the results 
before sending another query.

Original comment by tomoa...@kenroku.kanazawa-u.ac.jp on 12 Jun 2013 at 12:25

• Added labels: Priority-Low, Type-Other
• Removed labels: Priority-Medium, Type-Defect

[GoogleCodeExporter]

Original comment by tomoa...@kenroku.kanazawa-u.ac.jp on 24 Jul 2013 at 3:08

• Changed state: Invalid