Retrieving Results

In the last section we covered executing SQL queries to add, delete, update and select records from a database. Performing queries to retrieve data is one of the most important functions of a database.

Both dbExecSQL and dbPrepareSQL return a statement handle that is used to retrieve the data from selected rows. The entire set of rows returned by a query is called the result set. The statement handle must always be freed when you are done using it with the dbFreeSQL command.

Binding variables

Suppose we want to get last name, first name and branch information for customers at branch 11240 from the account table in a hypothetical banks database. To do this we might perform a query like the following:

SELECT lname, fname, branch FROM account WHERE branch = 11240

After preparing and executing this query, we are ready to fetch the data row by row. If all the information in the result columns in the projection of this query are known (e.g. data type, precision, etc.), then we can use dbBindVariable and dbGet to fetch the results.

Binding a variable associates it with a column in the result set. A bound variable is then updated with each call to the dbGet, dbGetNext or dbGetPrev functions. When binding variables it is important to remember that column numbers start at 1. Since our query has three column names after the SELECT statement there will be exactly three columns in the result set.

Example:

...
DEF last,first as STRING
DEF branch as INT
hstmt = dbExecSQL(pdb,"SELECT lname, fname, branch FROM account WHERE branch = 11240")
error = dbGetErrorCode(hstmt)
IF LEN(error)
    PRINT
    PRINT "Error Code: ", error
    PRINT "Error Text: ", dbGetErrorText(hstmt)
    PRINT
ELSE
    dbBindVariable(hstmt, 1, last)
    dbBindVariable(hstmt, 2, first)
    dbBindVariable(hstmt, 3, branch)
    WHILE dbGet(hstmt)
        PRINT last, " ", first
        PRINT branch
    ENDWHILE
ENDIF
dbFreeSQL(hstmt)

Direct data retrieval

Binding variables is the most common method for retrieving the data from a column. Some database drivers also support retrieving the data directly without the need for binding. After a statement is successfully executed you can use dbGetData, dbGetTime, dbGetDate, and dbGetTimeStamp to transfer the data from a result set column to a variable after each iteration of dbGet.

Binding variables will always result in more efficient database usage over larger result sets. Also certain drivers will not allow directly retrieving data from a column if it is already bound to a variable. The Access driver supports both binding and direct retrieval.

Example:

...
DEF last,first as STRING
DEF branch as INT
hstmt = dbExecSQL(pdb,"SELECT lname, fname, branch FROM account WHERE branch = 11240")
error = dbGetErrorCode(hstmt)
IF LEN(error)
    PRINT
    PRINT "Error Code: ", error
    PRINT "Error Text: ", dbGetErrorText(hstmt)
    PRINT
ELSE
    WHILE dbGet(hstmt)
        dbGetData(hstmt, 1, last)
        dbGetData(hstmt, 2, first)
        dbGetData(hstmt, 3, branch)
        PRINT last, " ", first
        PRINT branch
    ENDWHILE
ENDIF
dbFreeSQL(hstmt)

Dealing with NULL columns

When thinking of the term 'NULL' most programmers envision a NULL pointer, or a NULL string, or the number 0.  A database column in a result set can follow this methodology for any data type.

A NULL column in a result set is simply a column that contains no data, there is nothing to read. For obvious reasons the database can't use the number 0 to represent a NULL value so instead a field is tagged with a constant. The database command set provides two methods for determining whether a column is currently NULL.

The first method involves supplying an optional indicator variable to dbBindVariable that on each success call to any of the dbGet functions will be used by the database driver to store a value. This value is either the length of the returned data or one of the constants SQL_NULL_DATA or SQL_NO_TOTAL.  The one we are interested in is SQL_NULL_DATA which happens to be equal to -1

Example:

DEF last,first as STRING
DEF branch,branch_null as INT
hstmt = dbExecSQL(pdb,"SELECT lname, fname, branch FROM account WHERE branch = 11240")
error = dbGetErrorCode(hstmt)
IF LEN(error)
    PRINT
    PRINT "Error Code: ", error
    PRINT "Error Text: ", dbGetErrorText(hstmt)
    PRINT
ELSE
    dbBindVariable(hstmt, 1, last)
    dbBindVariable(hstmt, 2, first)
    dbBindVariable(hstmt, 3, branch, branch_null)
    WHILE dbGet(hstmt)
        PRINT last, " ", first
        IF branch_null <> -1
           PRINT branch
        ELSE
            PRINT "No longer an account holder"
        ENDIF
    ENDWHILE
ENDIF
dbFreeSQL(hstmt)

The second method to determine if a column is NULL is to use the dbIsNull function. dbIsNull returns TRUE if a column in a result set is currently NULL or FALSE otherwise. dbIsNull will not work with all database drivers and others will not allow a bound column to be checked against NULL in this manner.  The Access driver allows both.

Example:

DEF last,first as STRING
DEF branch as INT
hstmt = dbExecSQL(pdb,"SELECT lname, fname, branch FROM account WHERE branch = 11240")
error = dbGetErrorCode(hstmt)
IF LEN(error)
    PRINT
    PRINT "Error Code: ", error
    PRINT "Error Text: ", dbGetErrorText(hstmt)
    PRINT
ELSE
    dbBindVariable(hstmt, 1, last)
    dbBindVariable(hstmt, 2, first)
    dbBindVariable(hstmt, 3, branch)
    WHILE dbGet(hstmt)
        PRINT last, " ", first
        IF dbIsNull(hstmt,3) = FALSE
           PRINT branch
        ELSE
            PRINT "No longer an account holder"
        ENDIF
    ENDWHILE
ENDIF
dbFreeSQL(hstmt)

Which method you chose depends on the needs of the program and the capabilities of the database driver. A final method not outlined here is to adjust the SQL query to not return rows based on a NULL criteria such as:
 

hstmt = dbExecSQL(pdb,"SELECT lname, fname, branch FROM account WHERE branch IS NOT NULL")