FreeTDS User Guide: A Guide to Installing, Configuring, and Running FreeTDS
Prev | Chapter 11. Programming | Next

# DB-Library for the Tenderfoot

| Few things are harder to put up with than the annoyance of a good example.
| Mark Twain

> Below is a complete sample working db-lib program, presented as a series of examples.

> Features of sample code

> - Processes command-line options to select the server, database, username, and password

> - Remaining arguments on the command line comprise the SQL query to execute

> - Installs error and message handlers

> - Illustrates correct row-processing

> - Illustrates correct error detection and handling

> Other sample code may be found in the distribution, in the cleverly named `samples` directory. A complete program, heavily commented for your perusal, is `apps/bsqldb.c`.

How to Get and Build the sample code

1. Run `doc/grep_sample_code` to extract the `C` code from the User Guide `SGML` source.

2. Compile

3. Link

Files Required to Build the Sample Code

- `sybfront.h`

- `sybdb.h`

- `libsybdb.a` or `libsybdb.so`

Your library's extension may vary according to your operating system.

The source code may be built with commands similar to these. The precise options and paths depend on your particular system. The commands below work with the GNU compiler and linker on an ELF system with dynamic linking, common on Linux and BSD systems.

Example 11-1. Building the Sample Code

        $ ../doc/grep_sample_code ../doc/userguide.sgml > sample.c
        $ cc -I /usr/local/include -Wl,-L/usr/local/lib -Wl,-R/usr/local/lib sample.c -lsybdb -o sample

where `/usr/local/include` and `/usr/local/lib` are respectively the locations of your header files and libraries.

We now proceed to the code proper.

## Header files

> We need two header files to use db-lib. We need a few others to deal with I/O in C, as you know. Also declare the error and message handler functions, more about which later.

Example 11-2. Sample Code: `db-lib` header files

    #include <stdio.h>
    #include <stdlib.h>
    #include <string.h>
    #include <assert.h>
    #include <errno.h>
    #include <unistd.h>
    #include <libgen.h>

    #include <sqlfront.h>	/* sqlfront.h always comes first */
    #include <sybdb.h>	/* sybdb.h is the only other file you need */

    int err_handler(DBPROCESS*, int, int, int, char*, char*);
    int msg_handler(DBPROCESS*, DBINT, int, int, char*, char*, char*, int);

## Prolog

> Nothing special here. Collect the command line parameters. We do this with the standard `getopts(3)` function. Cf. man 3 getopts for details.

