cpdo  Update of "HOWTO"

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Artifact ID: 0ac4f6de97616b2ff1caf9200d15260b15c327ee
Page Name:HOWTO
Date: 2011-05-17 18:39:28
Original User: stephan
Parent: f7dd9bac0bc941a80ea2b018399c9053ce043e80
Content

ACHTUNG: THE CPDO WIKI IS NOW (AS OF 2011-May-17) MAINTAINED ON A DEDICATED WIKI SITE: http://whiki.wanderinghorse.net/wikis/cpdo/?page=HOWTO

This page demonstrates how to do most things with cpdo.

The API docs are quite detailed, and should be your first reference source. The test application which comes with the library is the next stop. Those interested in implementing a db wrapper should start by looking at the cpdo_sqlite3 code (it is relatively simple). cpdo_mysql5 demonstrates much more complex usage, due largely to the memory management requirements the MySQL client API imposes on clients.

The header file required by client code:

#include "wh/cpdo/cpdo.h"
// For driver implementors, probably this one instead:
#include "wh/cpdo/cpdo_internal.h"

Prologue: Member- vs. Non-member Functions

The core data types, cpdo_driver and cpdo_stmt are object-oriented and their driver-specific behaviours are modeled as member functions. They can be called like this:

myStatement->api->step( myStatement );
myDriver->api->close( myDriver );

The API includes a number of non-member functions which work identically but might be easier on the eyes for some users. For example, the above can be rewritten:

cpdo_step( myStatement );
cpdo_close( myDriver );

Use whichever form you prefer. The are equivalent. Sometimes the former form is required because there is not equivalent mapping into the "friendly form." The mysterious "api" member is explained in gross detail in this article:

http://wanderinghorse.net/computing/papers/#oo_c

So we won't go into details here except to say that this approach to OO in C saves us boatloads of memory for each driver and prepared statement instance when compared to the more conventional approaches which place member functons directly in their data type. For example:

// This is NOT valid cpdo:
myStatement->step(myStatement);
myDriver->close(myDriver);

See the above article for the details.

Prologue Part 2: Result Sets vs. Statements

Most database libraries have distinctly separate concepts of "statements" (SQL) and "result sets" (the fetched data). The PHP PDO interfaces combine these two concepts into one interface, called PDOStatement. This API does the same with the cpdo_stmt class. While this is a large departure from common DB conventions, in my experience the PDO model is quite easy to use.

So do not expect to see a cpdo_result_set class here.

Connecting

There are two options.

First, the simple way (including error handling):

cpdo_driver * drv = NULL; // do not forget the NULL
char const dsn = "sqlite3:mydb.sqlite";
int rc = cpdo_driver_new_connect( &drv, dsn, NULL/*userName*/, NULL/*password*/);
if( rc ) {
    printf("cpdo error #%d: %s\n", rc, cpdo_rc_string(rc) );
    if( drv ) { // means we found a driver but connection failed
       char const * errMsg = NULL;
       drv->api->error_info( drv, &errMsg, NULL, &rc );
       printf("Driver error #%d: %s\n", rc, errMsg );
       drv->api->close(drv);
    }
    return ...;
}
else {
   ... use driver, and eventually close it ...
    drv->api->close(drv);
}

Second, the less simple way (also including error handling):

// First create a new driver instance without connecting:
cpdo_driver * drv = NULL; // do not forget the NULL
int rc = cpdo_driver_new( &drv, "sqlite3", NULL/*currently unused*/);
if( rc ) {
    // drv is always NULL here
    printf("cpdo error #%d: %s\n", rc, cpdo_rc_string(rc) );
    return ...;
}

// Then connect:
cpdo_connect_opt opt = cpdo_connect_opt_empty;
opt.dsn = "sqlite3:mydb.sqlite";
rc = cpdo_driver_connect( drv, &opt );
if( ! rc ) {
    char const * errMsg = NULL;
    drv->api->error_info( drv, &errMsg, NULL, &rc );
    printf("Driver Error #%d: %s\n", rc, errMsg );
    drv->api->close(drv);
}
else {
   ... use driver, and eventually close it ...
    drv->api->close(drv);
}

Executing "simple" SQL

Non-fetching queries with no bound parameters can be sent as follows (including error handling):

char const * sql = "DROP TABLE X";
int rc = cpdo_exec( drv, sql, strlen(sql) );
if( rc == cpdo_rc.CheckDbError ) {
    char const * errMsg = NULL;
    drv->api->error_info( drv, &errMsg, NULL, &rc );
    printf("Db error #%d: %s\n", rc, errMsg );
}
else if( rc ) {
  // if this happens, one of the arguments to cpdo_exec() was invalid.
}
else {
  // success
}

The function cpdo_exec_f() works identically but uses printf-like format specifiers (plus a few extensions - see the docs for cpdo_mprintf() for details).

Preparing Statements

