dbNextResult-methods {RMySQL}R Documentation

Fetch Next Result Set from Multiple Statements or Stored Procedures

Description

dbMoreResults checks whether there are additional result sets for processing. dbNextResult fetches the next result set.

Methods

These MySQL methods provide functionality to sequentially extract multiple results produced by SQL scripts or stored procedures.

In the case of stored procedures invoked with CALL, the first result set indicates the call status, and output data (if any) are return as additional result sets.

con = "MySQLConnection"
a MySQL connection object.

Note

MySQL supports SQL scripts (a single string with multiple statements terminated by ';') from version 4.1.1 onwards and stored procedures from version 5.0.

To process SQL scripts on a MySQL connection, the connection must be created using the CLIENT\_MULTI\_STATEMENTS. In addition, to process stored procedures that return one or more result sets, the connection must be created using the CLIENT\_MULTI\_RESULTS client flag.

For simplicity, use CLIENT\_MULTI\_STATEMENTS for working with either SQL scripts or stored procedures. For more details, read on.

More precisely, to execute multiple statements the connection needs CLIENT\_MULTI\_STATEMENTS; this in turn automatically enables CLIENT\_MULTI\_RESULTS for fetching of multiple output results. On the other hand, the client flag CLIENT\_MULTI\_RESULTS by itself enables stored procedures to return one or more results. See the MySQL documentation in www.mysql.com for full details.

See Also

MySQL, dbConnect, dbSendQuery, dbHasCompleted, fetch, dbCommit, dbGetInfo, dbReadTable.

Examples

## Not run: 
con <- dbConnect(MySQL(), 
          dbname = "rs-dbi", 
          client.flag=CLIENT\_MULTI\_STATEMENTS)
sql.script <- paste(
   "select * from abc",
   "select * def", 
   collapse = ";")

rs1 <- dbSendQuery(con, sql.script)
data1 <- fetch(rs1, n = -1)

if(dbMoreResults(con)){
   rs2 <- dbNextResult(con)
   ## you could use dbHasCompleted(rs2) to determine whether
   ## rs2 is a select-like that generates output or not.
   data2 <- fetch(rs2, n = -1)   
   }
## End(Not run)

[Package RMySQL version 0.6-0 Index]