Example 11-3. Sample Code: `db-lib` prolog

    extern char *optarg;
    extern int optind;

    const static char syntax[] =
    		"syntax: example -S server -D db -U user -P passwd\n";

    struct {
    	char *appname, *servername, *dbname, *username, *password;
    } options = {0,0,0,0,0};

    int
    main(int argc, char *argv[])
    {
    	int i, ch;
    	LOGINREC *login;
    	DBPROCESS *dbproc;
    	RETCODE erc;

    	options.appname = basename(argv[0]);

    	while ((ch = getopt(argc, argv, "U:P:S:D:")) != -1) {
    		switch (ch) {
    		case 'S':
    			options.servername = strdup(optarg);
    			break;
    		case 'D':
    			options.dbname = strdup(optarg);
    			break;
    		case 'U':
    			options.username = strdup(optarg);
    			break;
    		case 'P':
    			options.password = strdup(optarg);
    			break;
    		case '?':
    		default:
    			fprintf(stderr, syntax);
    			exit(1);
    		}
    	}

    	argc -= optind;
    	argv += optind;

    	if (! (options.servername
    	    && options.username   && options.password)) {
    		fprintf(stderr, syntax);
    		exit(1);
    	}

111Prolog Notes

  `LOGINREC` is a structure that describes the client. It's passed to the server at connect time.
  `DBPROCESS` is a structure that describes the connection. It is returned by `dbopen()`.
  `RETCODE` is the most common return code type for `db-lib` functions.

## Initialize

> Initialize the library. Create and populate a `LOGINREC` record.

Example 11-4. Sample Code: `db-lib` Initialize

    	if (dbinit() == FAIL) {
    		fprintf(stderr, "%s:%d: dbinit() failed\n",
    				options.appname, __LINE__);
    		exit(1);
    	}

    	dberrhandle(err_handler);
    	dbmsghandle(msg_handler);

    	if ((login = dblogin()) == NULL) {
    		fprintf(stderr, "%s:%d: unable to allocate login structure\n",
    				options.appname, __LINE__);
    		exit(1);
    	}

    	DBSETLUSER(login, options.username);
    	DBSETLPWD(login, options.password);

112Initialization Notes

  Always make `dbinit()` the first db-lib call.
  Install the error- and mesage-handlers right away. They're explained in more detail later.
  `dblogin()` almost never fails. But check! No point in trying to use a null pointer.
  The `LOGIN` record isn't directly accessible. It's populated via macros like these. There are other fields, but these two are essential. Look for `SETLsomething` in the documentation.

## Connect to the server

> `dbopen()` forms a connection with the server. We pass our `LOGINREC` pointer (which describes the client end), and the name of the server. Then, optionally, we change to our favored database. If that step is skipped, the user lands in his default database.

Example 11-5. Sample Code: `db-lib` Connect to the server

    	if ((dbproc = dbopen(login, options.servername)) == NULL) {
    		fprintf(stderr, "%s:%d: unable to connect to %s as %s\n",
    				options.appname, __LINE__,
    				options.servername, options.username);
    		exit(1);
    	}

    	if (options.dbname  && (erc = dbuse(dbproc, options.dbname)) == FAIL) {
    		fprintf(stderr, "%s:%d: unable to use to database %s\n",
    				options.appname, __LINE__, options.dbname);
    		exit(1);
    	}

## Send a query

> `db-lib` maintains a command buffer to hold the SQL to be sent to the server. Two functions — `dbcmd()` and `dbfcmd()` — build up the query from strings of text. The command buffer is reset after the query is sent to the server.

> We left the SQL on the command line. We fetch it now and send it to the server.

Example 11-6. Sample Code: `db-lib` Send a query

    	for (i=0; i < argc; i++) {
    		assert(argv[i]);
    		printf("%s ", argv[i]);
    		if ((erc = dbfcmd(dbproc, "%s ", argv[i])) == FAIL) {
    			fprintf(stderr, "%s:%d: dbcmd() failed\n", options.appname, __LINE__);
    			exit(1);

    		}
    	}
    	printf("\n");

    	if ((erc = dbsqlexec(dbproc)) == FAIL) {
    		fprintf(stderr, "%s:%d: dbsqlexec() failed\n", options.appname, __LINE__);
    		exit(1);

    	}

113Initialization Notes

  Failure at this juncture is rare. The library is merely allocating memory to hold the SQL.
  `dbsqlexec()` waits for the server to execute the query. Depending on the complexity of the query, that may take a while.

`dbsqlexec()` will fail if something is grossly wrong with the query, e.g. incorrect syntax or a reference to nonexistent table. It's only the first of a few places where an error can crop up in processing the query, though. Just because `dbsqlexec()` succeeded doesn't mean you're in the clear.

## Fetch Results

> A query may produce zero, one, or more results. Broadly, that entails providing buffers to `db-lib` to fill, and iterating over the results a row (and column) at a time.

### Kinds of Results

Results is a special term: it means more than rows or no rows. To process the results means to gather the data returned by the server into the application's variables.

Table 11-4. Kinds of Results

Type | Metadata | Regular Rows | Compute Rows | Return Status | Example SQL
--- | --- | --- | --- | --- | ---
None | None | None | None | None | Any `INSERT`, `UPDATE`, or `DELETE` statement
Empty | 1 set | None | 0 or more | None | `SELECT name FROM systypes WHERE 0 = 1`
Simple | 1 set | 0 or more | None | None | `SELECT name FROM sysobjects`
Complex | 2 or more | 0 or more | 1 or more | None | `SELECT name FROM sysobjects COMPUTE COUNT(name)`
Stored Procedure | 0 or more | 0 or more | 0 or more | 1 or more | `EXEC sp_help sysobjects`

As the above table shows, results can comprise ordinary rows and compute rows (resulting from a `COMPUTE` clause). Stored procedures may of course contain multiple SQL statements, some of which may be `SELECT` statements and might include `COMPUTE` clauses. In addition, they generate a return status (with a `RETURN` statement or else automatically) and perhaps `OUTPUT` parameters.

### Data and Metadata

Observe that a row is set of columns, and each column has attributes such as type and size. The column attributes of a row are collectively known as metadata. The server always returns metadata before any data (even for a a `SELECT` statement that produced no rows).

Table 11-5. Result-fetching functions

Function | Fetches | Returns | Comment
--- | --- | --- | ---
`dbresults()` | metadata | `SUCCEED`, `FAIL` or, `NO_MORE_RESULTS`. | `SUCCEED` indicates just that: the query executed successfully (whew!). There may be metadata (and perhaps data) and/or stored procedure outputs available.
`nextrow()` | data | `REG_ROW`, compute_id, `NO_MORE_ROWS`, `BUF_FULL`, or `FAIL`. | Places fetched data into bound columns, if any.

### Binding

Each time `dbresults()` returns `SUCCEED`, there is something to retrieve. `db-lib` has different functions to deal with the different kinds of results. The functions are of two kinds: those that convert the data into a form desired by the application, known as binding, and those that return the data in "native" form.

To understand binding, it may be easiest to examine two primitive functions, `dbdata()` and `dbconvert()`. `dbdata()` returns a pointer to the column's data. The data to which it points are in "native" form, 4 bytes for an `INT`, 8 bytes for a `DATETIME` and so on. `dbconvert()` converts between datatypes; you can hand it an integer and get back a character array (or a `C double`. You might think of `dbconvert()` as `atoi(3)` on steroids). `dbbind()` combines these two functions. The application indicates in what form it would like to use each column, and the library converts them on the fly as each row is read.

To bind a column is to provide a buffer to `db-lib` to fill and indicate which datatype the buffer is meant to hold. [1]

It may be well to pause here to observe the three ways a datatype is described in a `db-lib` program.

`db-lib` Datatype Descriptors

Sever Datatype

  Describes the data as an abstract type, not representing any particular kind of storage. `SYBREAL`, for example, doesn't imply any particular arrangement of bits; it just means "a floating-point datatype corresponding to the `T-SQL REAL` type on the server." These all begin with `SYB`, e.g. `SYBINT4`.

Program Variable Datatype

  Defines a `C` variable in a machine-independent way. Because a `C` defines its `int` type according the CPU architecture, it may have 2, 4, 8, or some other number of bytes. A `DBINT` on the other hand, is guaranteed to be 4 bytes and, as such, assuredly will hold any value returned by the server from a `T-SQL INT` column. These all begin with `DB`, e.g. `DBREAL`.

Bind Type

  Prescribes a conversion operation. Indicates to `dbbind()` the Program Variable Datatype defined by the target buffer. Sybase and Microsoft call this the "vartype". These all end with `BIND`, e.g. `STRINGBIND`.

Typically it's more convenient to have `db-lib` convert the data into the desired form. The function that does that is `dbind()`. So: after fetching the metadata, and before fetching the data, we usually prepare the bound columns.

### Fetching Data

Table 11-6. Data-fetching functions

Type | Regular rows | Compute rows | Return status | `OUTPUT` parameters
--- | --- | --- | --- | ---
Meta | `dbnumcols()` | `dbnumcompute()`, `dbnumalts()`, `dbaltop()`, `dbbylist()` | `dbhasretstatus()` | `dbnumrets()`
Binding | `dbbind()`, `dbnullbind()` | `dbaltbind()`, `dbanullbind()` | `dbretstatus()` | none
Native | `dbdatlen()`, `dbdata()` | `dbadlen()`, `dbalttype()`, `dbaltutype()`, `dbaltlen()`, `dbadata()` | none | `dbretdata()`, `dbretlen()`, `dbretname()`, `dbrettype()`

The paradigm may now perhaps be clear: Query, fetch results, bind columns, fetch regular rows, fetch compute rows, fetch stored procedure outputs. Repeat as necessary.

Table 11-7. Putting it all together

Step | Function | Once Per | Many Times Per
--- | --- | --- | ---
Query | `dbsqlexec()` | Query | Program
Fetch metadata | `dbresults()` | SQL statement | Query
Prepare variables | `dbbind()` | Column | Statement
Fetch regular data | `dbnextrow()` | Row | Statement
Fetch compute data | `dbnextrow()` | Compute column | Statement
Fetch output parameters | `dbretdata()` | output parameter | Stored procedure
Fetch return status | `dbretstatus()` | Stored procedure | Program

| Fetch All Rows!

Now, at last, some sample code that fetches data. In the interest of simplicity, we don't bind anything except regular rows.

Example 11-7. Sample Code: `db-lib` Fetch Results

    	while ((erc = dbresults(dbproc)) != NO_MORE_RESULTS) {
    		struct COL
    		{
    			char *name;
    			char *buffer;
    			int type, size, status;
    		} *columns, *pcol;
    		int ncols;
    		int row_code;

    		if (erc == FAIL) {
    			fprintf(stderr, "%s:%d: dbresults failed\n",
    					options.appname, __LINE__);
    			exit(1);
    		}

    		ncols = dbnumcols(dbproc);

    		if ((columns = calloc(ncols, sizeof(struct COL))) == NULL) {
    			perror(NULL);
    			exit(1);
    		}

    		/*
    		 * Read metadata and bind.
    		 */
    		for (pcol = columns; pcol - columns < ncols; pcol++) {
    			int c = pcol - columns + 1;

    			pcol->name = dbcolname(dbproc, c);
    			pcol->type = dbcoltype(dbproc, c);
    			pcol->size = dbcollen(dbproc, c);

    			if (SYBCHAR != pcol->type) {
    				pcol->size = dbwillconvert(pcol->type, SYBCHAR);
    			}

    			printf("%*s ", pcol->size, pcol->name);

    			if ((pcol->buffer = calloc(1, pcol->size + 1)) == NULL){
    				perror(NULL);
    				exit(1);
    			}

    			erc = dbbind(dbproc, c, NTBSTRINGBIND,
    					     pcol->size+1, (BYTE*)pcol->buffer);
    			if (erc == FAIL) {
    				fprintf(stderr, "%s:%d: dbbind(%d) failed\n",
    						options.appname, __LINE__, c);
    				exit(1);
    			}
    			erc = dbnullbind(dbproc, c, &pcol->status);
    			if (erc == FAIL) {
    				fprintf(stderr, "%s:%d: dbnullbind(%d) failed\n",
    						options.appname, __LINE__, c);
    				exit(1);
    			}
    		}
    		printf("\n");

    		/*
    		 * Print the data to stdout.
    		 */
    		while ((row_code = dbnextrow(dbproc)) != NO_MORE_ROWS){
    			switch (row_code) {
    			case REG_ROW:
    				for (pcol=columns; pcol - columns < ncols; pcol++) {
    					char *buffer = pcol->status == -1?
    						"NULL" : pcol->buffer;
    					printf("%*s ", pcol->size, buffer);
    				}
    				printf("\n");
    				break;

    			case BUF_FULL:
    				assert(row_code != BUF_FULL);
    				break;

    			case FAIL:
    				fprintf(stderr, "%s:%d: dbresults failed\n",
    						options.appname, __LINE__);
    				exit(1);
    				break;

    			default:
    				printf("Data for computeid %d ignored\n", row_code);
    			}

    		}

    		/* free metadata and data buffers */
    		for (pcol=columns; pcol - columns < ncols; pcol++) {
    			free(pcol->buffer);
    		}
    		free(columns);

    		/*
    		 * Get row count, if available.
    		 */
    		if (DBCOUNT(dbproc) > -1)
    			fprintf(stderr, "%d rows affected\n", DBCOUNT(dbproc));

    		/*
    		 * Check return status
    		 */
    		if (dbhasretstat(dbproc) == TRUE) {
    			printf("Procedure returned %d\n", dbretstatus(dbproc));
    		}
    	}

    	dbclose(dbproc);
    	dbexit();
    	exit(0);
    }

