Programming with DB-Library for C

This chapter gives an overview of programming with DB-Library for C. It groups functions into categories, and gives a quick reference to the bulk copy and two-phase commit service special libraries.

Programming with DB-Library for C typically involves the following steps:

  1. Connect to SQL Server.
  2. Put Transact-SQL statements into a buffer and send them to SQL Server.
  3. Process the results, if any, returned from SQL Server, one statement at a time and one row at a time. You can put the results into program variables, where the application can manipulate them.
  4. Handle DB-Library errors and SQL Server messages.
  5. Disconnect from SQL Server.

The following example shows the basic framework of many DB-Library for C applications. The program connects to SQL Server, sends a Transact-SQL SELECT statement to SQL Server, and processes the set of rows resulting from the SELECT.

For information about defining the target operating system prior to compiling your application, see Building Applications.

#define DBNTWIN32
#include <stdio.h>
#include <windows.h>
#include <sqlfront.h>
#include <sqldb.h>
// Forward declarations of the error handler and message handler. 
int err_handler(PDBPROCESS, INT, INT, INT, LPCSTR, LPCSTR);
int msg_handler(PDBPROCESS, DBINT, INT, INT, LPCSTR, LPCSTR,
                LPCSTR, DBUSMALLINT);
main()
{
    PDBPROCESS  dbproc;    // The connection with SQL Server. 
    PLOGINREC   login;     // The login information. 
    DBCHAR      name[100];
    DBCHAR      city[100];

    // Install user-supplied error- and message-handling functions.
    dberrhandle (err_handler);
    dbmsghandle (msg_handler);

    // Initialize DB-Library.
    dbinit ();

    // Get a LOGINREC.
    login = dblogin ();
    DBSETLUSER (login, "my_login");
    DBSETLPWD (login, "my_password");
    DBSETLAPP (login, "example");

    // Get a DBPROCESS structure for communication with SQL Server. 
    dbproc = dbopen (login, "my_server");

    // Retrieve some columns from the "authors" table in the
    // "pubs" database.

    // First, put the command into the command buffer. 
    dbcmd (dbproc, "select au_lname, city from pubs..authors");
    dbcmd (dbproc, " where state = 'CA' ");

    // Send the command to SQL Server and start execution. 
    dbsqlexec (dbproc);

    // Process the results. 
    if (dbresults (dbproc) == SUCCEED)
    {
        // Bind column to program variables. 
        dbbind (dbproc, 1, NTBSTRINGBIND, 0, name);
        dbbind (dbproc, 2, NTBSTRINGBIND, 0, city);

        // Retrieve and print the result rows. 
        while (dbnextrow (dbproc) != NO_MORE_ROWS)
        {
            printf ("%s from %s\n", name, city);
        }
    }

    // Close the connection to SQL Server. 
    dbexit ();

    return (0);
}

int err_handler (PDBPROCESS dbproc, INT severity,
    INT dberr, INT oserr, LPCSTR dberrstr, LPCSTR oserrstr)
{
    printf ("DB-Library Error %i: %s\n", dberr, dberrstr);
    if (oserr != DBNOERR)
    {
        printf ("Operating System Error %i: %s\n", oserr, oserrstr);
    }
    return (INT_CANCEL);
}

int msg_handler (PDBPROCESS dbproc, DBINT msgno, INT msgstate,
    INT severity, LPCSTR msgtext, LPCSTR server,
    LPCSTR procedure, DBUSMALLINT line)
{
    printf ("SQL Server Message %ld: %s\n", msgno, msgtext);
    return (0);
}

This example illustrates features common to most DB-Library for C applications, including:

header files
All source files that contain calls to DB-Library functions require two header files, SQLFRONT.H and SQLDB.H.

Before including the SQLFRONT.H and SQLDB.H files, define the target operating system with #define:

Or put DBMSDOS, DBMSWIN, or DBNTWIN32 on the compilation command lines. For more information, see the examples in "Include Files," in Building Applications.

