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]