# 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