The functions SqlResults%, SqlNextRow%, and SqlData$ or SqlAData$ process the results of Transact-SQL statements and return information to the user.
The SqlResults% function sets up the results of the current Transact-SQL statement for processing. Normally, you call SqlResults% once for every Transact-SQL statement placed in the command buffer (SqlResults% should return SUCCEED), and then one last time to return the NOMORERESULTS indicator. In the preceding program fragment, three calls to SqlCmd% are used to place a single Transact-SQL statement in the command buffer. In this case, you need to call SqlResults% only twice. The first call should return SUCCEED, and the second call should return NOMORERESULTS.
When a call to SqlResults% returns SUCCEED (indicating that the results of the current Transact-SQL statement are available), call SqlNextRow% to read a row of data from SQL Server. Each successive call to SqlNextRow% reads another row until the last row has been read and the nomorerows indicator is returned. Row processing must take place after SqlNextRow% returns SUCCEED and before the next call to SqlNextRow%, because each call to SqlNextRow% overwrites the values in the previous row (unless row buffering has been turned on). You must call SqlNextRow% until it returns NOMOREROWS (to complete the processing of a result set) before calling SqlResults% again. The following program fragment uses a DO loop to call SqlNextRow% until nomorerows is returned.
Result% = SqlResults%(Sqlconn%) 'Process the command. IF Result% = SUCCEED THEN 'Retrieve and print the result rows. PRINT DO UNTIL SqlNextRow%(Sqlconn%) = NOMOREROWS Name$ = SqlData$(Sqlconn%, 1) City$ = SqlData$(Sqlconn%, 2) PRINT Name$, City$ LOOP END IF
Even when you know that a Transact-SQL query returns only one row, you must call SqlNextRow% twice: once to get the result row, and once to get the nomorerows flag.
Transact-SQL statements return two types of result rows: regular rows and compute rows. Regular rows are generated from columns in a Transact-SQL SELECT statement. Compute rows are generated from columns in a COMPUTE clause. Because these two types of rows contain very different data, the application must process them separately.
The SqlData$ function returns a string containing data from a regular result column. The SqlAData$ function returns a string containing data from a compute column. This data is always returned as a string, regardless of its datatype. Binary strings of the datatypes binary, varbinary, and image are returned as binary strings.
For an example of how these functions are used, refer to the QUERY sample application. This sample application calls the routines GetDatabases and Process_Sql_query, defined separately in the VBSQLGEN.BAS common code module.