3.2. Generic Example Program

The following listing shows how to establish a connection to a MySQL database server and retrieve the results of a SQL query. Only a small number of functions offered by libdbi are shown here. For a more extensive example check out the test program tests/test_dbi.c in the libdbi-drivers source tarball.

#include <stdio.h>
#include <dbi/dbi.h>

int main() {
    dbi_conn conn;
    dbi_result result;
    dbi_inst instance;

    double threshold = 4.333333;
    unsigned int idnumber;
    const char *fullname;
    
    dbi_initialize_r(NULL, &instance);
    conn = dbi_conn_new_r("mysql", instance);

    dbi_conn_set_option(conn, "host", "localhost");
    dbi_conn_set_option(conn, "username", "your_name");
    dbi_conn_set_option(conn, "password", "your_password");
    dbi_conn_set_option(conn, "dbname", "your_dbname");
    dbi_conn_set_option(conn, "encoding", "UTF-8");

    if (dbi_conn_connect(conn) < 0) {
      printf("Could not connect. Please check the option settings\n");
    }
    else {
      result = dbi_conn_queryf(conn, "SELECT id, name FROM coders "
                                  "WHERE hours_of_sleep > %0.2f", threshold);
    
      if (result) {
	while (dbi_result_next_row(result)) {
	  idnumber = dbi_result_get_uint(result, "id");
	  fullname = dbi_result_get_string(result, "name");
	  printf("%i. %s\n", idnumber, fullname);
	}
	dbi_result_free(result);
      }
      dbi_conn_close(conn);
    }
    
    dbi_shutdown_r(instance);

    return 0;
}

Compile with: gcc -lm -ldl -ldbi -o foo foo.c

Note: The -ldl option is not required on systems that implement the dynamic linking in their libc (like FreeBSD). You may also have to throw in something like -I/usr/local/include and -L/usr/local/lib to help gcc and ld find the libdbi headers and libraries.

Of course, a complete program should check for errors more thoroughly. This example keeps error-checking at a minimum for the sake of clarity. There are also other ways to retrieve data after a successful query. Keep reading on to see the rest.