SOCI
Simple Oracle Call Interface (and more)

Exchanging data

Binding local data
Binding output data
Binding input data
Binding by position
Binding by name
Indicators
Types
Static binding
Static binding for bulk operations
Dynamic resultset binding
Extending with user-provided datatypes
Object-relational mapping
Large objects (BLOBs)

Binding local data

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 information to the statement:

int count;
sql << "select count(*) from person", into(count);

string name;
sql << "select name from person where id = 7", into(name);

In the above examples, some data is retrieved from the database and transmitted into the given local variable.
There should be as many into elements as there are expected columns in the result (see dynamic resultset binding for the exception to this rule).

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 numbers with this column), 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);

Both examples above will insert the value of some local variable into the table numbers - we say that the local variable is used in the SQL statement.
There should be as many use elements as there are parameters used in the SQL query.

Portability note:
Older versions of the PostgreSQL client API do not allow to use input parameters at all. In order to compile SOCI with those old client libraries, define the SOCI_PGSQL_NOPARAMS preprocessor name.


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);

Portability notes:
The PostgreSQL backend allows to use the "native" PostgreSQL way of naming parameters in the query, which is by numbers like $1, $2, $3, etc. In fact, the backend rewrites the given query to the native form. For portability reasons, it is recommended to use named parameters, as shown in the examples above.
The query rewriting can be switched off by compiling the backend with the SOCI_PGSQL_NOBINDBYNAME name defined.

Indicators

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:

  1. there is a person with id = 7 and his name is returned
  2. there is a person with id = 7, but he has no name (his name is null)
  3. there is no such person

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.

Indicator variables can also be used in conjunction with vector based insert, update, and select statements:

vector<string> names(100);
vector<eIndicator> inds;
sql << "select name from person where id = 7", into(names, inds);

The above example retrieves first 100 rows of data (or less). The initial size of names vector provides the (maximum) number of rows that should be read. Both vectors will be automatically resized according to the number of rows that were actually read.

The following example inserts null for each value of name:

vector<int> ids;
vector<string> names;
vector<eIndicator> nameIndicators;

for (int i = 0; i != 10; ++i)
{
ids.push_back(i);
names.push_back("");
nameIndicators.push_back(eNull);
}

sql << "insert into person(id, name) values(:id, :name)",
use(ids), use(name, nameIndicators);

Types

Static type binding

The static binding for types is most useful when the types used in the database are known at compile time - this was already presented above with the help of into and use functions.
The following types are currently supported for use with into and use expressions:

See the test code that accompanies the library to see how each of these types is used.

Static type binding for bulk operations

Bulk inserts, updates, and selects are supported through the following std::vector based into and use types:

Use of the vector based types mirrors that of the standard types, with the size of the vector used to specify the number of records to process at a time. See below for examples.

Dynamic resultset binding

For certain applications it is desirable to be able to select data from arbitrarily structured tables (e.g. via "select * ") and format the resulting data based upon its type. SOCI supports this through the SOCI::Row and SOCI::ColumnProperties classes.

Data is selected into a Row object, which holds ColumnProperties objects describing the type of data contained in each column. Once the data type for each column is known, the data can be formatted appropriately.

For example, the code below creates an XML document from a selected row of data from an arbitrary table:

Session sql("oracle", "service=db1", "user=scott", "password=tiger");

sql << "create table ex2(num NUMBER, str VARCHAR2(20), dt DATE)";

int num_in = 123;
std::string str_in("my name");
std::time_t dt_in = time(0);

sql << "insert into ex2 values(:num1, :str1, :dt1)",
use(num_in), use(str_in), use(dt_in);

Row r;
sql << "select * from ex2", into(r);

std::ostringstream doc;
doc << "<row>" << std::endl;
for(int i=0; i<r.size(); ++i)
{
const ColumnProperties& props = r.getProperties(i);
doc << '<' << props.getName() << '>';
switch(props.getDataType())
{
case eString:
doc << r.get<std::string>(i);
break;
case eDouble:
doc << r.get<double>(i);
break;
case eInteger:
doc << r.get<int>(i);
break;
case eUnsignedLong:
doc << r.get<unsigned long>(i);
break;
case eDate:
std::tm when = r.get<std::tm>(i);
doc << asctime(&when);
break;
}
doc << "</" << props.getName() << '>' << std::endl;
}
doc << "</row>";


The following table shows the type T parameter that should be passed to Row::get<T>() for each possible SOCI Data Type that can be returned from ColumnProperties::getDataType(). Row::get<T>() throws an exception of type std::bad_cast if an incorrect type T is used.

Oracle Data Type Postgres Data Type SOCI Data Type Row::get<T> specializations
number (where scale > 0)
float4, float8, numeric
eDouble double
number
(where scale = 0 and precision <
std::numeric_limits<int>::digits10)
bool, int2, int4, int8
eInteger int
number
oid
eUnsignedLong unsigned long
char, varchar, varchar2
text, varchar, cstring, char, bpchar
eString std::string
date
abstime, reltime, data, time, timestamp, timestamptz, timetz
eDate std::tm


Extending SOCI to support custom (user-defined) C++ types

SOCI can be easily extended through user code so that custom types can be used transparently to insert into, update, and select from the database.

