RMySQL (version 0.9-3)

dbNextResult-methods: 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.

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

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)   
   }