OpenDBX/C API/Usage

Using the interface is fairly easy and pretty straight forward: Connect to your database, send your query, retrieve the result set and process the row values. After you've done that you can continue to send queries. If you've finished your job, you have to disconnect from the database and clean up all resources.

Sometimes there may occur an error somewhere, which you will be informed by a return value of the called function smaller than zero. The meanings of the error codes are listed in odbx.h and you get a textual explanation of it if you feed odbx_error() with the return value. A return value of -1 is handled a little bit special internally because then the native database library is asked directly what's wrong

Dealing with connections

You need to setup a connection to your database before you can send queries to the DataBase Management System. Before the connection can be established, the chosen backend must be loaded and all necessary data structures initialized. You don't have to worry about specific details because odbx_init() handles everything for you:

int err;
odbx_t* handle;

if( ( err = odbx_init( &handle, "mysql", "127.0.0.1", "" ) ) < 0 )
{
    fprintf( stderr, "odbx_init(): %s\n", odbx_error( handle, err ) );
    return err;
}

SQLite is a little bit different because there is no dedicated server you can connect to. Instead, the database is a file the SQLite library is operating on. Therefore you have to specify the path to the directory (without the file name but with trailing slash or backslash) as third parameter instead. The last parameter doesn't matter in this case. Most other backends will use their default port if the fourth parameter of odbx_init() is empty.

Opening the connection to the database is done by calling odbx_bind_simple() with the initialized handle, the database name and the credentials. The backend will either open an ip connection to the DBMS and authenticates itself by using the user name and password or the SQLite backend opens a file directly. The SQLite library also doesn't use the credentials because it relies on the access control provided by the file system.

if( ( err = odbx_bind_simple( handle, "testdb", "testuser", "testpwd" ) ) < 0 )
{
    fprintf( stderr, "odbx_bind_simple(): %s\n", odbx_error( handle, err ) );
    odbx_finish( handle );
    return err;
}

Sometimes it may be necessary to switch to another database or reauthenticate using different credentials. You can do this by calling odbx_bind_simple() again, but you have to close the connection with odbx_unbind() before.

if( ( err = odbx_unbind( handle ) ) < 0 )
{
    fprintf( stderr, "odbx_unbind(): %s\n", odbx_error( handle, err ) );
    odbx_finish( handle );
    return err;
}

I also strongly recommend to unbind before you terminate your programme.

odbx_finish( handle );

Handling options and capabilities

If you want to check for library info or use non-standard options like encrypted data transfer or compression (provided they are supported), this can be changed after calling odbx_init() and before odbx_open() by using odbx_{get,set}_options(). The function odbx_get_option() returns info about the library and its implemented functionality while odbx_set_option() can change the default behavior of the library.

int api;
int option;

if( ( err = odbx_get_option( handle, ODBX_OPT_API_VERSION, (void*) &api ) ) < 0 )
{
    fprintf( stderr, "odbx_get_option(): %s\n", odbx_error( handle, err ) );
    odbx_finish( handle );
    return err;
}

option = ODBX_TLS_ALWAYS;
if( ( err = odbx_set_option( handle, ODBX_OPT_TLS, (void*) &option ) ) < 0 )
{
    fprintf( stderr, "odbx_set_option(): %s\n", odbx_error( handle, err ) );
    odbx_finish( handle );
    return err;
}

Capabilities are implemented sets of functions serving a specific purpose, e.g. handling prepared statements. By calling odbx_capabilities() you can find out what sets of functionality are supported by the backend and you can do this any time after successfully invoking odbx_init(). Each set - the basic set is only the default one which is always available - is fully implemented by the backend, so you don't have to worry that the one or another function might be missing. Currently only ODBX_CAP_BASIC is defined.

if( ( err = odbx_capabilities( handle, ODBX_CAP_BASIC ) ) < 0 )
{
    fprintf( stderr, "odbx_capabilites(): %s\n", odbx_error( handle, err ) );
    odbx_finish( handle );
    return err;
}

Starting queries

Before you are sending queries to your database, you should be aware of the security issues you will be facing. If you will use any kind of user input in your statements, you will potentially be vulnerable to the injection of code and this can lead to disastrous effects. Imagine for example the following simple statement:

SELECT * FROM mytable WHERE uid = '%s';

Now your programme replaces "%s" by my input, which is

