/
odbc.html
282 lines (228 loc) · 9.17 KB
/
odbc.html
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
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
<!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 - ODBC Backend Reference</title>
</head>
<body>
<p class="banner">SOCI - The C++ Database Access Library</p>
<h2>ODBC 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 />
<div class="navigation-indented">
<a href="#odbc_soci_error">odbc_soci_error</a><br />
</div>
<a href="#options">Configuration options</a><br />
</div>
<h3 id="prerequisites">Prerequisites</h3>
<h4 id="versions">Supported Versions</h4>
<p>The SOCI ODBC backend is supported for use with ODBC 3.</p>
<h4 id="tested">Tested Platforms</h4>
<table border="1" cellpadding="5" cellspacing="0">
<tbody>
<tr><th>ODBC version</th><th>Operating System</th><th>Compiler</th></tr>
<tr><td>3</td><td>Linux (Ubuntu 12.04)</td><td>g++ 4.6.3</td></tr>
<tr><td>3</td><td>Linux (Ubuntu 12.04)</td><td>clang 3.2</td></tr>
<tr><td>3.8</td><td>Windows 8</td><td>Visual Studio 2012</td></tr>
<tr><td>3</td><td>Windows 7</td><td>Visual Studio 2010</td></tr>
<tr><td>3</td><td>Windows XP</td><td>Visual Studio 2005 (express)</td></tr>
<tr><td>3</td><td>Windows XP</td><td>Visual C++ 8.0 Professional</td></tr>
<tr><td>3</td><td>Windows XP</td><td>g++ 3.3.4 (Cygwin)</td></tr>
</tbody>
</table>
<h4 id="required">Required Client Libraries</h4>
<p>The SOCI ODBC backend requires the ODBC client library.</p>
<h4 id="connecting">Connecting to the Database</h4>
<p>To establish a connection to the ODBC database, create a Session object
using the <code>ODBC</code> backend factory together with a connection string:</p>
<pre class="example">
backend_factory const& backEnd = odbc;
session sql(backEnd, "filedsn=c:\\my.dsn");
</pre>
<p>or simply:</p>
<pre class="example">
session sql(odbc, "filedsn=c:\\my.dsn");
</pre>
<p>The set of parameters used in the connection string for ODBC is the same as accepted by the <code><a href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbcsql/od_odbc_d_4x4k.asp">SQLDriverConnect</a></code> function from the ODBC 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 ODBC 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 ODBC backend, this type mapping is:</p>
<table border="1" cellpadding="5" cellspacing="0">
<tbody>
<tr>
<th>ODBC Data Type</th>
<th>SOCI Data Type</th>
<th><code>row::get<T></code> specializations</th>
</tr>
<tr>
<td>SQL_DOUBLE
, SQL_DECIMAL
, SQL_REAL
, SQL_FLOAT
, SQL_NUMERIC
</td>
<td><code>dt_double</code></td>
<td><code>double</code></td>
</tr>
<tr>
<td>SQL_TINYINT
, SQL_SMALLINT
, SQL_INTEGER
, SQL_BIGINT</td>
<td><code>dt_integer</code></td>
<td><code>int</code></td>
</tr>
<tr>
<td>SQL_CHAR, SQL_VARCHAR</td>
<td><code>dt_string</code></td>
<td><code>std::string</code></td>
</tr>
<tr>
<td>SQL_TYPE_DATE
, SQL_TYPE_TIME
, SQL_TYPE_TIMESTAMP</td>
<td><code>dt_date</code></td>
<td><code>std::tm</code><code></code></td>
</tr>
</tbody>
</table>
<p>Not all ODBC drivers support all datatypes</p>
<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 ODBC 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 ODBC ? syntax:</p>
<pre class="example">
int i = 7;
int j = 8;
sql << "insert into t(x, y) values(?, ?)", use(i), use(j);
</pre>
<h4 id="bulk">Bulk Operations</h4>
<p>The ODBC backend has support for SOCI's <a href="../statements.html#bulk">bulk operations</a> interface. Not all ODBC drivers support bulk operations, the following is a list of some tested backends:</p>
<table border="1" cellpadding="5" cellspacing="0">
<tbody>
<tr>
<th>ODBC Driver</th>
<th>Bulk Read</th>
<th>Bulk Insert</th>
</tr>
<tr>
<td>MS SQL Server 2005</td>
<td>YES</td>
<td>YES</td>
</tr>
<tr>
<td>MS Access 2003</td>
<td>YES</td>
<td>NO</td>
</tr>
<tr>
<td>PostgresQL 8.1</td>
<td>YES</td>
<td>YES</td>
</tr>
<tr>
<td>MySQL 4.1</td>
<td>NO</td>
<td>NO</td>
</tr>
</tbody>
</table>
<h4 id="transactions">Transactions</h4>
<p><a href="../statements.html#transactions">Transactions</a> are also fully supported by the ODBC backend, provided that they are supported by the underlying database.</p>
<h4 id="blob">BLOB Data Type</h4>
<p>Not currently supported</p>
<h4 id="rowid">RowID Data Type</h4>
<p>Not currently supported</p>
<h4 id="nested">Nested Statements</h4>
<p>Not currently supported</p>
<h4 id="procedures">Stored Procedures</h4>
<p>Not currently supported</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 ODBC 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>session_backend* session::get_backend()</code></td>
<td><code>odbc_statement_backend</code></td>
</tr>
<tr>
<td><code>statement_backend* statement::get_backend()</code></td>
<td><code>odbc_statement_backend</code></td>
</tr>
<tr>
<td><code>rowid_backend* rowid::get_backend()</code></td>
<td><code>odbc_rowid_backend</code></td>
</tr>
</tbody>
</table>
<h3 id="extensions">Backend-specific extensions</h3>
<h4 id="odbc_soci_error">odbc_soci_error</h4>
<p>The ODBC backend can throw instances of class <code>odbc_soci_error</code>,
which is publicly derived from <code>soci_error</code> and has
additional public members containing the ODBC error code, the Native database
error code, and the message returned from ODBC:</p>
<pre class="example">
int main()
{
try
{
// regular code
}
catch (soci::odbc_soci_error const& e)
{
cerr << "ODBC Error Code: " << e.odbc_error_code() << endl
<< "Native Error Code: " << e.native_error_code() << endl
<< "SOCI Message: " << e.what() << std::endl
<< "ODBC Message: " << e.odbc_error_message() << endl;
}
catch (exception const &e)
{
cerr << "Some other error: " << e.what() << endl;
}
}
</pre>
<h4 id="get_connection_string">get_connection_string()</h4>
<p>The <code>odbc_session_backend</code> class provides <code>std::string get_connection_string() const</code> method
that returns fully expanded connection string as returned by the <code>SQLDriverConnect</code> function.</p>
<h3 id="options">Configuration options</h3>
<p>None</p>
<p class="copyright">Copyright © 2013 Mateusz Loskot</p>
<p class="copyright">Copyright © 2004-2006 Maciej Sobczak, Stephen Hutton, David Courtney</p>
</body>
</html>