Result Sets

When many rows of data are returned from a database query, a significant amount of memory may be required to hold the result. If all of the data does not need to be available at the same time, it might be preferable to get the result row by row or a few rows at a time. Rows can then be processed individually or in small groups. This functionality is provided by the SQL result set functions of DatabaseLink.

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 "DatabaseLink`DatabaseExamples`" package, as described in "Using the Example Databases".

Basic result set functions.

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 Wolfram Language 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.

SQLResultSet Options

SQLResultSetOpen takes an option, "Mode", that controls movement in the result set and whether the result is sensitive to changes in the database.

First, the DatabaseLink package is loaded and a connection is made to the publisher example database.

This opens a result set, but you can only move forward in this result set.

Settings of the Mode option of SQLResultSetOpen.

The "ForwardOnly" setting of the "Mode" option means that you can only move forward in the result set and the result set is insensitive to any changes to the database after the result set has been created.

The "ScrollInsensitive" setting of the "Mode" option means that you can move forward and backward in the result set and the result set is insensitive to any changes to the database after the result set has been created.

The "ScrollSensitive" setting of the "Mode" option means that you can move forward and backward in the result set and the result set is sensitive to any changes to the database after the result set has been created.

You should note that not all databases support moving backward in the result set or can detect changes in the data.

In addition, you use SetOptions to change the options of a result set after it has been created. The following sets the direction in which it is expected that a result will be retrieved from the result set. This helps the driver to optimize retrieval of data.

SQLResultSet options.

Result Set Positions

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 "DatabaseLink`DatabaseExamples`" package, as described in "Using the Example Databases".

Result set position functions.

A result set is created from a database query, and it can be seen as an array of the rows that match the query. The array actually has two extra positions, one before the first row and one after the last row. When the result set is created, its current position is before the first row.

This loads DatabaseLink and creates a result set from a query to the publisher database.

The position is 0, which means that the current position is before the first row.

If a read is done at the current position, the result is $Failed because there is nothing to read before the first row.

The following shifts the result set by two. The result is True, which means that there is something to read at the new position.

The result set is now positioned at the second row.

The following reads the row at the current position.

By default SQLResultSetCurrent maps data types into various Wolfram Language expressions. However, setting the option "GetAsStrings" to True gets results as string expressions.

Now an absolute move is carried out to the eighth row. The result is True, which tells you there is something to be read.

This reads the last row in the result set.

Now a shift of one is done and the result is False. This means that there is nothing to be read from this position.

The current position is nine, which means that the current position is after the last row.

If a read is done the result is $Failed; there is nothing to read after the last row.

SQLResultSetShift can take a negative shift. If the result set allows moving backward, this will shift backward. SQLResultSetGoto also can take negative settings; these are interpreted as counting from the end of the result set. The following table summarizes how various arguments work.

Examples of result set position functions.

This closes the result set and the SQL connection.

SQLResultSetRead[rs] can be seen as equivalent to SQLResultSetShift[rs,1];SQLResultSetCurrent[rs].

Advanced Result Set Operations

This section discusses advanced result set operations.

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 "DatabaseLink`DatabaseExamples`" package, as described in "Using the Example Databases".

Advanced result set functions.

This loads DatabaseLink and creates a result set from a query to the publisher database.

This shows the rows that are in the result set.

The following gets rows two through four.

After the read, the position is at the fourth row.

SQLResultSetTake can take from the end of the result set. The following reads the last three rows of the result set.

SQLResultSetRead can also take a negative number. This means that it shifts one position to the left and reads. This is repeated till the requested number has been read. The following goes to the end of the result set and then reads the previous four rows.

After the read, the current position is the last thing that was read.

By default SQLResultSetTake maps data types into various Wolfram Language expressions. However, setting the option "GetAsStrings" to True gets results as string expressions.

Finally, you can get the names of the columns in a result set by using SQLResultSetColumnNames.

This closes the result set and the SQL connection.

Result Set Examples

This section discusses common examples of result set operations.

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 "DatabaseLink`DatabaseExamples`" package, as described in "Using the Example Databases".

This shows the rows that are in the result set.

One common operation is to iterate over all the rows, operating on each of the rows one at a time. The following example sums the last element of each row.

The following resets the result set to the beginning.

This example extracts every second row of the result set. It does this by shifting and reading the current row. The result is formed by using Reap and Sow.

This closes the result set and the SQL connection.