For Windows, Windows 95, and Windows NT, you must include WINDOWS.H before including the SQLFRONT.H and SQLDB.H files.

Include SQLFRONT.H before SQLDB.H. SQLFRONT.H defines symbolic constants, such as function return values and the exit values STDEXIT and ERREXIT. These exit values can be used as the parameter for the C standard library function exit. The exit values are defined appropriately for the operating system running the program. The SQLFRONT.H file also includes type definitions for datatypes that can be used in program variable declarations. These datatypes are described in Appendix B: DB-Library Datatypes.

The SQLDB.H file contains additional type definitions and DB-Library function prototypes, most of which are meant to be used only by the DB-Library functions. They should not be accessed directly by the program. To ensure compatibility with future releases of DB-Library, use the contents of SQLDB.H only as documented in this documentation.

dberrhandle and dbmsghandle
The first of these DB-Library functions, dberrhandle, installs a user-supplied error-handling function, which is called automatically whenever the application encounters a DB-Library error. Similarly, dbmsghandle installs a message-handling function, which is called in response to informational or error messages returned from SQL Server. The error- and message-handling functions are user-supplied. It is strongly recommended that users supply error-processing functions.
dblogin
Supplies a LOGINREC structure, which DB-Library uses to log in to SQL Server. Two functions set entries in the LOGINREC. DBSETLPWD sets the password that DB-Library uses when logging in. DBSETLAPP sets the name of the application, which appears in the SQL Server sysprocess table. Certain functions set other aspects of the LOGINREC, which contains defaults for each value they set.
dbopen
Opens a connection between the application and SQL Server. It uses the LOGINREC supplied by dblogin to log in to the server. It returns a DBPROCESS structure, which serves as the conduit for information between the application and the server. After this function has been called, the application is connected with SQL Server and can send Transact-SQL statements to SQL Server and process the results. Simultaneous transactions must each have a distinct DBPROCESS. Serial transactions can use the same DBPROCESS.
dbcmd
Fills the command buffer with Transact-SQL statements, which can then be sent to SQL Server. Each call, after the first, to dbcmd adds the supplied text to the end of any text already in the buffer. The programmer must supply necessary blanks between words, such as the space between the quotation mark and the word where in the second dbcmd call in the example:
dbcmd(dbproc, " where state = 'CA' ");

Although multiple statements can be included in the buffer, this example only shows how to send and process a single statement. DB-Library allows an application to send multiple statements (called a command batch) to SQL Server and process each statement's set of results separately.

dbsqlexec
Executes the command buffer; that is, it sends the contents of the buffer to SQL Server, which parses and executes the commands. This function causes DB-Library to wait until SQL Server has completed execution of the query. To avoid this delay, you can call dbsettime to set the DB-Library timeout, or you can use dbsqlsend, dbdataready, and dbsqlok (instead of dbsqlexec) to retain control while SQL Server is busy.
dbresults
Gets the results of the current Transact-SQL statement ready for processing. After dbresults returns SUCCEED, column metadata for the current results set is available. Your program should call dbresults until it returns no_more_results. If your program fails to do this, the DB-Library error message 10038 "Results Pending" occurs the next time that DBPROCESS is used.
dbbind
Binds result columns to program variables. In the example, the first call to dbbind binds the first result column to the name variable. In other words, when the program reads a result row by calling dbnextrow, the contents of the first column in the result row are placed in the name variable. The datatype of the binding is NTBSTRINGBIND, one of several binding types available for character data. The second call binds the second result column to the city variable.
dbnextrow
Reads a row and places the results in the program variables specified by the earlier dbbind calls. Each successive call to dbnextrow reads another result row until the last row has been read and NO_MORE_ROWS is returned. Processing of the results must take place inside the dbnextrow loop. This is because each call to dbnextrow overwrites the previous values in the bound program variables.
dbexit
Closes all SQL Server connections and frees all DBPROCESS structures created because of the application. It is usually the last DB-Library function in the program.