An application programmer writes a DB-Library program, using calls to DB-Library routines to set up DB-Library structures, connect to servers, send commands, process results, and clean up. A DB-Library program is compiled and run in the same way as any other C language program.
Programming with DB-Library/C typically involves a few basic steps:
Logging into a server.
Placing language commands into a buffer and sending them to the server.
Processing the results, if any, returned from the server, one command at a time and one result row at a time. The results can be placed in program variables, where they can be manipulated by the application.
Handling DB-Library/C errors and server messages.
Closing the connection with the server.
The example below shows the basic framework of many DB-Library/C applications. The program opens a connection to a Adaptive Server, sends a Transact-SQL select command to the server, and processes the set of rows resulting from the select. For brevity’s sake, this program does not include the error or message handling routines; those routines are illustrated in the example programs included online with DB-Library.
#include <sybfront.h>
#include <sybdb.h>
#include <syberror.h>
/* Forward declarations of the error handler and message
** handler.
*/
interr_handler();
intmsg_handler();
main()
{
DBPROCESS *dbproc; /* The connection with */
/* SQL Server */
LOGINREC *login; /* The login information */
DBCHAR name[40];
DBCHAR city[20];
RETCODE return_code;
/* Initialize DB-Library */
if (dbinit() == FAIL)
exit(ERREXIT);
/*
** Install user-supplied error-handling and message-
** handling routines. The code for these is omitted
** from this example for conciseness.
*/
dberrhandle(err_handler);
dbmsghandle(msg_handler);
/* Get a LOGINREC */
login = dblogin();
DBSETLPWD(login, "server_password");
DBSETLAPP(login, "example");
/* Get a DBPROCESS structure for communication */
/* with SQL Server. */
dbproc = dbopen(login, NULL);
/*
** Retrieve some columns from the "authors" table
** in the "pubs2" database.
*/
/* First, put the command into the command buffer. */
dbcmd(dbproc, "select au_lname, city from
pubs2..authors");
dbcmd(dbproc, "
where state = ’CA’ ");
/*
** Send the command to SQL Server and start execution
*/
dbsqlexec(dbproc);
/* Process the command */
while ((return_code = dbresults(dbproc)) !=
NO_MORE_RESULTS)
{
if (return_code == SUCCEED)
{
/* Bind results to program variables. */
dbbind(dbproc, 1, STRINGBIND, (DBINT)0, name);
dbbind(dbproc, 2, STRINGBIND, (DBINT)0, city);
/* Retrieve and print the result rows. */
while (dbnextrow(dbproc) != NO_MORE_ROWS)
{
printf ("%s: %s\n", name, city);
}
}
}
/* Close the connection to SQL Server */
dbexit();
}
The example illustrates features common to most DB-Library/C applications:
Header files – Two header files, sybfront.h and sybdb.h, are required in all source files that contain calls to DB-Library/C routines. sybfront.h must appear first in the file. It defines symbolic constants such as function return values, described in the reference pages in Chapter 2, “Routines” and the exit values STDEXIT and ERREXIT. These exit values can be used as the argument for the C standard library function exit. Since they are defined appropriately for the operating system running the program, their use provides a system-independent approach to exiting the program. sybfront.h also includes type definitions for datatypes that can be used in program variable declarations. These datatypes are described later.
sybdb.h contains additional definitions, most of which are meant to be used only by the DB-Library/C routines and should not be directly accessed by the program. Of chief importance in sybdb.h is the definition of the DBPROCESS structure. As discussed earlier, the DBPROCESS structure should be manipulated only through DB-Library/C routines; you should not access its components directly. To ensure compatibility with future releases of DB-Library/C, use the contents of sybdb.h only as documented in the reference pages in Chapter 2, “Routines.”
The third header file in the example, syberror.h, contains error severity values and should be included if the program refers to those values.
dbinit – This routine initializes DB-Library/C. It must be the first DB-Library/C routine in the program. Not all DB-Library/C environments currently require the dbinit call. However, to ensure future compatibility and portability, you should include this call at the start of all DB-Library/C programs.
dberrhandle and dbmsghandle – dberrhandle installs a user-supplied error-handling routine, which gets called automatically whenever the application encounters a DB-Library/C error. Similarly, dbmsghandle installs a message-handling routine, which gets called in response to informational or error messages returned from the server. The error and message handling routines are user-supplied. Sample handlers have not been supplied with this example, but are included with the online example programs. For more information on the example programs, see the Open Client/Server Programmer’s Supplement.
dblogin – This routine allocates a LOGINREC structure, which DB-Library/C will use to log in to the server. The two macros that follow set certain components of the LOGINREC. DBSETLUSER and DBSETLPWD set the user name and password that DB-Library/C will use when logging in. DBSETLAPP sets the name of the application, which will appear in Adaptive Server’s sysprocesses table. Routines are available for setting other aspects of the LOGINREC. However, in most environments these routines are optional; the LOGINREC contains default values for each of the values they set.
dbopen – The dbopen routine opens a connection between the application and a 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 routine has been called, the application is connected with Adaptive Server and can now send Transact-SQL commands to Adaptive Server and process any results.
dbcmd – This routine fills the command buffer with Transact-SQL commands, which can then be sent to Adaptive Server. Each succeeding call to dbcmd simply adds the supplied text to the end of any text already in the buffer. It is the programmer’s responsibility to supply necessary blanks between words, such as the blank at the beginning of the text in the second dbcmd call in the example. Multiple commands can be included in the buffer. This example only shows how to send and process a single command, but DB-Library/C is designed to allow an application to send multiple commands to a server and process each command’s set of results separately.
dbsqlexec – This routine executes the command buffer; that is, it sends the contents of the buffer to Adaptive Server, which parses and executes them.
dbresults – This routine gets the results of the current Transact-SQL command ready for processing. In this case, the buffer contains a single command that returns rows, so the program is required to call dbresults one time. dbresults is called in a loop, however, because it is good programming practice to do so. It is recommended that dbresults always be called in a loop, as it is in this example, even when it is not strictly necessary.
dbbind – dbbind binds result columns to program variables. In the example, the first call to dbbind binds the first result column to the program variable city. In other words, when the program reads a result row by calling dbnextrow, the contents of the first result column (au_lname) will get placed in the program variable name. The second dbbind call binds the second result column to the variable city.
The bind type of both bindings is STRINGBIND, one of several binding types available for character data. The binding type must correspond to the datatype of the specified program variable. In this example the variable has a DBCHAR datatype, a DB-Library/C-defined datatype that accepts a STRINGBIND result. By means of the binding type parameter, dbbind supports a wide variety of type conversions, allowing the datatype of the receiving variable to differ from the datatype of the result column.
dbnextrow – This routine 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, because each call to dbnextrow overwrites the previous values in the program variables. This example program merely prints each row’s contents.
dbexit – This routine closes the server connection and deallocates the DBPROCESS. It also cleans up any structures initialized by dbinit. It must be the last DB-Library/C routine in the program.
Although DB-Library/C contains a great number of routines, much can be accomplished with just the few routines shown in this example.