me'; DELETE FROM mytable WHERE uid LIKE '%

This results in a valid query with two statements and suddenly all your table entries are gone. To avoid code insertion, you have to escape every (!) user input you like to use in a query, insert statement or anything else. Fortunately odbx_escape() handles this for you. It prevents any kind of code insertion and sometimes replaces special characters in the input string. The result is written to a buffer provided by your programme and the parameter containing the length of the buffer will be overwritten by the real length of the result. Unfortunately most backends require that your result buffer is more than twice as big as your input, so you should first think about how long the user input might be.

char to[100];
int tolen = 100;

if( ( err = odbx_escape( handle, input, strlen( input ), to, &tolen ) ) < 0 )
{
    fprintf( stderr, "odbx_escape(): %s\n", odbx_error( handle, err ) );
    return err;
}

The queries sent to the server must be understood by the DBMS because there is no translation done between the different dialects of SQL for example. But this is desired! It adds no further level of complexity to the library and your programme which slows down your real work. Usually only a few statements with varying values are used in programmes and the easiest way make them adaptable to different databases by the users is to get them from a config file. Each of these statements must end with a semicolon - independent of the backend database.

int len;
char query[256] = "SELECT * FROM mytable WHERE uid = '%s';";

if( ( len = snprintf( query, 256, to ) ) > 0 && len < 256 )
{
    if( ( err = odbx_query( handle, query, len ) ) < 0 )
    {
        fprintf( stderr, "odbx_query(): %s\n", odbx_error( handle, err ) );
        return err;
    }
}

Retrieving results

Once you've send a query to the DBMS, the result sets can be retrieved by odbx_result(). In most backends you are able to specify a timeout and how the result should be retrieved: either one by one, all at once or in chunks. If chunks are not supported, they are returned one by one. The result handle - which you must use to get the rows and the values - is stored in the second parameter after odbx_result() returns. In case your query was not a SELECT-like statement and therefore returns no result, you can check for the number of affected rows instead. SELECT-like statement or not, in each case odbx_result() doesn't return an error, you have to delete the result structure by calling odbx_result_free().

odbx_result_t* result;
struct timeval tv = { 3, 0 };

while( ( err = odbx_result( handle, &result, &tv, 0 ) ) != 0 )
{
    if( err < 0 )
    {
        fprintf( stderr, "odbx_result(): %s\n", odbx_error( handle, err ) );
        return err;
    }

    switch( err )
    {
        case 1:
        // NOTE: Retrieving the result is not (!) canceled
        fprintf( stdout, "odbx_result(): Timeout\n" );
        continue;

        case 2:
        fprintf( stdout, "Affected rows: %d\n", odbx_rows_affected( result ) );
        odbx_result_free( result );
        continue;
    }

    // Fetch rows and process values

    odbx_result_free();
}

When you've successfully retrieved a result from the DBMS, you have to use odbx_row_fetch() to get the rows subsequently until a return value of zero indicates that no more rows are available. It is essential to fetch all rows of the result, even if you've found the row you've been searching for. Otherwise you will get an error while sending the next query or the outstanding rows are returned before the new result is available.

while( ( err = odbx_row_fetch( result ) ) != 0 )
{
    if( err < 0 )
    {
        fprintf( stderr, "odbx_row_fetch(): %s\n", odbx_error( handle, err ) );
        break;
    }

    // Get the values
}

Processing results

After fetching a row, all values of this row are available for further processing, as well as their name, length and type - but the name and the type of a column won't change. Also the number of columns returned by odbx_column_count() is fixed for the whole result.

int i;

for( i = 0; i < odbx_column_count( result ); i++ )
{
    fprintf( stdout, "Name: %s\n", odbx_column_name( result, i );
    fprintf( stdout, "Type: %d\n", odbx_column_type( result, i );
    fprintf( stdout, "Length: %d\n", odbx_field_length( result, i );
    fprintf( stdout, "Value: %s\n", odbx_field_value( result, i );
}

Besides odbx_column_type() these functions doesn't return error codes and odbx_column_name() only returns NULL if an error occured. Instead they return zero or NULL, but you shouldn't check for those because these values are also valid return values for odbx_column_count() and odbx_field_{length,value}().

All numbers are returned as strings from the database regardless if they are integers or floats. If you want to do arithmetic operations, you have to convert them to their machine dependent binary representation first.

---

Back to Overview