mysql.md 5.1 KB
Newer Older
1
# MySQL Backend Reference
Benjamin Blundell's avatar
Benjamin Blundell committed
2

3
SOCI backend for accessing MySQL database.
Benjamin Blundell's avatar
Benjamin Blundell committed
4

5
## Prerequisites
Benjamin Blundell's avatar
Benjamin Blundell committed
6

7
### Supported Versions
Benjamin Blundell's avatar
Benjamin Blundell committed
8

9 10
The SOCI MySQL backend should in principle work with every version of MySQL 5.x.
Some of the features (transactions, stored functions) are not available when MySQL server doesn't support them.
Benjamin Blundell's avatar
Benjamin Blundell committed
11

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

14 15 16 17 18
|MySQL|OS|Compiler|
|--- |--- |--- |
|8.0.1|Windows 10|Visual Studio 2017 (15.3.3)|
|5.5.28|OS X 10.8.2|Apple LLVM version 4.2 (clang-425.0.24)|
|5.0.96|Ubuntu 8.04.4 LTS (Hardy Heron)|g++ (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu4)|
Benjamin Blundell's avatar
Benjamin Blundell committed
19

20
### Required Client Libraries
Benjamin Blundell's avatar
Benjamin Blundell committed
21

22
The SOCI MySQL backend requires MySQL's `libmysqlclient` client library from the [MySQL Connector/C](https://dev.mysql.com/downloads/connector/c/).
Benjamin Blundell's avatar
Benjamin Blundell committed
23 24 25 26 27

Note that the SOCI library itself depends also on `libdl`, so the minimum set of libraries needed to compile a basic client program is:

    -lsoci_core -lsoci_mysql -ldl -lmysqlclient

28
## Connecting to the Database
Benjamin Blundell's avatar
Benjamin Blundell committed
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

To establish a connection to a MySQL server, create a `session` object using the `mysql` backend factory together with a connection string:

    session sql(mysql, "db=test user=root password='Ala ma kota'");

    // or:
    session sql("mysql", "db=test user=root password='Ala ma kota'");

    // or:
    session sql("mysql://db=test user=root password='Ala ma kota'");

The set of parameters used in the connection string for MySQL is:

* `dbname` or `db` or `service` (required)
* `user`
* `password` or `pass`
* `host`
* `port`
* `unix_socket`
* `sslca`
* `sslcert`
* `local_infile` - should be `0` or `1`, `1` means `MYSQL_OPT_LOCAL_INFILE` will be set.
* `charset`

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

    int count;
    sql << "select count(*) from invoices", into(count);

58 59
(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
60

61
## SOCI Feature Support
Benjamin Blundell's avatar
Benjamin Blundell committed
62

63
### Dynamic Binding
Benjamin Blundell's avatar
Benjamin Blundell committed
64 65 66

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

67
When calling `row::get<T>()`, the type you should pass as `T` depends upon the underlying database type.
Benjamin Blundell's avatar
Benjamin Blundell committed
68 69
For the MySQL backend, this type mapping is:

70 71 72 73 74 75 76 77 78
|MySQL Data Type|SOCI Data Type|`row::get<T>` specializations|
|--- |--- |--- |
|FLOAT, DOUBLE, DECIMAL and synonyms|dt_double|double|
|TINYINT, TINYINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, INT|dt_integer|int|
|INT UNSIGNED|dt_long_long|long long or unsigned|
|BIGINT|dt_long_long|long long|
|BIGINT UNSIGNED|dt_unsigned_long_long|unsigned long long|
|CHAR, VARCHAR, BINARY, VARBINARY, TINYBLOB, MEDIUMBLOB, BLOB,LONGBLOB, TINYTEXT, MEDIUMTEXT, TEXT, LONGTEXT, ENUM|dt_string|std::string|
|TIMESTAMP (works only with MySQL >= 5.0), DATE, TIME, DATETIME|dt_date|std::tm|
Benjamin Blundell's avatar
Benjamin Blundell committed
79

80 81
(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
82

83
### Binding by Name
Benjamin Blundell's avatar
Benjamin Blundell committed
84

85 86
In addition to [binding by position](../binding.md#binding-by-position), the MySQL backend supports
[binding by name](../binding.md#binding-by-name), via an overload of the `use()` function:
Benjamin Blundell's avatar
Benjamin Blundell committed
87 88 89 90 91 92

    int id = 7;
    sql << "select name from person where id = :id", use(id, "id")

It should be noted that parameter binding of any kind is supported only by means of emulation, since the underlying API used by the backend doesn't provide this feature.

93
### Bulk Operations
Benjamin Blundell's avatar
Benjamin Blundell committed
94

95 96 97
### Transactions

[Transactions](../transactions.md) are also supported by the MySQL backend. Please note, however, that transactions can only be used when the MySQL server supports them (it depends on options used during the compilation of the server; typically, but not always, servers >=4.0 support transactions and earlier versions do not) and only with appropriate table types.
Benjamin Blundell's avatar
Benjamin Blundell committed
98

99
### BLOB Data Type
Benjamin Blundell's avatar
Benjamin Blundell committed
100 101 102 103 104

SOCI `blob` interface is not supported by the MySQL backend.

Note that this does not mean you cannot use MySQL's `BLOB` types.  They can be selected using the usual SQL syntax and read into `std::string` on the C++ side, so no special interface is required.

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

The `rowid` functionality is not supported by the MySQL backend.

109
### Nested Statements
Benjamin Blundell's avatar
Benjamin Blundell committed
110 111 112

Nested statements are not supported by the MySQL backend.

113
### Stored Procedures
Benjamin Blundell's avatar
Benjamin Blundell committed
114

115
MySQL version 5.0 and later supports two kinds of stored routines: stored procedures and stored functions (for details, please consult the [procedure MySQL documentation](http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html)). Stored functions can be executed by using SOCI's [procedure class](../procedures.md). There is currently no support for stored procedures.
Benjamin Blundell's avatar
Benjamin Blundell committed
116

117
## Native API Access
Benjamin Blundell's avatar
Benjamin Blundell committed
118

119
SOCI provides access to underlying datbabase APIs via several `get_backend()` functions, as described in the [Beyond SOCI](../beyond.md) documentation.
Benjamin Blundell's avatar
Benjamin Blundell committed
120 121 122

The MySQL backend provides the following concrete classes for native API access:

123 124 125 126
|Accessor Function|Concrete Class|
|--- |--- |
|session_backend * session::get_backend()|mysql_session_backend|
|statement_backend * statement::get_backend()|mysql_statement_backend|
Benjamin Blundell's avatar
Benjamin Blundell committed
127

128
## Backend-specific extensions
Benjamin Blundell's avatar
Benjamin Blundell committed
129 130 131

None.

132
## Configuration options
Benjamin Blundell's avatar
Benjamin Blundell committed
133

134
None.