// Example 1. |
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, even if some of the database servers
are likely to optimize the second case.
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; |
The true
parameter given to the execute
method indicates that the actual data exchange is wanted, so that the
meaning of the whole example is "prepare the statement and exchange the
data for each value of variable i
".
Further performance improvements may be possible by having the
underlying database API group operations together to reduce network
roundtrips. SOCI makes such bulk operations possible by supporting std::vector
based types:
// Example 3. |
(Of course, the size of the vector that will achieve optimum performance will vary, depending on many environmental factors, such as network speed.)
It is also possible to read all the numbers written in the above
examples:
int i; |
In the above example, the execute
method is called
with the default parameter false
. This means that the
statement should be executed, but the actual data exchange will be
executed later.
Further fetch
calls perform the actual data retrieval and cursor traversal. The
end-of-cursor condition is indicated by the fetch
function returning false
.
Note: The above code example should be treated as an idiomatic way
of reading many rows of data, one at
a time.
It is further possible to select records in batches into std::vector
based types, with the size of the vector specifying the number of
records to retrieve in each round trip:
std::vector<int> valsOut(100); |
Above, the value 100
indicates that no more values
should be retrieved, even if it would be otherwise possible. If there
is less rows than asked for, the vector will be appropriately
down-sized.
The Statement::execute()
and Statement::fetch()
functions can also be used to repeatedly select all rows returned by a
query into a vector based type:
const int BATCH_SIZE = 30; |
Assuming there are 100 rows returned by the query, the above code
will retrieve and print all of them. Since the output vector was
created with size 30, it will take (at least) 4 calls to fetch()
to retrieve all 100 values. Each call to fetch()
can potentially resize the vector to a size less than its initial size
- how often this happens depends on the underlying database
implementation.
This explains why the resize(BATCH_SIZE)
operation is
needed - it is there to ensure that each time the fetch()
is called, the vector is ready to accept the next bunch of values.
Without this operation, the vector might
be getting smaller with subsequent iterations of the loop, forcing more
iterations to be performed (because all
rows will be read anyway), than really needed.
Notes:
fetch()
, the vector's size might
be less than requested, but fetch()
returning true means that there was at
least one row retrieved.fetch
to down-size the
vector even before reaching the end of rowset might buy some
performance gains.Procedure
class provides a convenient mechanism for
calling stored procedures:
sql << "create or replace procedure echo(output out varchar2," |
Session
class for transaction management:void begin();
void commit();
void rollback();
Previous
(Exchanging data) |
Next (Beyond SOCI) |