Basic Result Set Operations
Result set operations involve creating a result set, reading from it, and then closing it. This section discusses the basic ways to work with result sets.
If you find that the examples in this section do not work as shown, you may need to install or restore the example database with the DatabaseExamples` package.
The query argument to SQLResultSetOpen is a function that selects data, using either SQLSelect or SQLExecute. Here is an example.
First, the DatabaseLink package is loaded and a connection is made to the publisher example database.
You can use this connection to read eight rows from the ROYSCHED table.
You can also obtain a result set from the same query.
SQLResultSetRead reads from the result set and returns the rows that were read. After reading from a result set, the next read will read the next row. The following example reads a single row. Since the result set was just created, it reads the first row.
The following reads the second and third rows.
By default SQLResultSetRead maps data types into various Mathematica expressions. However, setting the option GetAsStrings to True gets results as string expressions.
If you want to process each row individually, you can use a construct like the following. It reads the remaining rows and sums the last element of each row. Since there were eight rows in the result set and four had already been read, this operation will read four rows. When SQLResultSetRead returns something that is not a list you have reached the end of the result set.
If you call SQLResultSetRead again it will return Null because the end of the result set has been reached.
This closes the result set and the SQL connection.