114Data-fetching Notes

  As soon as `dbresults()` reports `SUCCESS`, the row's metadata are available.
  `db-lib` columns start with 1.
  `dbcollen()` returns the sizeof the native data (e.g. 4 bytes for a T-SQL `INT`). We'll use `dbbind()` to convert everything to strings. If the column is `[VAR]CHAR`, we want the column's defined size, otherwise we want its maximum size when represented as a string, which FreeTDS's `dbwillconvert()` returns (for fixed-length datatypes). [2]
  `NTBSTRINGBIND` null-terminates the character array for us. "NTB" might perhaps stand for "null terminating byte".
  A zero-length string is not a NULL! `dbnullbind()` arranges for the passed buffer to be set to -1 whenever that column is NULL for a particular row.
  Each time `dbnextrow()` returns `REG_ROW`, it has filled the bound buffers with the converted values for the row.
  Computed rows are left as an exercise to the reader.

## Messages and Errors

> Errors may originate on the server or in the library itself. The former are known as messages (because they are: they arrive as messages from the server); the latter are termed errors. Their handling is a little intimidating. It requires writing and installing a callback function (whose parameters are predefined by `db-lib`), and thinking about how to handle different types of errors.

Kinds of Errors

Messages

  Messages arise because the server has something to say. [3]. They usually describe some problem encountered executing the SQL. Perhaps the SQL refers to a nonexistent object or attempted to violate a constraint. But they can also be benign, indicating for instance merely that the default database has changed.

