odbc.md 5.79 KB
Newer Older
1 2 3 4 5 6 7
# ODBC Backend Reference

SOCI backend for accessing variety of databases via ODBC API.

## Prerequisites

### Supported Versions
Benjamin Blundell's avatar
Benjamin Blundell committed
8 9 10

The SOCI ODBC backend is supported for use with ODBC 3.

11
### Tested Platforms
Benjamin Blundell's avatar
Benjamin Blundell committed
12

13 14 15 16 17 18 19 20 21
|ODBC|OS|Compiler|
|--- |--- |--- |
|3|Linux (Ubuntu 12.04)|g++ 4.6.3|
|3|Linux (Ubuntu 12.04)|clang 3.2|
|3.8|Windows 8|Visual Studio 2012|
|3|Windows 7|Visual Studio 2010|
|3|Windows XP|Visual Studio 2005 (express)|
|3|Windows XP|Visual C++ 8.0 Professional|
|3|Windows XP|g++ 3.3.4 (Cygwin)|
Benjamin Blundell's avatar
Benjamin Blundell committed
22

23
### Required Client Libraries
Benjamin Blundell's avatar
Benjamin Blundell committed
24 25 26

The SOCI ODBC backend requires the ODBC client library.

27
## Connecting to the Database
Benjamin Blundell's avatar
Benjamin Blundell committed
28 29 30

To establish a connection to the ODBC database, create a Session object using the `ODBC` backend factory together with a connection string:

31 32 33 34
```cpp
backend_factory const& backEnd = odbc;
session sql(backEnd, "filedsn=c:\\my.dsn");
```
Benjamin Blundell's avatar
Benjamin Blundell committed
35 36 37

or simply:

38 39 40
```cpp
session sql(odbc, "filedsn=c:\\my.dsn");
```
Benjamin Blundell's avatar
Benjamin Blundell committed
41

42
The set of parameters used in the connection string for ODBC is the same as accepted by the [SQLDriverConnect](http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbcsql/od_odbc_d_4x4k.asp) function from the ODBC library.
Benjamin Blundell's avatar
Benjamin Blundell committed
43 44 45

Once you have created a `session` object as shown above, you can use it to access the database, for example:

46 47 48 49
```cpp
int count;
sql << "select count(*) from invoices", into(count);
```
Benjamin Blundell's avatar
Benjamin Blundell committed
50

Astrinus's avatar
Astrinus committed
51
(See the [connection](../connections.md) and [data binding](../binding.md) documentation for general information on using the `session` class.)
Benjamin Blundell's avatar
Benjamin Blundell committed
52

53
## SOCI Feature Support
Benjamin Blundell's avatar
Benjamin Blundell committed
54

55
### Dynamic Binding
Benjamin Blundell's avatar
Benjamin Blundell committed
56 57 58 59 60 61

The ODBC backend supports the use of the SOCI `row` class, which facilitates retrieval of data whose type is not known at compile time.

When calling `row::get<T>()`, the type you should pass as T depends upon the underlying database type.
For the ODBC backend, this type mapping is:

62 63 64 65 66 67
|ODBC Data Type|SOCI Data Type|`row::get<T>` specializations|
|--- |--- |--- |
|SQL_DOUBLE, SQL_DECIMAL, SQL_REAL, SQL_FLOAT, SQL_NUMERIC|dt_double|double|
|SQL_TINYINT, SQL_SMALLINT, SQL_INTEGER, SQL_BIGINT|dt_integer|int|
|SQL_CHAR, SQL_VARCHAR|dt_string|std::string|
|SQL_TYPE_DATE, SQL_TYPE_TIME, SQL_TYPE_TIMESTAMP|dt_date|std::tm|
Benjamin Blundell's avatar
Benjamin Blundell committed
68 69 70

Not all ODBC drivers support all datatypes.

