File: statements.html

package info (click to toggle)
soci 2.2.0-4
links: PTS, VCS
area: main
in suites: lenny
size: 3,400 kB
ctags: 1,969
sloc: cpp: 17,658; sh: 8,749; makefile: 379
file content (350 lines) | stat: -rw-r--r-- 13,541 bytes
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
<head>
  <meta content="text/html; charset=ISO-8859-1"  http-equiv="content-type" />
  <link rel="stylesheet" type="text/css" href="style.css" />
  <title>SOCI - statements, procedures and transactions</title>
</head>

<body>
<p class="banner">SOCI - The C++ Database Access Library</p>

<h2>Statements, procedures and transactions</h2>

<div class="navigation">
<a href="#preparation">Statement preparation and repeated execution</a><br />
<a href="#rowset">Rowset and iterator-based access</a><br />
<a href="#bulk">Bulk operations</a><br />
<a href="#procedures">Stored procedures</a><br />
<a href="#transactions">Transactions</a><br />
<a href="#logging">Basic logging support</a><br />
</div>

<h3 id="preparation">Statement preparation and repeated execution</h3>

<p>Consider the following examples:</p>

<pre class="example">
// Example 1.
for (int i = 0; i != 100; ++i)
{
    sql &lt;&lt; "insert into numbers(value) values(" &lt;&lt; i &lt;&lt; ")";
}

// Example 2.
for (int i = 0; i != 100; ++i)
{
    sql &lt;&lt; "insert into numbers(value) values(:val)", use(i);
}
</pre>

<p>Both examples will populate the table <code>numbers</code> with the
values from <code>0</code> to <code>99</code>.</p>

<p>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. In fact, more complicated queries are
likely to suffer in terms of lower performance, because finding the optimal
execution plan is quite expensive and here it would be needlessly repeated.</p>

<p>The following example uses the class <code>Statement</code>
explicitly, by preparing the statement only once and repeating its
execution with changing data (note the use of <code>prepare</code>
member of <code>Session</code> class):</p>


<pre class="example">
int i;
Statement st = (sql.prepare &lt;&lt;
                "insert into numbers(value) values(:val)",
                use(i));
for (i = 0; i != 100; ++i)
{
    st.execute(true);
}
</pre>

<p>The <code>true</code> parameter given to the <code>execute</code>
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 <code>i</code>".</p>

<div class="note">
<p><span class="note">Portability note:</span></p>
<p>The above syntax is supported for all backends, even if some database server
does not actually provide this functionality - in which case the library will internally
execute the query in a single phase, without really separating
the statement preparation from execution.</p>
<p>For PostgreSQL servers older than 8.0 it is necessary to define the
<code>SOCI_PGSQL_NOPREPARE</code> macro while compiling the library
to fall back to this one-phase behaviour.</p>
</div>

<h3 id="rowset">Rowset and iterator-based access</h3>

<p>The <code>Rowset</code> class provides an alternative means of executing queries and accessing results using STL-like iterator interface.</p>

<p>The <code>rowset_iterator</code> type is compatible with requirements defined for input iterator category and is available via <code>iterator</code> and <code>const_iterator</code> definitions in the <code>Rowset</code> class.</p>
<p>The <code>Rowset</code> itself can be used only with select queries.</p>

<p>The following example creates an instance of the <code>Rowset</code> class and binds query results into elements of <code>int</code> type - in this query only one result column is expected. After executing the query the code iterates through the query result using <code>rowset_iterator</code>:</p>

<pre class="example">
Rowset&lt;int&gt; rs = (sql.prepare &lt;&lt; "select values from numbers");

for (Rowset&lt;int&gt;::const_iterator it = rs.begin(); it != rs.end(); ++it)
{
     cout &lt;&lt; *it &lt;&lt; '\n';
}
</pre>

<p>Another example presents how to retrieve more complex results, where <code>Rowset</code> elements are of type <code>Row</code> and therefore use <a href="exchange.html#dynamic">dynamic bindings</a>:</p>

<pre class="example">
// person table has 4 columns

Rowset&lt;Row&gt; rs = (sql.prepare &lt;&lt; "select id, firstname, lastname, gender from person");

