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. This saves processor time by eliminating the statement compilation step each time the statement is executed. In eXtremeSQL this is done by calling mcosql_prepare_statement() to compile the statement; then mcosql_execute_prepared_statement() is called to execute the precompiled statement or mcosql_execute_prepared_query() is called to execute queries and return a result set. 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 mcosql_prepare_statement()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 following code snippet defines and compiles 5 prepared statements:

     
    int2 delta;
    unt1 aid;
    unt4 tid;
    int8 bid;
    char historyFiller[10] = “----------“;
    database_t *       engine;
    transaction_t * trans;
    prepared_statement stmt[5];
    ...
     
    mcosql_prepare_statement(engine, trans, stmt[0],
        "UPDATE accounts SET Abalance=Abalance+%*i2 WHERE Aid=%*u1",
                    &delta, &aid);
    mcosql_prepare_statement(engine, trans, stmt[1],
        "SELECT Abalance FROM  accounts WHERE Aid=%*u1", &aid);
    mcosql_prepare_statement(engine, trans, stmt[2],
        "UPDATE tellers SET Tbalance=Tbalance+%*i2 WHERE Tid=%*u4",
                    &delta, &tid);
    mcosql_prepare_statement(engine, trans, stmt[3],
        "UPDATE branches SET Bbalance=Bbalance+%*i2 WHERE Bid=%*i8",
                    &delta, &bid);
    mcosql_prepare_statement(engine, trans, 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.)

The SQL insert, update and delete statements are executed as follows:

     
    mcosql_execute_prepared_statement(engine, stmt[0]);
     

And queries are executed with the mcosql_execute_prepared_query() function to return a data_source_t struct. The function signature is:

     
    status_t mcosql_execute_prepared_query(database_t database, transaction_t trans,
                        prepared_statement_t stmt,
                        data_source_t* data_source);
     

It is also possible to prepare a statement in one thread and execute the statement in a different thread.