To do so, provide an appropriate specialization of the TypeConversion struct that converts to and from one of the SOCI Base Types listed below:

Oracle Data Type SOCI Base Type
number
double
number
int
number
unsigned long
varchar, varchar2
std::string
char
char
date
std::tm

There are three required class members for a valid TypeConversion specialization:

(Note that no database-specific code is required.)

The following example shows how application code could extend SOCI to transparently support boost::gregorian::date:

#include "boost/date_time/gregorian/gregorian.hpp"

template<>
struct TypeConversion<boost::gregorian::date>
{
typedef std::tm base_type;
static boost::gregorian::date from(std::tm& t)
{
boost::gregorian::months_of_year month =
static_cast<boost::gregorian::months_of_year>(t.tm_mon + 1);

boost::gregorian::date d(t.tm_year + 1900, month, t.tm_mday);
return d;
}
static std::tm to(boost::gregorian::date& d)
{
std::tm t;

t.tm_isdst = -1;
t.tm_year = d.year() - 1900;
t.tm_mon = d.month() - 1;
t.tm_mday = d.day();
t.tm_hour = 0;
t.tm_min = 0;
t.tm_sec = 0;
std::mktime(&t); //normalize and compute remaining fields

return t;
}
};


With the above TypeConversion specialization in place, it is possible to use boost::gregorian::date directly with SOCI, binding input or output, either statically or via a dynamic resultset:

using boost::gregorian::date;

Session sql("oracle", "service=db1", "user=scott", "password=tiger");

sql << "create table test_boost(when DATE)";
date dIn(1999, boost::gregorian::Feb, 1);
sql << "insert into test_boost values(:when)", use(dIn);

date dOut(boost::gregorian::not_a_date_time);
sql << "select when from test_boost", into(dOut);
assert(dIn == dOut);

Row r;
sql << "select * from test_boost", into(r);
assert(r.get<date>(0) == dIn);

std::vector<date> datesIn;
for (int i = 2; i != 12; ++i)
{
date d(1999, boost::gregorian::Feb, i);
datesIn.push_back(d);
}
sql << "insert into test_boost values(:when)", use(datesIn);

std::vector<date> datesOut(10);
sql << "select when from test_boost where when > :d", use(dIn), into(datesOut);
assert(datesIn == datesOut);


The other possibility to extend SOCI with custom data types is to use 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.

Object-relational mapping

SOCI provides a class called Values specifically to enable object-relational mapping via TypeConversion specializations.

For example, the following code maps a Person object to and from a database table containing columns "ID", "FIRST_NAME", "LAST_NAME", and "GENDER". 

Note that the mapping is non-invasive - the Person object itself doesn't contain any SOCI-specific code:

struct Person
{
int id;
std::string firstName;
std::string lastName;
std::string gender;
};

template<> struct TypeConversion<Person>
{
typedef Values base_type;
static Person from(Values const &v)
{
Person p;
p.id = v.get<int>("ID");
p.firstName = v.get<std::string>("FIRST_NAME");
p.lastName = v.get<std::string>("LAST_NAME");

// p.gender will be set to the default value "unknown"
// when the column is null:
p.gender = v.get<std::string>("GENDER", "unknown");

// alternatively, the indicator can be tested directly:
// if (v.indicator("GENDER") == eNull)
// {
// p.gender = "unknown";
// }
// else
// {
// p.gender = v.get<std::string>("GENDER");
// }

return p;
}
static Values to(Person &p)
{
Values v;
v.set("ID", p.id);
v.set("FIRST_NAME", p.firstName);
v.set("LAST_NAME", p.lastName);
v.set("GENDER", p.gender, p.gender.empty() ? eNull : eOK);
return v;
}
};

With the above TypeConversion specialization in place, it is possible to use Person directly with SOCI:

Session sql("oracle", "service=db1", "user=scott", "password=tiger");

Person p;
p.id = 1;
p.lastName = "Smith";
p.firstName = "Pat";
sql << "insert into person(id, first_name, last_name) "
"values(:ID, :FIRST_NAME, :LAST_NAME)", use(p);

Person p1;
sql << "select * from person", into(p1);
assert(p1.id == 1);
assert(p1.firstName + p.lastName == "PatSmith");
assert(p1.gender == "unknown");

p.firstName = "Patricia";
sql << "update person set first_name = :FIRST_NAME "
"where id = :ID", use(p);

Note: the Values class is currently not suited for use outside of TypeConversion specializations.  It is specially designed to facilitate object-relational mapping when used as shown above.

Large objects (BLOBs)

The SOCI library provides also an interface for basic operations on large objects (BLOBs - Binary Large OBjects).

BLOB b(sql); // sql is a Session object
sql << "select mp3 from mymusic where id = 123", into(b);

The following functions are provided in the BLOB interface, mimicking the file-like operations:

The offset parameter is always counted from the beginning of the BLOB's data.

Portability notes:
  1. The way to define BLOB table columns and create or destroy BLOB objects in the database varies between different database engines. Please see the SQL documentation relevant for the given server to learn how this is actually done. The test programs provided with the SOCI library can be also a simple source of full working examples.
  2. The trim function is not currently available for the PostgreSQL backend.

Previous (Connections and simple queries)
Next (Statements, procedures and transactions)


Copyright © 2004-2006 Maciej Sobczak, Stephen Hutton