Compilation Tutorials: Getting Results

In this chapter, we'll learn how to get the execution results of an SQL statement.

We call a set of records returned by a query as a result set (or a Recordset in VB). The results of the search are usually grouped into the following steps:

Verify that a result set is available.

Bind the columns of the result set to the appropriate variables.

Get rows

Once we have completed the operation on the result set, we must call SQLCloseCursor to destroy it.

To confirm that a result set is available

Sometimes after executing the SQL statement, we already know whether the result set is available. If the executed statement is not a type that returns a result set, we know that the result set is not available. But sometimes we don't quite know what type of SQL statement to use, for example, let the user enter the SQL statement themselves. At this point, we have to confirm whether a result set is established, which can be achieved by sqlnumresultcols function. If the result set exists, the function returns the number of columns (fields) in the result set. The syntax is as follows:

Sqlnumresultcols Proto Statementhandle:dword, Pnumcols:dword

Statementhandle statement Handle

Pnumcols points to a two-word pointer that contains the number of columns in the result set.

If Pnumcols points to a value of 0, it indicates that the result set does not exist.

Bound columns

As with the arguments that bind a variable to an SQL statement, we connect (BIND) A variable to a column in the result set. The function to use here is SQLBindCol, the syntax is as follows:

SQLBindCol Proto Statementhandle:dword, Columnnumber:dword, Targettype:dword, Targetvalueptr:dword, BufferLength:D WORD, Pstrlenorindptr:dword

Statementhandle statement Handle

ColumnNumber the column ordinal to bind in the result set. Column ordinal starting from 1. Column 0 is a bookmark (bookmark) column.

TargetType The constant that indicates the type of variable (buffer) that targetvalueptr points to.

Targetvalueptr A pointer to a variable or buffer that will be bundled to a column. When SQLFetch is invoked to obtain a row in the result set, the variable or buffer to which this argument refers is populated with the value of the bound column.

Bufferlength the length of the buffer to which the targetvalueptr points.

Pstrlenorindptr See previous chapter SQLBindParameter

Example:

. Data?
Buffer db DUP (?)
Datalength DD?; the length of the string to be filled into the buffer after the SQLFetch is invoked
. Code
.....
Invoke SQLBindCol, hstmt, 1, Sql_c_char, addr buffer, addr datalength

Get a row

Very simple. First Call SQLFetch retrieves a column of the result set into the bound variable. When the SQLFetch call completes, the cursor (cursor) is updated (updated). You can think of a cursor as a record pointer (a recording pointer). It indicates which row will be returned after the SQLFetch call. For example, if the result set has 4 columns, when the result set is established, the cursor points to the first row. When the SQLFetch is invoked, the cursor is added by 1. So if you call SQLFetch 4 times, there will be no more rows to be submitted. The cursor appears pointing at the end of the file (EOF). The SQLFetch syntax is as follows:

SQLFetch Proto Statementhandle:dword

This function returns SQL_NO_DATA when there is no row to commit.

Example:

. Data?
Buffer db DUP (?)
DATALENGTH DD?
. Code
.....
Invoke SQLBindCol, hstmt, 1, Sql_c_char, addr buffer, addr datalength
Invoke SQLFetch, hstmt

Translator Nonsense: Remember the Earth's Jingwei? We use SQLBindCol to locate the Longitude (column), define latitude (rows) with a cursor, use SQLFetch to get the coordinates, and the result set is the Earth. The Great sailing age 2 of the lovers should not forget it! (Captain, we can't find the water ...) By the way, is this the earth?%$#@&*^:-)