beyond.md

Beyond standard SQL

Sometimes the standard SQL is not enough and database-specific syntax needs to be used. When possible and practical, SOCI provides wrappers hiding the differences between the backends and this section describes these wrappers. And, if this is still not enough, you can use the backend-specific methods directly as described below.

Affected rows

It can be useful to know how many rows were affected by the last SQL statement, most often when using INSERT, UPDATE or DELETE. SOCI provides statement::get_affected_rows() method allowing to do this:

statement st = (sql.prepare << "update some_table ...");
st.execute(true);

if ( !st.get_affected_rows() )
{
    ... investigate why no rows were modified ...
}

Portability note

This method behaviour in case of partially executed update, i.e. when some records were updated or inserted while some other have failed to be updated or inserted, depends on the exact backend and, in the case of ODBC backend, on the exact ODBC driver used. It can return -1, meaning that the number of rows is unknown, the number of rows actually updated or the total number of affected rows.

Sequences

It is common to have auto-incrementing database fields or fields whose value come from a sequence. In the latter case you need to retrieve the value of the field for a new row before inserting it into the database. In the former case, this is unnecessary but you may still want to know the value generated by the database, e.g. to use it as a foreign key in another table. So it would be useful to have a way to obtain the value of such a field. But, of course, to make life of database programmers more interesting, different products usually support either autoincrement fields or sequences but not both -- and they use different syntaxes for them, too. SOCI tries to help to deal with this unfortunate situation by providing two functions: session::get_next_sequence_value() and session::get_last_insert_id.

If you know which kind of database you use, you may use only one of them: when working with sequences, the first one allows to generate the next value in a sequence and when working with autoincrement fields, the second one retrieves the last value generated for such a field for the given table.

However if you use multiple SOCI backends or even just a single ODBC backend but support connecting to databases of different types, you actually must use both of them in the following way to insert a row:

long long id;
statement st;
if ( sql.get_next_sequence_value("table_sequence", id) )
{
    st << "insert into table(id, f1, f2) values(:id, :f1, :f2)",
        use(id), use(f1), use(f2);
}
else
{
    // We're not using sequences, so don't specify the value,
    // it will be automatically generated by the database on insert.
    st << "insert into table(f1, f2) value(:f1, :f2)",
        use(f1), use(f2);

    // If the ID used for the above row is needed later, get it:
    if ( !sql.get_last_insert_id("table", id) )
        ... unexpected error, handle appropriately ...
}

Please note that, generally speaking, with the backends implementing get_last_insert_id(), it is impossible to predict the next auto-generated value before the row is actually inserted due to the possibility of concurrent access to the database from another client. However if the application has exclusive access to the database, it can be assumed that the next value will be equal to the sum of the last insert ID and the increment used for the column (in most cases the increment will be 1 and some databases only support using 1 as the increment, but others, e.g. Microsoft SQL Server with its IDENTITY(start, increment) columns, can use arbitrary values). In particular, calling get_last_insert_id() for the just created, and still empty, table returns 0 (or start - increment in the SQL Server case), so that adding the increment value to it still corresponds to the next value that will be used.

Portability note

These methods are currently only implemented in Firebird, MySQL, ODBC, PostgreSQL and SQLite3 backends.

Beyond SOCI API

As the original name of the library (Simple Oracle Call Interface) clearly stated, SOCI is intended to be a simple library, targeting the majority of needs in regular C++ application. We do not claim that everything can be done with SOCI and it was never the intent of the library. What is important, though, is that the simplicity of the library does not prevent the client applications from reaching into the low-level specifics of each database backend in order to achieve special configuration or performance goals.

Most of the SOCI classes have the getBackEnd method, which returns the pointer to the actual backend object that implements the given functionality. The knowledge of the actual backend allows the client application to get access to all low-level details that are involved.

blob b(sql);

oracle_session_back_end * sessionBackEnd = static_cast<oracle_session_back_end *>(sql.get_back_end());
oracle_blob_back_end * blobBackEnd = static_cast<oracle_blob_back_end *>(b.get_back_end());

OCILobDisableBuffering(sessionBackEnd->svchp_, sessionBackEnd->errhp_, blobBackEnd->lobp_);

The above example retrieves the rowid ("something" that identifies the row in the table) from the table and uses the get_back_end function to extract the actual object that implements this functionality. Assuming that it is the "postgresql" backend which is in use, the downcast is performed to use the postgresql_rowid_back_end interface to get the actual OID value that is a physical, low-level implementation of row identifier on PostgreSQL databases.

In order for any of the above to compile, you have to explicitly #include the appropriate backend's header file.

Please see the header file related to the given backend to learn what low-level handles and descriptors are available.