| dbNextResult-methods {RMySQL} | R Documentation |
dbMoreResults checks whether there are additional result sets for
processing. dbNextResult fetches the next result set.
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.
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.
MySQL,
dbConnect,
dbSendQuery,
dbHasCompleted,
fetch,
dbCommit,
dbGetInfo,
dbReadTable.
## 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)