cpdo_stmt * st = NULL;
char const * sql = "...";
uint32_t const len = strlen(sql);
int rc = cpdo_prepare( drv, &st, sql, len );
if( rc ) { ... error. st==NULL ... }
else {
    ... use st and eventually free it ...
    cpdo_stmt_finalize( st );
    // or:
    st->api->finalize(st);
}

Preparing a statement compiles it for use with the database, and might generate network traffic (but doesn't in my experience), but it does not process the query. To do that we need to "step" through it. See below for how.

The function cpdo_prepare_f() works identically but uses printf-like format specifiers (plus a few extensions - see the docs for cpdo_mprintf() for details).

ACHTUNG: drivers are not required to support multiple open statements at one time, so do not depend on such behaviour and keep only one statement opened at a time for a given connection.

Executing Prepared Statements

For non-fetching queries, like INSERT and CREATE TABLE, we have to step through the statement one time:

cpdo_step_code scode = st->api->step(st).
// For non-fetching queries scode will be one of
// CPDO_STEP_ERROR or CPDO_STEP_DONE. Fetching
// queries might return CPDO_STEP_OK instead.

For fetching queries (SELECT and some db-specific queries like MySQL's SHOW TABLES) we need to iterate over them:

cpdo_step_code scode;
while( CPDO_STEP_OK == (scode = st->api->step(st)) ) {
  ... use the row's results ...
}
// For fetching queries scode will be one of
// CPDO_STEP_ERROR or CPDO_STEP_DONE now unless
// we abort the loop early.
if( CPDO_STEP_ERROR == scode ) {
   ... error ...
}

// Always clean up the statement when done:
st->api->finalize( st );

Fetching Data

ACHTUNG: the "get" functions use 0-based indexes whereas the "bind" functions use 1-based indexes. i didn't create these conventions, i just try to follow them.

Here we demonstrate the easiest approach, which doesn't give the caller any direct error information (but is simpler to use). After preparing the SELECT statement (or driver-specific pseudo-select, like MySQL's "SHOW TABLES"), loop over the results like this:

cpdo_step_code scode;
while( CPDO_STEP_OK == (scode = st->api->step(st)) )
{
    cpdo_data_type type = cpdo_get_type(st,0);
    char const * val = cpdo_get_string( st, 0, NULL );    
    int32_t i32 = cpdo_get_int32( st, 1 );
    ...
}
// Always clean up the statement when done:
st->api->finalize( st );

Instead of using cpdo_get_xxx() you can get more complete error information by using st->api->get->xxx(), since they return an error code if fetching of the column data fails.

Binding Values

ACHTUNG: the "get" functions use 0-based indexes whereas the "bind" functions use 1-based indexes. i didn't create these conventions, i just try to follow them.

The API supports binding parameters by index (1-based) or name, but binding by name is an optional feature, so drivers are not required to implement it.

Here's an example, with error handling:

char const * sql = "INSERT INTO t (a,b,c) VALUES(?,?.?)";
uint32_t const slen = strlen(sql);
cpdo_stmt * st = NULL;
int rc = cpdo_prepare( drv, &st, sql, slen );
if( rc ) ... error ...
else do {
    rc = cpdo_bind_int32( st, 1, 42 );
    if( rc ) break;
    rc = cpdo_bind_double( st, 2, 42.24 );
    if( rc ) break;
    rc = cpdo_bind_string( st, 3, "hi!", 3 );
    if ( rc ) break;
    // Note that drivers are not required to be able to convert
    // non-string fields to strings, but the MySQL and sqlite3
    // bindings support this.
} while(0);
if( rc ) { ... error ...
    cpdo_stmt_finalize(st);
    return ...;
}

// To send the query, step() it one time:
cpdo_step_code scode = cpdo_step(st);
if( CPDO_STEP_ERROR == scode ) {
   ... you know the routine ...
}

// The data is now in the DB, and we can clean up:
cpdo_stmt_finalize(st);

It is not recommended that SQL use a mixture of ?-style and :namedParameters, as drivers may have no control over how the underlying db handles such cases.

Getting the last Insertion ID

Drivers may optionally support the ability to fetch the last record insertion ID. The sqlite3 and mysql bindings support this.

uint64_t id = 0;
int rc = drv->api->last_insert_id( drv, &id, NULL );
if( cpdo_rc.UnsupportedError == rc ) {
    ... driver does not support this feature ...
}
else if( rc == cpdo_rc.CheckDbError ) {
   ... same as demonstrated above ...
}
else if( rc ) {
   ... error almost certainly caused by invalid arguments ...
}
else {
   ... success! ...
}

// Or the simpler, non-error-reporting form:
id = cpdo_last_insert_id( drv, NULL );

That final NULL argument is a string which provides a "hint" to the driver about where to get the value from. Most drivers don't need this, and accept NULL, but some drivers might require a sequence or table name to be passed so that they know where to look.