// iteration through the resultset:
for (Rowset&lt;Row&gt;::const_iterator it = rs.begin(); it != rs.end(); ++it)
{
    Row const&amp; row = (*it);

    // dynamic data extraction from each Row:
    cout &lt;&lt; "Id: " &lt;&lt; row.get&lt;int&gt;(0) &lt;&lt; '\n'
         &lt;&lt; "Name: " &lt;&lt; row.get&lt;string&gt;(1) &lt;&lt; " " &lt;&lt; row.get&lt;string&gt;(2) &lt;&lt; '\n'
         &lt;&lt; "Gender: " &lt;&lt; row.get&lt;string&gt;(3) &lt;&lt; endl;
}
</pre>

<p><code>rowset_iterator</code> can be used with standard algorithms as well:</p>

<pre class="example">
Rowset&lt;string&gt; rs = (sql.prepare &lt;&lt; "select firstname from person");

std::copy(rs.begin(), rs.end(), std::ostream_iterator&lt;std::string&gt;(std::cout, "\n"));
</pre>

<p>Above, the query result contains a single column which is bound to <code>Rowset</code> element of type of <code>std::string</code>. All records are sent to standard output using the <code>std::copy</code> algorithm.</p>

<h3 id="bulk">Bulk operations</h3>

<p>When using some databases, 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 <code>std::vector</code>
based types:</p>

<pre class="example">
// Example 3.
const int BATCH_SIZE = 25;
std::vector&lt;int&gt; valsIn;
for (int i = 0; i != BATCH_SIZE; ++i)
{
    ids.push_back(i);
}

Statement st = (sql.prepare &lt;&lt;
                "insert into numbers(value) values(:val)",
                use(valsIn));
for (int i = 0; i != 4; ++i)
{
    st.execute(true);
}
</pre>

<p>(Of course, the size of the vector that will achieve optimum
performance will vary, depending on many environmental factors, such as
network speed.)</p>

<p>It is also possible to read all the numbers written in the above
examples:</p>

<pre class="example">
int i;
Statement st = (sql.prepare &lt;&lt;
                "select value from numbers order by value",
                into(i));
st.execute();
while (st.fetch())
{
    cout &lt;&lt; i &lt;&lt; '\n';
}
</pre>

<p>In the above example, the <code>execute</code> method is called
with the default parameter <code>false</code>. This means that the
statement should be executed, but the actual data exchange will be
executed later.</p>

<p>Further <code>fetch</code>
calls perform the actual data retrieval and cursor traversal. The
end-of-cursor condition is indicated by the <code>fetch</code>
function returning <code>false</code>.</p>

<p>The above code example should be treated as an idiomatic way
of reading many rows of data, <i>one at a time</i>.</p>

<p>It is further possible to select records in batches into <code>std::vector</code>
based types, with the size of the vector specifying the number of
records to retrieve in each round trip:</p>

<pre class="example">
std::vector&lt;int&gt; valsOut(100);
sql &lt;&lt; "select val from numbers", into(valsOut);
</pre>

<p>Above, the value <code>100</code> 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.</p>

<p>The <code>Statement::execute()</code> and <code>Statement::fetch()</code>
functions can also be used to repeatedly select all rows returned by a
query into a vector based type:</p>

<pre class="example">
const int BATCH_SIZE = 30;
std::vector&lt;int&gt; valsOut(BATCH_SIZE);
Statement st = (sql.prepare &lt;&lt;
                "select value from numbers",
                into(valsOut));
st.execute();
while (st.fetch())
{
    std::vector&lt;int&gt;::iterator pos;
    for(; pos != valsOut.end(); ++pos)
    {
        cout &lt;&lt; *pos &lt;&lt; '\n';
    }

    valsOut.resize(BATCH_SIZE);
}
</pre>

<p>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 <code>fetch()</code>
to retrieve all 100 values. Each call to <code>fetch()</code>
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 <code>resize(BATCH_SIZE)</code> operation is
needed - it is there to ensure that each time the <code>fetch()</code>
is called, the vector is ready to accept the next bunch of values.
Without this operation, the vector <i>might</i>
be getting smaller with subsequent iterations of the loop, forcing more
iterations to be performed (because <i>all</i>
rows will be read anyway), than really needed.</p>

