Result Sets
This feature is not supported on the Wolfram Cloud.

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

SQLResultSetOpen[query]create an SQL result set based on query
SQLResultSetOpen[query,opts]create an SQL result set using options opts
SQLResultSetRead[rs]read a row from result set rs
SQLResultSetRead[rs,num]read num rows from result set rs
SQLResultSetClose[rs]close result set rs

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.

In[1]:=
Click for copyable input

You can use this connection to read eight rows from the ROYSCHED table.

In[2]:=
Click for copyable input
Out[2]//TableForm=

You can also obtain a result set from the same query.

In[3]:=
Click for copyable input
Out[3]=

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.

In[4]:=
Click for copyable input
Out[4]=

The following reads the second and third rows.

In[5]:=
Click for copyable input
Out[5]=

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

In[6]:=
Click for copyable input
Out[6]//InputForm=

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.

In[7]:=
Click for copyable input
Out[8]=

If you call SQLResultSetRead again, it will return Null because the end of the result set has been reached.

In[9]:=
Click for copyable input

This closes the result set and the SQL connection.

In[10]:=
Click for copyable input

SQLResultSet Options

SQLResultSetOpen takes an option, , 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.

In[12]:=
Click for copyable input

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

In[14]:=
Click for copyable input
Out[14]=
setting
description
"ForwardOnly"only moving forward is possible
"ScrollInsensitive"moving forward and backward is possible and result set does not pick up changes to the database
"ScrollSensitive"moving forward and backward is possible and result set picks up changes to the database

Settings of the option of SQLResultSetOpen.

The setting of the 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 setting of the 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 setting of the 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.

In[15]:=
Click for copyable input
Out[15]=
setting
description
"FetchDirection"give a hint as to the direction in which rows will be processed
"FetchSize"give a hint as to the number of rows that should be fetched from the database

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

SQLResultSetShift[rs,num]shift current position by num in result set rs
SQLResultSetGoto[rs,num]move current position to num in result set rs
SQLResultSetPosition[rs]return current position in result set rs
SQLResultSetCurrent[rs]read the row at the current position in result set rs

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.

In[16]:=
Click for copyable input
Out[18]=

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

In[19]:=
Click for copyable input
Out[19]=

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

In[20]:=
Click for copyable input
Out[20]=

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

In[21]:=
Click for copyable input
Out[21]=

The result set is now positioned at the second row.

In[22]:=
Click for copyable input
Out[22]=

The following reads the row at the current position.

In[23]:=
Click for copyable input
Out[23]=

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

In[24]:=
Click for copyable input
Out[24]//InputForm=

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

In[25]:=
Click for copyable input
Out[25]=

This reads the last row in the result set.

In[26]:=
Click for copyable input
Out[26]=

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

In[27]:=
Click for copyable input
Out[27]=

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

In[28]:=
Click for copyable input
Out[28]=

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

In[29]:=
Click for copyable input
Out[29]=

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.

SQLResultSetShift[rs,-num]shift num positions to the left in the result set rs
SQLResultSetGoto[rs,0]move to before the first row in the result set rs
SQLResultSetGoto[rs,3]move to the third row in the result set rs
SQLResultSetGoto[rs,-2]move to the second row from the end in the result set rs
SQLResultSetGoto[rs,-1]move to last row in the result set rs
SQLResultSetGoto[rs,Infinity]move to after the last row in the result set rs

Examples of result set position functions.

This closes the result set and the SQL connection.

In[30]:=
Click for copyable input

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

SQLResultSetTake[rs,spec]use specification spec to read from the result set rs
SQLResultSetRead[rs,-num]shift current position by num in the result set rs
SQLResultSetColumnNames[rs]return the names of the columns in the result set rs

Advanced result set functions.

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

In[32]:=
Click for copyable input
Out[34]=

This shows the rows that are in the result set.

In[35]:=
Click for copyable input
Out[35]//TableForm=

The following gets rows two through four.

In[36]:=
Click for copyable input
Out[36]=

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

In[37]:=
Click for copyable input
Out[37]=

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

In[38]:=
Click for copyable input
Out[38]=
In[39]:=
Click for copyable input
Out[39]=

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.

In[40]:=
Click for copyable input
Out[41]=

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

In[42]:=
Click for copyable input
Out[42]=

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

In[43]:=
Click for copyable input
Out[43]//InputForm=

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

In[44]:=
Click for copyable input
Out[44]=

This closes the result set and the SQL connection.

In[45]:=
Click for copyable input

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

In[47]:=
Click for copyable input
Out[49]=

This shows the rows that are in the result set.

In[50]:=
Click for copyable input
Out[50]//TableForm=

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.

In[51]:=
Click for copyable input
Out[52]=

The following resets the result set to the beginning.

In[53]:=
Click for copyable input
Out[53]=

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.

In[54]:=
Click for copyable input
Out[54]=

This closes the result set and the SQL connection.

In[55]:=
Click for copyable input