The whole SOCI library consists of only two files: soci.h and soci.cpp. The first needs to be #included in your own code and the second should be just added to the whole project for compilation and linking. There was no reason to make a precompiled library out of this single file.
Everything in SOCI is declared in the namespace SOCI. All the following examples assume that your code begins with something like:
#include "soci.h" // other includes using namespace SOCI; // ...
All DB-related errors manifest themselves as exceptions of type SOCIError, which is derived from std::runtime_error. This allows to handle Oracle errors within the standard exception framework:
int main()
{
try
{
// regular code
}
catch (exception const &e)
{
cerr << "Bang! " << e.what() << endl;
}
}
In addition, the SOCIError class has a public errNum_ member, which contains the Oracle error code. Of course, in order to access this code you have to catch the exception as SOCIError:
int main()
{
try
{
// regular code
}
catch (SOCIError const &e)
{
cerr << "Oracle error: " << e.errNum_
<< " " << e.what() << endl;
}
catch (exception const &e)
{
cerr << "Some other error: " << e.what() << endl;
}
}
The class Session encapsulates the database connection and other OCI-related details, which are common to all the statements that will be later executed. Its constructor expects three string parameters, which are service name, user name and password:
Session sql("service", "user", "password");
The constructor either connects successfully, or throws the exception.
It is possible to have many active Sessions at the same time.
In many cases, the SQL query is intended to be executed only once, which means that statement parsing and execution can go together.
The Session class provides a special once member, which triggers parsing and execution of such one-time statements:
sql.once << "drop table person";
For shorter syntax, the following form is also allowed:
sql << "drop table person";
The IOStream-like interface is exactly what it looks like, so that the statement text can be composed of many parts, involving anything that is streamable (including custom classes, if they have appropriate operator<<):
string tableName = "person"; sql << "drop table " << tableName;
Note:
The Oracle documentation uses two terms: defining (for instructing the library where the output data should go) and binding (for the input data and input/output PL/SQL parameters). For the sake of simplicity, SOCI uses the term binding for both of these.
Binding output data:
The into expression is used to add binding info to the statement:
int count; sql << "select count(*) from person", into(count); string name; sql << "select name from person where id = 7", into(name);
Binding input data:
The use expression is used (no pun intended) to associate the SQL placeholder (written with colon) with the local data:
int val = 7; sql << "insert into numbers(val) values(:val)", use(val);
In the above statement, the first "val" is a column name (assuming that there is appropriate table), the second "val" (with colon) is a placeholder and its name is ignored here, and the third "val" is a name of local variable.
To better understand the meaning of each "val" above, consider also:
int number = 7; sql << "insert into numbers(val) values(:blabla)", use(number);
Binding by position
If there is more output or input "holes" in the single statement, it is possible to use many into and use expressions, separated by commas, where each expression will be responsible for the consecutive "hole" in the statement:
string firstName = "John", lastName = "Smith";
int personId = 7;
sql << "insert into person(id, firstname, lastname) values(:id, :fn, :ln)",
use(personId), use(firstName), use(lastName);
sql << "select firstname, lastname from person where id = :id",
into(firstName), into(lastName), use(personId);
In the code above, the order of "holes" in the SQL statement and the order of into and use expression should match.
Binding by name
The SQL placeholders that have their names (with colon) can be bound by name.
This allows to use different order:
string firstName = "John", lastName = "Smith";
int personId = 7;
sql << "insert into person(id, firstname, lastname) values(:id, :fn, :ln)",
use(firstName, "fn"), use(lastName, "ln"), use(personId, "id");
or bind the same local data to many "holes" at the same time:
string addr = "...";
sql << "update person"
" set mainaddress = :addr, contactaddress = :addr"
" where id = 7",
use(addr, "addr);
Types:
The following types are currently supported for use with into and use expressions:
char, short, int, unsigned long, double (for numeric values)char*, char[], std::string (for string values)std::tm, std::time_t (for datetime values)SOCI::Statement (for nested statements and PL/SQL cursors)SOCI::BLOB (for Binary Large OBjects)SOCI::RowID (for row identifiers)See the test code that accompanies the library to see how each of these types is used.
The library can be extended to support other types (including user-defined classes). The extension points are the IntoType<T> and UseType<T> class templates, which specializations can be user-provided. These specializations need to implement the interface defined by, respectively, the IntoTypeBase and UseTypeBase classes.
Note that when specializing these template classes for handling your own types, you are free to choose the expected parameters for the classes' constructors. The template functions into and use support up to 5 parameters, which are just forwarded to the constructor of appropriate class. The only convention is that when the indicator variable is used (see below), it should appear in the second position.
Please refer to the library source code to see how this is done for the standard types.
In order to support null values and other conditions which are not real errors, the concept of indicator is provided.
For example, when the following SQL query is executed:
select name from person where id = 7
there are three possible outcomes:
Whereas the first alternative is easy, the other two are more complex. Moreover, they are not necessarily errors from the application's point of view and what's more interesting, they are different and the application may wish to detect which is the case.
The following example does this:
string name;
eIndicator ind;
sql << "select name from person where id = 7", into(name, ind);
switch (ind)
{
case eOK:
// the data was returned without problems
break;
case eNoData:
// no such person
break;
case eNull:
// there is a person, but he has no name (his name is null)
break;
case eTruncated:
// the name was returned only in part,
// because the provided buffer was too short
// (not possible with std::string, but possible with char* and char[])
break;
}
The use of indicator variable is optional, but if it is not used and the result would be either eNoData or eNull, then the exception is thrown. This means that you should use indicator variables everywhere where the application logic (and database schema) allow "no such object" or "attribute not set" conditions.
Indicator variables can be also used when binding input data, to control whether the data is to be used as provided, or explicitly overrided to be null:
int id = 7;
string name;
eIndicator ind = eNull;
sql << "insert into person(id, name) values(:id, :name)",
use(id), use(name, ind);
In the above example, the row is inserted with name attribute set to null.
Consider the following examples:
// Example 1.
for (int i = 0; i != 100; ++i)
{
sql << "insert into numbers(value) values(" << i << ")";
}
// Example 2.
for (int i = 0; i != 100; ++i)
{
sql << "insert into numbers(value) values(:val)", use(i);
}
Both examples will populate the table numbers with the values from 0 to 99.
The problem is that in both examples, not only the statement execution is repeated 100 times, but also the statement parsing and preparation. This means unnecessary overhead.
The following example uses the class Statement explicitly, by preparing the statement only once and repeating its execution with changing data (note the use of prepare member of Session class):
int i;
Statement st = (sql.prepare <<
"insert into numbers(value) values(:val)",
use(i));
for (i = 0; i != 100; ++i)
{
st.execute(1);
}
The 1 parameter given to the execute method indicates the number of times the statement has to be executed. Here, it is executed once in each body of the loop.
It is also possible to read all the numbers written in the above example:
int i;
Statement st = (sql.prepare <<
"select value from numbers order by value",
into(i));
st.execute();
while (st.fetch())
{
cout << i << '\n';
}
In the above example, the execute method is called with the default parameter 0. The following fetch calls perform the actual data retrieval and cursor traversal. The end-of-cursor condition is indicated by the fetch function returning false.
The Session class provides two methods for transaction processing: commit and rollback.
The transaction is implicitly started with the first statement that modifies data in the database.
If there is any need to use more advanced features of the underlying OCI library (special connection steps, attribute manipulation, call-back functions, etc.), it can be done by accessing the low-level OCI handles available as public (see disclaimer below) members of the Session and Statement classes. Please refer to the soci.h file to see exactly what handles are available.
Disclaimer:
Since public data members are considered harmful amongst C++ programmers, some explanation is in order to defend SOCI against anticipated criticism.
The intent is to allow reading, writing and object modifications through the low-level OCI handles, to enable experienced programmers to reuse SOCI in most demanding situations. The usual approach would be to provide the public getter/setter method pairs for private handles. However, OCI handles are opaque pointers and are strong abstractions by themselves - this is what makes them different from data members of standard types (like int or string). The getter/setter pairs (or even single accessors returning non-const references) were considered in the library design, but abandoned due to the lack of any real benefits. The only argument for providing getter/setter abstractions would be to allow future replacements of low-level OCI handles with "something else", but the reality is that OCI handles exist in a specific family and that "something else" (like handles for database other than Oracle) would not map into those abstrations. Porting the SOCI library to support other databases would require to change not only low-level handles, but also the getter/setter pairs, which would then prove to be not really abstract. Uff...
Having said that, it has to be reiterated that public data members are generally bad.