ToC path:

Executing eXtremeSQL Prepared Statements in C++

As explained in the Embedded SQL Application Development in C++ page, it is sometimes useful to create a SQL statement that will be executed multiple times. In eXtremeSQL, this is done by calling one of the prepare() or vprepare() methods in class McoSqlEngine to compile the SQL statement into a PreparedStatement instance. This has the advantages of eliminating the statement compilation step each time the statement is executed and binding the statement argument pointers only once. However, note that, as a consequence of using only pointer arguments, the application must keep the argument variables passed to prepare()by pointer (i.e. as references) in scope during the entire life-time of that prepared statement, and assure that the arguments have actual values at the moment of execution of that prepared statement. (Please see eXtremeSQL Statement Argument Substitution page for the detailed list of argument substitution format specification.)

Note that the Python, JNI and .NET Framework wrapper API's have no prepared statements support. This is because of the absence of pointers in the Python, Java and C# languages. As result it is impossible to store and then use a variable indirectly.

Prepare

The prepare() method is defined as follows:
 
    virtual void prepare(PreparedStatement &stmt, char const* sql, ...);
     
Then the executePreparedStatement()method is called to execute the pre-compiled statement or executePreparedQuery() is called to execute queries and return a result set.

For example, the following code snippet defines and compiles 5 PreparedStatements:

 
    #include <mcosql.h>
    #include <commonSQL.h>
    #include "sqldb.h"
     
    McoSqlEngine engine;
     
    void Prepare()
    {
        int2 delta;
        unt1 aid;
        unt4 tid;
        int8 bid;
        char historyFiller[10] = “----------“;
        PreparedStatement stmt[5];
         
        engine.open( db_name, sqldb_get_dictionary(), DATABASE_SIZE, MEMORY_PAGE_SIZE);
        ...
     
        engine.prepare(stmt[0], "UPDATE accounts SET Abalance=Abalance+%*i2 WHERE Aid=%*u1",
                    &delta, &aid);
        engine.prepare(stmt[1], "SELECT Abalance FROM  accounts WHERE Aid=%*u1", &aid);
        engine.prepare(stmt[2], "UPDATE tellers SET Tbalance=Tbalance+%*i2 WHERE Tid=%*u4",
                    &delta, &tid);
        engine.prepare(stmt[3], "UPDATE branches SET Bbalance=Bbalance+%*i2 WHERE Bid=%*i8",
                    &delta, &bid);
        engine.prepare(stmt[4],
            "INSERT INTO account_history(Tid, Bid, Aid, delta, htime, filler) VALUES”
            “ (%*u4,%*i8,%*u1,%*i2,now,%s)", &tid, &bid, &aid, &delta, historyFiller);
        ...
    }

(Note the use of “%*iN” and “%*uN” placeholders for the signed and unsigned integer variables where “N” indicates the integer width. See also page Parameter Substitution Format Specifiers for further details on parameter substitution.)

Then the statements can be executed as follows:

 
    McoSqlEngine engine;
     
    void ExecuteStatements()
    {
        engine.executePreparedStatement(stmt[0]);
        engine.executePreparedStatement(stmt[2]);
        engine.executePreparedStatement(stmt[3]);
        engine.executePreparedStatement(stmt[4]);
    }

And queries are executed instantiating a QueryResult for the query result set as follows:

 
    McoSqlEngine engine;
     
    void PerformQuery()
    {
        QueryResult *result = engine.executePreparedQuery(stmt[1]);
        Cursor * cursor = result->records();
         
        while (cursor->hasNext())
        {
            Record * rec = cursor->next();
             
            // Process the result row
            ...
        }
    }

Note that it is also possible to prepare a statement in one thread and execute the statement in a different thread. However, be aware that when executing a prepared query, the object of class PreparedStatement must exist until the release of the resulting DataSource object. Otherwise the internal structures of the result set could be corrupted.

The vprepare() methods

The prepare() method uses a variable arguments list (please see the following page for details of the stdarg interface). The SqlEngine base class of McoSqlEngine provides two additional "lower level" vprepare() methods:
     
    virtual void vprepare(PreparedStatement &stmt, char const* sql, va_list* list);
     
    virtual void vprepare(PreparedStatement &stmt, char const* sql, ParamDesc* params);
     
The first of these two methods, which uses the va_list argument, is actually used in the implementation of prepare() as follows:
 
    void SqlEngine::prepare(PreparedStatement &stmt, char const* sql, ...)
    {
        va_list list;
        va_start(list, sql);
        vprepare(stmt, sql, &list);
        va_end(list);
    }
     
The second requires creating an array of ParamDesc instances which specify the type and address of each parameter. For example:
 
    #include <mcosql.h>
    #include <commonSQL.h>
    #include "sqldb.h"
     
    McoSqlEngine engine;
     
    int main( int argc, char ** argv )
    {
        PreparedStatement stmt;
        ParamDesc params[2];
 
        int4 id = 0;
        char str[10];
 
        params[0].type = tpInt4;
        params[0].ptr = &id;
        params[0].lenptr = NULL;
        params[1].type = tpString;
        params[1].ptr = &str;
        params[1].lenptr = NULL;
        ...
        engine.open( db_name, sqldb_get_dictionary(), DATABASE_SIZE, MEMORY_PAGE_SIZE);
         
        engine.vprepare(stmt, "insert into AnObject values (?,?)", params);
        id = 1; strcpy(str, "one");
        engine.executePreparedStatement(stmt);
 
        id = 2; strcpy(str, "two");
        engine.executePreparedStatement(stmt);
         
        id = 3; strcpy(str, "three");
        engine.executePreparedStatement(stmt);
        ...
    }
     
The ParamDesc structure is defined in include/sql/sqlcpp.h as follows:
 
    struct ParamDesc
    {
        Type type;
        void* ptr;
        int* lenptr;
        bool indirectStr;
    };
     
The type, ptr and lenptr elements specify the variable type, its address and the address of an integer that specifies the length of the variable if of type tpString , tpUnicode or tpBinary; for all other data types it should be set to NULL. (For fields of type tpBinary it is mandatory to specify lenptr, for data types tpString and tpUnicode it is optional; if lenptr is set to NULL for a variable of type tpString or tpUnicode the size will be automatically calculated from the actual size of string specified by field ptr.)

The indirectStr element must be set false for variables of type char* or wchar_t* and set true for variables of type char** or wchar_t**; the size of the string variable can optionally be specified in the lenptr element.
Note that usually a string passed as argument is a pointer to the first character of string. For example the following string definition:
 
    const char *mystring = "The Stranglers";
     
could be passed as an argument to an executeStatement() method with the substitution placeholder %*S. This could be called a "direct string pointer". By contrast, a prepared statement works with indirect (by pointer) access to the arguments. So the substitution placeholder %*s means a pointer to a string which in its turn is a pointer to char. For example the following definitions could be used for "indirect string" arguments:
 
    const char *mystring = "Ramones";
    const char **ptr_to_string = &mystring;
     
The following code snippet demonstrates how string arguments can be used with prepare() and the ParmDesc variant of the vprepare() method :
 
    const char *mystring = "The Stranglers";
    const char **ptr_to_string = &mystring;
    PreparedStatement stmt;
 
    // Indirect string 'prepare'
    engine.prepare(stmt, "select * from Bands where name=%*s", ptr_to_string);
     
    // Indirect string 'vprepare'
    ParamDesc desc;
    desc.type = tpString;
    desc.ptr = ptr_to_string;
    desc.lenptr = NULL;
    desc. indirectStr = true;
    engine.vprepare("select * from Bands where name=?", &desc);
     
    // Direct string 'prepare'
    engine.prepare(stmt, "select * from Bands where name=%*S", mystring);
 
    // Direct string 'vprepare'
    desc.type = tpString;
    desc.ptr = mystring;
    desc.lenptr = NULL;
    desc. indirectStr = false;
    engine.vprepare("select * from Bands where name=?", &desc);
     
The same applies to wide character strings. The substitution placeholder %*w assumes an argument of type wchar_t* * ("pointer to a pointer to a wide character"), while the placeholder %*W assumes an argument of type wchar_t*.
ParmDesc Types

Valid values for the type element (as enumerated in include/sql/value.h) and the corresponding C/C++ variable types are defined in the following:

type specification
C/C++ variable type

tpNull NULL

tpBool, tpInt1, tpUInt1 char

tpInt2 short

tpUInt2 unsigned short

tpInt4 int

tpUInt4 unsigned int

tpInt8
long int

tpUInt8 uint64_t

tpReal4 float

tpReal8 double

tpDateTime mco_datetime (unsigned int)

tpNumeric double

tpUnicode wstring (wchar_t*)

tpString string (char *)

tpBinary byte[]

tpInt = tpInt8 int64_t

tpReal = tpReal8 double

tpReference autoid_t

type specification	C/C++ variable type
tpNull	NULL
tpBool, tpInt1, tpUInt1	char
tpInt2	short
tpUInt2	unsigned short
tpInt4	int
tpUInt4	unsigned int
tpInt8	long int
tpUInt8	uint64_t
tpReal4	float
tpReal8	double
tpDateTime	mco_datetime (unsigned int)
tpNumeric	double
tpUnicode	wstring (wchar_t*)
tpString	string (char *)
tpBinary	byte[]
tpInt = tpInt8	int64_t
tpReal = tpReal8	double
tpReference	autoid_t