3.1. Quick Overview

libdbi is implemented as a library and is usually available as a shared object (aka dynamically linked library). If your program is supposed to use libdbi's capabilities, your program must be linked against libdbi which will be explained shortly. At runtime, the first step is always to create a libdbi instance by calling dbi_initialize_r. Programs that use threads or a plugin system may create more than one instance if needed and use them independently of each other. libdbi uses a plugin system that allows various databases to be supported simultaneously, and can dynamically load or unload drivers that are supplied by libdbi or a third party. Each instance loads the available database drivers independently. Programs can then use one or more of these drivers to create connections to databases, either by calling dbi_driver_open_r followed by dbi_conn_open, or in a single step by calling dbi_conn_new_r. Multiple connections may exist for a single driver, and all will function independently of each other. As not all database engine client libraries allow simultaneous access of a single connection by multiple threads, it is advisable to use separate connections per thread, even if you use a single libdbi instance for the entire process. Also, an instance may open connections using different drivers at the same time. Each libdbi instance can be independently shut down when no longer needed by calling dbi_shutdown_r which frees the memory occupied by the loaded drivers.

Note: libdbi versions 0.8.x and earlier did not allow the use of separate instances per process. Instead, each process had exactly one instance which was managed by libdbi internally. This interface is still available for backwards compatibility but will eventually be dropped.

The connection's options (username, password, hostname, etc.) are set with dbi_conn_set_option and dbi_conn_set_option_numeric. Once all options are set, dbi_conn_connect will connect to the database, waiting to handle a dbi_conn_query. A query is a string containing a valid SQL statement. libdbi provides several functions to automatically quote any characters that might screw up the query string. The preferred functions are dbi_conn_quote_string and dbi_conn_quote_string_copy as they take into consideration the character encoding used by the current connection. The legacy functions dbi_driver_quote_string and dbi_driver_quote_string_copy are still supported but should be avoided in new code. After a successful query, you can retrieve rows with dbi_result_first_row, dbi_result_last_row, dbi_result_prev_row, dbi_result_next_row, and dbi_result_seek_row.

String data may be sent to and retrieved from a database using character encodings if they contain characters not covered by the ASCII character set. Most database engines support character encodings like ISO-8859-1, suitable for many European languages, or even the multibyte Unicode character sets like UTF-8. The character set used to store your data in your database is usually set by the CREATE DATABASE command, which you have to take care of yourself. libdbi uses the connection option "encoding" to select a particular character encoding for the current connection. If you set the value to "auto", libdbi will automatically use the database character encoding as the connection encoding. If you request a different character encoding, as defined by its IANA name, libdbi will convert the data on the fly.

There are two methods for fetching field data, and two ways to perform each method. You can either "pull" the data from DBI using the dbi_result_get_* family of functions, or have DBI automatically "push" the data into predefined variables with the dbi_result_bind_* family of functions. Both families of functions are as strongly typed as most SQL database engines are. That is, you must use the dbi_result_get_* or dbi_result_bind_* function that matches the type of the requested field. Table 3-1 shows an overview of these functions sorted by the field type they retrieve.

Pulling the data from the database can be done with one of the "get" functions such as dbi_result_get_long or dbi_result_get_string, which simply return the data in the field you asked for. You should run the function dbi_conn_error_flag immediately after each call to a "get" function to check for errors. You can also get more than one field at a time with dbi_result_get_fields, which uses a printf-like syntax.

If you want DBI to automatically fill your program's variables with field values whenever a new row is fetched, you can "bind" fields to your variables. Bindings are set up with dbi_result_bind_long, dbi_result_bind_string, and the rest of the bind family of functions. Like the associated "get" function, you can set up multiple bindings at once with the dbi_result_bind_fields function.

String data can be safely included into query strings by using the dbi_conn_quote_string and dbi_conn_quote_string_copy functions. Binary data can be included into query strings by using the dbi_conn_quote_binary_copy function. All of these functions return zero-terminated strings enclosed in the appropriate quoting characters. Binary strings are returned in their binary representation. That is, they may contain null bytes and other non-printable characters. It is mandatory to use the dbi_result_get_field_length or dbi_result_get_field_length_idx functions to determine the number of bytes contained in the binary string.


dbi_result_next_row and the other row-seeking functions will return zero when there are no more rows available. Before the program terminates, you must call dbi_result_free on every result set and the connection must be disconnected and unloaded with dbi_conn_close. Finally, libdbi must be unloaded with dbi_shutdown.

Table 3-1. get* and bind* functions sorted by field type

field typeget by nameget by field indexbind
signed chardbi_result_get_chardbi_result_get_char_idxdbi_result_bind_char
unsigned chardbi_result_get_uchardbi_result_get_uchar_idxdbi_result_bind_uchar
unsigned shortdbi_result_get_ushortdbi_result_get_ushort_idxdbi_result_bind_ushort
unsigned intdbi_result_get_uintdbi_result_get_uint_idxdbi_result_bind_uint
long longdbi_result_get_longlongdbi_result_get_longlong_idxdbi_result_bind_longlong
unsigned long longdbi_result_get_ulonglongdbi_result_get_ulonglong_idxdbi_result_bind_ulonglong
character stringdbi_result_get_string, dbi_result_get_string_copydbi_result_get_string_idx, dbi_result_get_string_copy_idxdbi_result_bind_string
binary stringdbi_result_get_binary, dbi_result_get_binary_copydbi_result_get_binary_idx, dbi_result_get_binary_copy_idxdbi_result_bind_binary