<p>Note the following details about the above examples:</p>
<ul>
  <li>After performing <code>fetch()</code>, the vector's size might
be <i>less</i> than requested, but <code>fetch()</code>
returning true means that there was <i>at least one</i> row retrieved.</li>
  <li>It is forbidden to manually resize the vector to the size <i>higher</i> than it was initially (this
can cause the vector to reallocate its internal buffer and the library
can lose track of it).</li>
</ul>

<p>Taking these points under consideration, the above code example should
be treated as an idiomatic way of reading many rows by bunches of
requested size.</p>

<div class="note">
<p><span class="note">Portability note:</span></p>
<p>Actually, all supported backends guarantee that the requested
number of rows will be read with each fetch and that the vector will
never be down-sized (unless the end of rowset condition is met, of
course). This means that the manual vector
resizing is not needed - the vector will keep its size until the end of
rowset. The above idiom, however, is provided with future backends in
mind, where the constant size of the vector might be too expensive to
guarantee and where allowing <code>fetch</code> to down-size the
vector even before reaching the end of rowset might buy some
performance gains.</p>
</div>

<h3 id="procedures">Stored procedures</h3>

<p>The <code>Procedure</code> class provides a convenient mechanism for
calling stored procedures:</p>

<pre class="example">
sql &lt;&lt; "create or replace procedure echo(output out varchar2,"
       "input in varchar2) as "
       "begin output := input; end;";

std::string in("my message");
std::string out;
Procedure proc = (sql.prepare &lt;&lt; "echo(:output, :input)",
                                 use(out, "output"),
                                 use(in, "input"));
proc.execute(true);
assert(out == "my message");
</pre>

<div class="note">
<p><span class="note">Portability note:</span></p>
<p>The above way of calling stored procedures is provided for portability
of the code that might need it. It is of course still possible to call
procedures or functions using the syntax supported by the given
database server.</p>
</div>

<h3 id="transactions">Transactions</h3>

<p>The SOCI library provides the following members of the <code>Session</code>
class for transaction management:</p>
<ul>
  <li><code>void begin();</code></li>
  <li><code>void commit();</code></li>
  <li><code>void rollback();</code></li>
</ul>

<div class="note">
<p><span class="note">Portability note:</span></p>
<p>Different database servers have different policies with regard to the
implicit transaction management. Some of them start the implicit
transaction with the first DML statement and keep it open until
explicitly commited or rolled back (or closing the whole session).
Others will treat each statement as if it was a separate, auto-commited
transaction. For better compatibility, it is recommended to use the
above functions for explicit transaction management.</p>
</div>

<h3 id="logging">Basic logging support</h3>

<p>The following members of the <code>Session</code> class support the basic logging functionality:</p>
<ul>
  <li><code>void setLogStream(std::ostream *s);</code></li>
  <li><code>std::ostream * getLogStream() const;</code></li>
  <li><code>std::string getLastQuery() const;</code></li>
</ul>

<p>The first two functions allow to set the user-provided output stream object for logging. The <code>NULL</code> value, which is the default, means that there is no logging. An example use might be:</p>

<pre class="example">
Session sql(oracle, "...");

ofstream file("my_log.txt");
sql.setLogStream(&amp;file);

// ...
</pre>

<p>Each statement logs its query string before the preparation step (whether explicit or implicit) and the logging is effective whether the query succeeds or not. Note that each prepared query is logged only once, independent on how many times it is executed.</p>
<p>The <code>getLastQuery</code> function allows to retrieve the last used query.</p>

<table class="foot-links" border="0" cellpadding="2" cellspacing="2">
  <tr>
    <td class="foot-link-left">
      <a href="exchange.html">Previous (Exchanging data)</a>
    </td>
    <td class="foot-link-right">
      <a href="beyond.html">Next (Beyond SOCI)</a>
    </td>
  </tr>
</table>

<p class="copyright">Copyright &copy; 2004-2006 Maciej Sobczak, Stephen Hutton</p>
</body>
</html>