into
expression is used to add binding information to
the
statement:
int count; |
into
elements as there are
expected columns in the result (see dynamic
resultset binding for the
exception to this rule).The use
expression is used (no pun intended) to
associate the SQL placeholder (written with colon) with the local data:
int val = 7; |
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; |
numbers
- we say that the local variable is used in the SQL statement.use
elements as there are
parameters used in the SQL query.SOCI_PGSQL_NOPARAMS
preprocessor
name.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"; |
into
and use
expression should
match.
string firstName = "John", lastName = "Smith"; |
string addr = "..."; |
$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.SOCI_PGSQL_NOBINDBYNAME
name defined.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; |
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; |
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); |
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; |
into
and use
functions.into
and use
expressions:
char
(for character values)short
, int
, unsigned
long
, double
(for numeric values)char*
, char[]
, std::string
(for string values)std::tm
(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.
Static
type binding for bulk operations
Bulk inserts, updates, and selects are supported through the
following std::vector
based into and use types:
std::vector<char>
std::vector<short>
std::vector<int>
std::vector<unsigned long>
std::vector<double>
std::vector<std::string>
std::vector<std::tm>
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"); |
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
|
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:
base_type
trait, defining the base typefrom()
static member function, converting from
the base typeto()
static member function, converting to the
base type(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" |
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; |
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.
Values
specifically to
enable
object-relational mapping via TypeConversion
specializations.Person
object to
and from a
database table containing columns "ID", "FIRST_NAME", "LAST_NAME", and
"GENDER". Person
object
itself doesn't contain any SOCI-specific code:
struct Person |
TypeConversion
specialization in place, it
is possible to use Person
directly with SOCI:
Session sql("oracle", "service=db1", "user=scott", "password=tiger"); |
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.
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 |
The following functions are provided in the BLOB
interface, mimicking the file-like operations:
std::size_t getLen();
std::size_t read(std::size_t offset, char *buf, std::size_t
toRead);
std::size_t write(std::size_t offset, char const *buf,
std::size_t toWrite);
std::size_t append(char const *buf, std::size_t toWrite);
void trim(std::size_t newLen);
offset
parameter is always counted from the beginning
of the BLOB's data.trim
function is not currently available for
the PostgreSQL backend.Previous
(Connections and simple queries) |
Next (Statements, procedures and transactions) |