1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
|
<!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 - PostgreSQL Backend Reference</title>
</head>
<body>
<p class="banner">SOCI - The C++ Database Access Library</p>
<h2>PostgreSQL Backend Reference</h2>
<div class="navigation">
<a href="#prerequisites">Prerequisites</a><br />
<div class="navigation-indented">
<a href="#versions">Supported Versions</a><br />
<a href="#tested">Tested Platforms</a><br />
<a href="#required">Required Client Libraries</a><br />
</div>
<a href="#connecting">Connecting to the Database</a><br />
<a href="#support">SOCI Feature Support</a><br />
<div class="navigation-indented">
<a href="#dynamic">Dynamic Binding</a><br />
<a href="#bindingbyname">Binding by Name</a><br />
<a href="#bulk">Bulk Operations</a><br />
<a href="#transactions">Transactions</a><br />
<a href="#blob">BLOB Data Type</a><br />
<a href="#rowid">RowID Data Type</a><br />
<a href="#nested">Nested Statements</a><br />
<a href="#procedures">Stored Procedures</a><br />
</div>
<a href="#native">Accessing the Native Database API</a><br />
<a href="#extensions">Backend-specific Extensions</a><br />
<a href="#options">Configuration options</a><br />
</div>
<h3 id="prerequisites">Prerequisites</h3>
<h4 id="versions">Supported Versions</h4>
<p>The SOCI PostgreSQL backend is supported for use with PostgreSQL >= 7.3, although versions older than 8.0 will suffer from limited feature support. See below for details.</p>
<h4 id="tested">Tested Platforms</h4>
<table border="1" cellpadding="5" cellspacing="0">
<tbody>
<tr><th>PostgreSQL version</th><th>Operating System</th><th>Compiler</th></tr>
<tr><td>8.1</td><td>FreeBSD 6.1</td><td>g++ 3.4.4</td></tr>
<tr><td>7.3</td><td>Linux i686 2.4.21 (RedHat type)</td><td>g++ 3.2.3</td></tr>
<tr><td>8.0.4</td><td>Linux i686 2.6.10-gentoo-r6</td><td>g++ 3.4.5</td></tr>
<tr><td>8.1.5</td><td>Windows XP</td><td>Visual C++ 8.0 Professional</td></tr>
<tr><td>8.2rc1</td><td>Windows XP</td><td>Visual C++ 8.0 Professional</td></tr>
</tbody>
</table>
<h4 id="required">Required Client Libraries</h4>
<p>The SOCI PostgreSQL backend requires PostgreSQL's <code>libpq</code> client library.</p>
<h4 id="connecting">Connecting to the Database</h4>
<p>To establish a connection to the PostgreSQL database, create a Session object
using the <code>postgresql</code> backend factory together with a connection string:</p>
<pre class="example">
BackEndFactory const &backEnd = postgresql;
Session sql(postgresql, "dbname=mydatabase");
</pre>
<p>The set of parameters used in the connection string for PostgreSQL is the same as accepted by the <code><a href="http://www.postgresql.org/docs/8.1/interactive/libpq.html#LIBPQ-CONNECT">PQconnectdb</a></code> function from the <code>libpq</code> library.</p>
<p>Once you have created a <code>Session</code> object as shown above, you can use it to access the database, for example:</p>
<pre class="example">
int count;
sql << "select count(*) from invoices", into(count);
</pre>
<p>(See the <a href="../basics.html">SOCI basics</a> and <a href="../exchange.html">exchanging data</a> documentation for general information on using the <code>Session</code> class.)</p>
<h3 id="support">SOCI Feature Support</h3>
<h4 id="dynamic">Dynamic Binding</h4>
<p>The PostgreSQL backend supports the use of the SOCI <code>Row</code> class, which facilitates retrieval of data whose type is not known at compile time.</p>
<p>When calling <code>Row::get<T>()</code>, the type you should pass as T depends upon the underlying database type.<br/> For the PostgreSQL backend, this type mapping is:</p>
<table border="1" cellpadding="5" cellspacing="0">
<tbody>
<tr>
<th>PostgreSQL Data Type</th>
<th>SOCI Data Type</th>
<th><code>Row::get<T></code> specializations</th>
</tr>
<tr>
<td>numeric, real, double</td>
<td><code>eDouble</code></td>
<td><code>double</code></td>
</tr>
<tr>
<td>boolean, smallint, integer, bigint (provided it's in the range)</td>
<td><code>eInteger</code></td>
<td><code>int</code></td>
</tr>
<tr>
<td>oid</td>
<td><code>eUnsignedLong</code></td>
<td><code>unsigned long</code></td>
</tr>
<tr>
<td>char, varchar, text, cstring, bpchar</td>
<td><code>eString</code></td>
<td><code>std::string</code></td>
</tr>
<tr>
<td>abstime, reltime, date, time, timestamp, timestamptz, timetz</td>
<td><code>eDate</code></td>
<td><code>std::tm</code><code></code></td>
</tr>
</tbody>
</table>
<p>(See the <a href="../exchange.html#dynamic">dynamic resultset binding</a> documentation for general information on using the <code>Row</code> class.)</p>
<h4 id="bindingbyname">Binding by Name</h4>
<p>In addition to <a href="../exchange.html#bind_position">binding by position</a>, the PostgreSQL backend supports <a href="../exchange.html#bind_name">binding by name</a>, via an overload of the <code>use()</code> function:</p>
<pre class="example">
int id = 7;
sql << "select name from person where id = :id", use(id, "id")
</pre>
<p>Apart from the portable "colon-name" syntax above, which is achieved by rewriting the query string, the backend also supports the PostgreSQL native numbered syntax:</p>
<pre class="example">
int i = 7;
int j = 8;
sql << "insert into t(x, y) values($1, $2)", use(i), use(j);
</pre>
<p>The use of native syntax is not recommended, but can be nevertheless imposed by switching off the query rewriting. This can be achieved by defining the macro <code>SOCI_PGSQL_NOBINDBYNAME</code> and it is actually necessary for PostgreSQL 7.3, in which case binding of use elements is not supported at all. See the <a href="#options">Configuration options</a> section for details.</p>
<h4 id="bulk">Bulk Operations</h4>
<p>The PostgreSQL backend has full support for SOCI's <a href="../statements.html#bulk">bulk operations</a> interface.</p>
<h4 id="transactions">Transactions</h4>
<p><a href="../statements.html#transactions">Transactions</a> are also fully supported by the PostgreSQL backend.</p>
<h4 id="blob">BLOB Data Type</h4>
<p>The PostgreSQL backend supports working with data stored in columns of type Blob, via SOCI's <a href="../exchange.html#blob"><code>BLOB</code></a> class with the exception that trimming is not supported.</p>
<h4 id="rowid">RowID Data Type</h4>
<p>The concept of row identifier (OID in PostgreSQL) is supported via SOCI's <a href="../reference.html#rowid">RowID</a> class.</p>
<h4 id="nested">Nested Statements</h4>
<p>Nested statements are not supported by PostgreSQL backend.</p>
<h4 id="procedures">Stored Procedures</h4>
<p>PostgreSQL stored procedures can be executed by using SOCI's <a href="../statements.html#procedures">Procedure</a> class.</p>
<h3 id="native">Acessing the native database API</h3>
<p>SOCI provides access to underlying datbabase APIs via several getBackEnd() functions, as described in the <a href="../beyond.html">beyond SOCI</a> documentation.</p>
<p>The PostgreSQL backend provides the following concrete classes for navite API access:</p>
<table border="1" cellpadding="5" cellspacing="0">
<tbody>
<tr>
<th>Accessor Function</th>
<th>Concrete Class</th>
</tr>
<tr>
<td><code>SessionBackEnd* Session::getBackEnd()</code></td>
<td><code>PostgreSQLSessionBackEnd</code></td>
</tr>
<tr>
<td><code>StatementBackEnd* Statement::getBackEnd()</code></td>
<td><code>PostgreSQLStatementBackEnd</code></td>
</tr>
<tr>
<td><code>BLOBBackEnd* BLOB::getBackEnd()</code></td>
<td><code>PostgreSQLBLOBBackEnd</code></td>
</tr>
<tr>
<td><code>RowIDBackEnd* RowID::getBackEnd()</code></td>
<td><code>PostgreSQLRowIDBackEnd</code></td>
</tr>
</tbody>
</table>
<h3 id="extensions">Backend-specific extensions</h3>
<p>None.</p>
<h3 id="options">Configuration options</h3>
<p>To support older PostgreSQL versions, the following configuration macros are recognized:</p>
<ul>
<li><code>SOCI_PGSQL_NOBINDBYNAME</code> - switches off the query rewriting.</li>
<li><code>SOCI_PGSQL_NOPARAMS</code> - disables support for parameterized queries (binding of use elements), automatically imposes also the <code>SOCI_PGSQL_NOBINDBYNAME</code> macro. It is necessary for PostgreSQL 7.3.</li>
<li><code>SOCI_PGSQL_NOPREPARE</code> - disables support for separate query preparation, which in this backend is significant only in terms of optimization. It is necessary for PostgreSQL 7.3 and 7.4.</li>
</ul>
<p class="copyright">Copyright © 2004-2006 Maciej Sobczak, Stephen Hutton</p>
</body>
</html>
|