Errors

  Errors arise either because the application has misused `db-lib` in some way — say, passed a NULL `DBPROCESS` pointer or tried to issue a query while results were pending — or because some trouble cropped up in communicating with the server (couldn't find it, say, or didn't hear back from it).

Why these two require distinct handling is lost in the mists of time. But it does help to keep them distinct in your mind, especially while reading the documentation.

To have `db-lib` use your handler, pass its name to the appropriate `dberrhandle()` or `dbmsghandle()` function immediately after calling `dbinit()`.

Example 11-8. Sample Code: `db-lib` Error and Message handlers

    int
    msg_handler(DBPROCESS *dbproc, DBINT msgno, int msgstate, int severity,
    	    char *msgtext, char *srvname, char *procname, int line)
    {
    	enum {changed_database = 5701, changed_language = 5703 };

    	if (msgno == changed_database || msgno == changed_language)
    		return 0;

    	if (msgno > 0) {
    		fprintf(stderr, "Msg %ld, Level %d, State %d\n",
    				(long) msgno, severity, msgstate);

    		if (strlen(srvname) > 0)
    			fprintf(stderr, "Server '%s', ", srvname);
    		if (strlen(procname) > 0)
    			fprintf(stderr, "Procedure '%s', ", procname);
    		if (line > 0)
    			fprintf(stderr, "Line %d", line);

    		fprintf(stderr, "\n\t");
    	}
    	fprintf(stderr, "%s\n", msgtext);

    	if (severity > 10) {
    		fprintf(stderr, "%s: error: severity %d > 10, exiting\n",
    				options.appname, severity);
    		exit(severity);
    	}

    	return 0;
    }

    int
    err_handler(DBPROCESS * dbproc, int severity, int dberr, int oserr,
    	    char *dberrstr, char *oserrstr)
    {
    	if (dberr) {
    		fprintf(stderr, "%s: Msg %d, Level %d\n",
    				options.appname, dberr, severity);
    		fprintf(stderr, "%s\n\n", dberrstr);
    	}

    	else {
    		fprintf(stderr, "%s: DB-LIBRARY error:\n\t", options.appname);
    		fprintf(stderr, "%s\n", dberrstr);
    	}

    	return INT_CANCEL;
    }

