SOCI - The C++ Database Access Library

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.

Getting the number of rows affected by an operation

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 ...
}

Working with 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 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 ...
}

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 code creates the blob object and uses two calls to the get_back_end function (on both the session and the blob objects) to get access to the actual backend objects. Assuming that it is the "oracle" backend which is in use, the downcasts allow to access all relevant low-level handles and use them in the call to the OCILobDisableBuffering function. This way, the BLOB handle was configured in a way that the SOCI library alone would not allow.

rowid rid(sql); // sql is a session object
sql << "select oid from mytable where id = 7", into(rid);

postgresql_rowid_back_end * rbe = static_cast<postgresql_rowid_back_end *>(rid.get_back_end());

unsigned long oid = rbe->value_;

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.

Copyright © 2012 Vadim Zeitlin

Copyright © 2004-2006 Maciej Sobczak, Stephen Hutton