Export (0) Print
Expand All

Programming with DB-Library for C

SQL Server 2000

  New Information - SQL Server 2000 SP3.

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

  1. Connect to Microsoft® SQL Server™ 2000.

  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 application connects to SQL Server, sends a Transact-SQL SELECT statement to SQL Server, and processes the set of rows resulting from the SELECT statement.

For more 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 ();
    DBSETLSECURE (login);
    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:

  • DBMSDOS (for Microsoft MS-DOS®)

  • DBMSWIN (for 16-bit Microsoft Windows®)

  • DBNTWIN32 (for 32-bit Windows 95 and Microsoft Windows NT® 4.0)

    An alternative is to 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 4.0, 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 application. The Sqlfront.h file also includes type definitions for data types that can be used in program variable declarations. These data types are described in DB-Library for C Data types.

    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 here.

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 on 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.

Security Note  Authorization information, including user name and password, is stored in memory in the LOGINREC structure. It is possible that someone accessing a memory dump of the machine running the application could access this information. Take precautions to prevent access to memory data by unauthorized individuals.

dbopen

Opens a connection between the application and SQL Server. It uses the LOGINREC supplied by dblogin to log on 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.

Security Note  Connection information, including user name and password, is stored in memory in the DBPROCESS structure. It is possible that someone accessing a memory dump of the machine running the application could access this information. Take precautions to prevent access to memory data by unatuthorized individuals.

dbcmd

Fills the command buffer with Transact-SQL statements, which can then be sent to SQL Server. Each call to dbcmd, after the first, 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 time-out, 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 meta data for the current result set is available. Your application 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 data type 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.

Show:
© 2014 Microsoft