| Handlers are always called before the function that engendered them returns control to the application.

115Error Handling Notes

  When first writing a handler, pay careful attention to the precise type of each parameter. Only by carefully matching them will you convince a modern `C` compiler that the address of your function is of the type accepted by `dbmsghandle()`. [4]
  Some messages don't convey much, as though the server gets lonely sometimes. You're not obliged to print every one.
  Severities are defined in the server documentation, and can be set by the `T-SQL RAISERROR` statement.
  Message handlers always and only ever return zero.
  When first writing the handler, pay careful attention to the precise type of each parameter. Only by carefully matching them will you convince a modern `C` compiler that the address of your function is of the type accepted by `dberrhandle()`. [5]
  Some messages are so severe they provoke `db-lib` into calling the error handler, too! If you have both installed — and of course you do, right? — then you can skip those lacking an error number.
  While `INT_CANCEL` is the most common return code, it's not the only one. For one thing, the error handler's return code can control how long `db-lib` keeps retrying timeout errors. See the documentation for details.

| No matter what the error handler says or does, it can't remedy the error. It's still an error and usually the best that can happen is that the function will return `FAIL`. The exception is timeout conditions, when the handler can stave off failure by requesting retries.

You may be asking yourself, "OK, fine, I can print the error message. But what if I want to communicate something back to line in my program where the error occurred? How to do that?" First of all, remember the calling function — that's your application — will learn of an error from the return code. If it needs more detail, though, there are two ways to pass it.

1. Set a global variable.

2. Use `setuserdata()` and `getuserdata()`.

| If your application is written in `C++`, you may be tempted to use `throw()`. Don't! Your handler is a `C` function and, more important, it's an extension of `db-lib`. You can put a `throw()` in your handler and it will compile. But when it executes, it's going to rip through `db-lib`'s stack. Your application will be unuseable at that point, if it doesn't cause a segment fault.

## Last Remarks

We've reached the end of our `db-lib` tour. The almost 300 lines of `C` above constitute program with these features:

Sample Code features

- Accepts command-line parameters and SQL.

- Checks for errors and server messages.

- Processes any number of results..

- Prints results in columns of suitable widths.

There are things it doesn't do, in the name of simplicity.

Sample Code nonfeatures

- No BCP (bulk copy) mode

- No RPC (remote procedure call) mode, preventing it from retrieving output parameters.

Your humble author hopes you found it worthwhile. Happy Hacking.

### Notes

[1] | This is the sort of thing `C++`'s type system does so much better
[2] | For IMAGE data, we need to multiply by 2, because `dbbind()` will convert each byte to a hexadecimal pair. The example program will report an error with IMAGE data.
[3] | Just one more way in which databases differ from files.
[4] | Back in K&R days, that wasn't such a problem. But there were other problems, some much worse.
[5] | If that advice sounds familiar, it's because it bears repeating.

Prev | Home | Next
ODBC API Implementation Summary | Up | Acknowledgments