Astrinus's avatar
Astrinus committed
71
(See the [dynamic resultset binding](../types.md#dynamic-binding) documentation for general information on using the `row` class.)
Benjamin Blundell's avatar
Benjamin Blundell committed
72

73
### Binding by Name
Benjamin Blundell's avatar
Benjamin Blundell committed
74

Astrinus's avatar
Astrinus committed
75
In addition to [binding by position](../binding.md#binding-by-position), the ODBC backend supports [binding by name](../binding.md#binding-by-name), via an overload of the `use()` function:
Benjamin Blundell's avatar
Benjamin Blundell committed
76

77 78 79 80
```cpp
int id = 7;
sql << "select name from person where id = :id", use(id, "id")
```
Benjamin Blundell's avatar
Benjamin Blundell committed
81 82 83

Apart from the portable "colon-name" syntax above, which is achieved by rewriting the query string, the backend also supports the ODBC ? syntax:

84 85 86 87 88
```cpp
int i = 7;
int j = 8;
sql << "insert into t(x, y) values(?, ?)", use(i), use(j);
```
Benjamin Blundell's avatar
Benjamin Blundell committed
89

90
### Bulk Operations
Benjamin Blundell's avatar
Benjamin Blundell committed
91

Astrinus's avatar
Astrinus committed
92
The ODBC backend has support for SOCI's [bulk operations](../binding.md#bulk-operations) interface.  Not all ODBC drivers support bulk operations, the following is a list of some tested backends:
Benjamin Blundell's avatar
Benjamin Blundell committed
93

94 95 96 97 98 99
|ODBC Driver|Bulk Read|Bulk Insert|
|--- |--- |--- |
|MS SQL Server 2005|YES|YES|
|MS Access 2003|YES|NO|
|PostgresQL 8.1|YES|YES|
|MySQL 4.1|NO|NO|
Benjamin Blundell's avatar
Benjamin Blundell committed
100

101
### Transactions
Benjamin Blundell's avatar
Benjamin Blundell committed
102

Astrinus's avatar
Astrinus committed
103
[Transactions](../transactions.md) are also fully supported by the ODBC backend, provided that they are supported by the underlying database.
Benjamin Blundell's avatar
Benjamin Blundell committed
104

105
### BLOB Data Type
Benjamin Blundell's avatar
Benjamin Blundell committed
106 107 108

Not currently supported.

109
### RowID Data Type
Benjamin Blundell's avatar
Benjamin Blundell committed
110 111 112

Not currently supported.

113
### Nested Statements
Benjamin Blundell's avatar
Benjamin Blundell committed
114 115 116

Not currently supported.

117
### Stored Procedures
Benjamin Blundell's avatar
Benjamin Blundell committed
118 119 120

Not currently supported.

121
## Native API Access
Benjamin Blundell's avatar
Benjamin Blundell committed
122

Astrinus's avatar
Astrinus committed
123
SOCI provides access to underlying datbabase APIs via several getBackEnd() functions, as described in the [beyond SOCI](../beyond.md) documentation.
Benjamin Blundell's avatar
Benjamin Blundell committed
124 125 126

The ODBC backend provides the following concrete classes for navite API access:

127 128 129 130 131
|Accessor Function|Concrete Class|
|--- |--- |
|session_backend* session::get_backend()|odbc_statement_backend|
|statement_backend* statement::get_backend()|odbc_statement_backend|
|rowid_backend* rowid::get_backend()|odbc_rowid_backend|
Benjamin Blundell's avatar
Benjamin Blundell committed
132

133
## Backend-specific extensions
Benjamin Blundell's avatar
Benjamin Blundell committed
134

135
### odbc_soci_error
Benjamin Blundell's avatar
Benjamin Blundell committed
136 137 138

The ODBC backend can throw instances of class `odbc_soci_error`, which is publicly derived from `soci_error` and has additional public members containing the ODBC error code, the Native database error code, and the message returned from ODBC:

139 140 141 142 143 144 145 146 147 148 149 150 151 152 153
```cpp
int main()
{
    try
    {
        // regular code
    }
    catch (soci::odbc_soci_error const&amp; 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 &amp;e)
Benjamin Blundell's avatar
Benjamin Blundell committed
154
    {
155
        cerr << "Some other error: " << e.what() << endl;
Benjamin Blundell's avatar
Benjamin Blundell committed
156
    }
157 158
}
```
Benjamin Blundell's avatar
Benjamin Blundell committed
159

160
### get_connection_string()
Benjamin Blundell's avatar
Benjamin Blundell committed
161 162 163 164

The `odbc_session_backend` class provides `std::string get_connection_string() const` method
that returns fully expanded connection string as returned by the `SQLDriverConnect` function.

165
## Configuration options
Benjamin Blundell's avatar
Benjamin Blundell committed
166 167 168

This backend supports `odbc_option_driver_complete` option which can be passed to it via `connection_parameters` class. The value of this option is passed to `SQLDriverConnect()` function as "driver completion" parameter and so must be one of `SQL_DRIVER_XXX` values, in the string form. The default value of this option is `SQL_DRIVER_PROMPT` meaning that the driver will query the user for the user name and/or the password if they are not stored together with the connection. If this is undesirable for some reason, you can use `SQL_DRIVER_NOPROMPT` value for this option to suppress showing the message box:

169 170 171 172 173
```cpp
connection_parameters parameters("odbc", "DSN=mydb");
parameters.set_option(odbc_option_driver_complete, "0" /* SQL_DRIVER_NOPROMPT */);
session sql(parameters);
```