Working with results sets

If the SQL statements you execute generate one or more results sets, you will likely want to retrieve data from them. Several statements in the SQL function library allow you to do this.

Accessing a results set

If one or more results sets are generated, the first one will be active by default, allowing you to retrieve data from it. If there are multiple results sets, use the SQL_GetNextResults() function to make the next results set active. Once you have moved to the next results set, you can’t return to the previous results set. As an example, the following two SQL statements generate the two results sets shown below:

SQL statements

select Buyer_ID, Buyer_First_Name, Buyer_Last_Name from BUYERDAT where City = 'Fargo'
select House_ID, Address from HOUSEDAT where City = 'Fargo'

Results sets

Retrieving data in a results set

You can retrieve data from the currently-active results set one row at a time. Use the SQL_FetchNext() function to access the first row in the current results set. The SQL_GetNumCols() function returns the number of columns in the results set. Use the SQL_GetData() function to retrieve a specific column from the current row. The SQL_DescribeColumn() function can be used to retrieve information about the contents of the column.

When you have finished retrieving data for the current row, use the SQL_FetchNext() function to move to the next row. Once you have moved to the next row, you can’t return to the previous row.

There is no way to know how many rows a results set contains. To read all rows in a results set, use the SQL_FetchNext() function to retrieve rows until it returns an error 31, indicating that there are no more rows in the results set.

Clearing results sets

Once you have accessed all of the data resulting from the SQL statements that were executed, use the SQL_Clear() function. This function clears any results sets and error information, preparing the pass-through SQL connection to be used again.