fetch {RMySQL}R Documentation

Fetch records from a previously executed SELECT statement

Description

Fetch records from a previously executed SELECT statement

Usage

fetch(res, n, ...)

Arguments

resultSet a resultSet object. This object needs to be the result of a SELECT or SELECT-like statement, as produced by dbExec. or dbExecStatement. SQL statements such as INSERT, DELETE, do not create result sets.
n maximum number of records to retrieve per fetch. Use n = -1 to retrieve all pending records. Some implementations may recognize other special values (RS-MySQL interprets n = 0 to mean "use whatever default was set in the call to MySQL).
... any other database-engine specific arguments.

Details

See the notes for the various database server implementations.

Value

a data.frame with as many rows as records were fetched and as many columns as fields in the result set.

Side Effects

As the R/S client fetches records the remote database server updates its cursor accordingly.

Note

Make sure you close the result set with close(resultSet) as soon as you finish retrieving the records you want.

References

See the Omega Project for Statistical Computing (http://www.omegahat.org) for details on the R/S interface to databases.

See Also

On database managers:

dbManager MySQL load unload

On connections, SQL statements and resultSets:

dbExecStatement dbExec fetch quickSQL

On transaction management:

commit rollback

On meta-data:

describe getVersion getDatabases getTables getFields getCurrentDatabase getTableIndices getException getStatement hasCompleted getRowCount getAffectedRows getNullOk getInfo

Examples

# Run an SQL statement by creating first a resultSet object
> m <- MySQL()
> con <- dbConnect(m)    
> rs <- dbExecStatement(con, 
         statement = "SELECT w.laser_id, w.wavelength, p.cut_off 
                      FROM WL w, PURGE P
                      WHERE w.laser_id = p.laser_id
                      ORDER BY w.laser_id")
> rs
MySQLResultSet id = (12629,1,3)

# we now fetch the first 100 records from the restulSet into a data.frame
> data1 <- fetch(rs, n = 100)   
> dim(data)
[1] 10  18

> hasCompleted(rs)
[1] 0

# let's get all remaining records
> data2 <- fetch(rs, n = -1)
[Package Contents]