cpdo  Artifact Content

Artifact 0ac4f6de97616b2ff1caf9200d15260b15c327ee:

Wiki page [HOWTO] by stephan 2011-05-17 18:39:28.
D 2011-05-17T18:39:28.133
L HOWTO
P f7dd9bac0bc941a80ea2b018399c9053ce043e80
U stephan
W 10206
<strong>ACHTUNG: THE CPDO WIKI IS NOW (AS OF 2011-May-17) MAINTAINED ON A DEDICATED WIKI SITE:</strong> [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:

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

<h1>Prologue: Member- vs. Non-member Functions</h1>

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

<verbatim>
myStatement->api->step( myStatement );
myDriver->api->close( myDriver );
</verbatim>

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:

<verbatim>
cpdo_step( myStatement );
cpdo_close( myDriver );
</verbatim>

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:

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

See the above article for the details.

<h1>Prologue Part 2: Result Sets vs. Statements</h1>

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 <tt>cpdo_stmt</tt> 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 <tt>cpdo_result_set</tt> class here.

<h1>Connecting</h1>

There are two options.

First, the simple way (including error handling):

<verbatim>
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);
}
</verbatim>

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

<verbatim>
// 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);
}
</verbatim>


<h1>Executing "simple" SQL</h1>

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

<verbatim>
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
}
</verbatim>

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

<h1>Preparing Statements</h1>

<verbatim>
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);
}
</verbatim>

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 <tt>cpdo_prepare_f()</tt> works identically but uses printf-like format specifiers (plus a few extensions - see the docs for <tt>cpdo_mprintf()</tt> for details).

<b>ACHTUNG:</b> 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.

<h1>Executing Prepared Statements</h1>

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

<verbatim>
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.
</verbatim>

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

<verbatim>
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 );
</verbatim>

<h1>Fetching Data</h1>

<b>ACHTUNG:</b> 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:

<verbatim>
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 );
</verbatim>

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


<h1>Binding Values</h1>

<b>ACHTUNG:</b> 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:

<verbatim>
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);
</verbatim>

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

<h1>Getting the last Insertion ID</h1>

Drivers may optionally support the ability to fetch the last record
insertion ID. The [cpdo_sqlite3|sqlite3] and [cpdo_mysql5|mysql]
bindings support this.

<verbatim>
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 );
</verbatim>

That final <tt>NULL</tt> 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.

Z d26666bc955fb7c419b6bd8eb70ee372