Difference between revisions of "OpenDBX/C API/Usage"

Latest revision as of 17:18, 5 October 2012

Using the interface is fairly easy and pretty straight forward: Connect to your database, send your query, retrieve the result set, process the row values and clean up the fetched result set. 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 free 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.

A simple test program might be:

#include <odbx.h>
#include <stdio.h>

int main( int argc, char* argv[] )
{
    odbx_t* handle;

    if( odbx_init( &handle, "mysql", "127.0.0.1", "" ) < 0 )
    {
        printf( "Error in odbx_init\n" );
        return 1;
    }

    printf( "Initialized connection\n" );

    if( odbx_finish( handle ) < 0 )
    {
        printf( "Error in odbx_finish\n" );
        return 1;
    }

    return 0;
}

As soon as you've implemented something you want to test, you want compile and run your program to see if it is successful. Compilation and linking of your program are done with

gcc -o myprog myprog.c -lopendbx

This call the GNU compiler and translates the C code in "myprog.c" into an binary object called "myprog.o". In the second step it creates an executable named "myprog" and links it against the OpenDBX library ("-lopendbx"). Now you can test and see what happens.

Dealing with connections

You need connect to your database before you can send queries to the DataBase Management System (DBMS). 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;
}

The values for the host and port arguments of odbx_init() respectively the database, who and cred arguments of odbx_bind() may be dependent on the database backend you are using. Please refer to the OpenDBX backend configuration page and the documentation of the database backend to get further information.

Connecting to the database is done by calling odbx_bind() with the initialized handle, the database name and the credentials. The backend will open an connection to the DBMS and authenticates itself by using the user name and password.

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

Sometimes it may be necessary to connect to a different database or re-authenticate using different credentials. You can do this by calling odbx_bind() 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 program. Finally call odbx_finish() to release all resources and disconnect from the database.

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

Handling options and capabilities

If you want to check for library info or use non-standard options like encrypted data transfer or multi-statement support (provided they are supported), this can be changed after calling odbx_init() and before odbx_bind() by using odbx_{get,set}_option(). 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 safe;
int option;

if( ( err = odbx_get_option( handle, ODBX_OPT_THREAD_SAFE, (void*) &safe ) ) < 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 large objects. 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.

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

Executing statements

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 SQL injection, you have to escape every (!) user input you like to use in a query, insert statement or anything else. It's a good idea to escape also strings returned from the database because they could be injected SQL statements too. Fortunately odbx_escape() handles all escaping for you. It prevents any kind of SQL insertion and sometimes replaces special characters in the input string. The result is written to a buffer provided by your program 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 (precisely, 2 * input size + 1 ), 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 program 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.

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

if( ( len = snprintf( query, 256, qstr, 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() returns a result structure you have to delete afterwards by calling odbx_result_finish().

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_finish( result );
        continue;
    }

    // Fetch rows and process values

    odbx_result_finish(result);
    tv = { 3, 0 };
}

When you've successfully retrieved a result from the database, you have to use odbx_row_fetch() to get the rows subsequently until it returns ODBX_ROW_DONE which 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 ) ) != ODBX_ROW_DONE )
{
    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 don't return error codes. Instead, they return zero (odbx_field_length()) or NULL (odbx_column_name() and odbx_field_value()), but you shouldn't check for those because these values are also valid return values.

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.

Futher examples

A full-featured working example of how to use the OpenDBX C API correctly can be found in the test/odbx-regression.c file of the source distribution available at the download section.