Skip to content

Latest commit

 

History

History
156 lines (105 loc) · 9.51 KB

mysql.md

File metadata and controls

156 lines (105 loc) · 9.51 KB
Raw

MySQL Backend Reference

SOCI backend for accessing MySQL database.

Prerequisites

Supported Versions

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.

Tested Platforms

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)
5.7.22 macOS High Sierra 10.13.5 AppleClang 9.1.0.9020039

Required Client Libraries

The SOCI MySQL backend requires MySQL's libmysqlclient client library from the MySQL Connector/C.

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

Connecting to the Database

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
  • reconnect - if set to 1, set MYSQL_OPT_RECONNECT to reconnect on connection loss.
  • connect_timeout - should be positive integer value that means seconds corresponding to MYSQL_OPT_CONNECT_TIMEOUT.
  • read_timeout - should be positive integer value that means seconds corresponding to MYSQL_OPT_READ_TIMEOUT.
  • write_timeout - should be positive integer value that means seconds corresponding to MYSQL_OPT_WRITE_TIMEOUT.
  • ssl_mode - should be one of the name constants DISABLED, PREFERRED, REQUIRED, VERIFY_CA or VERIFY_IDENTITY corresponding to MYSQL_OPT_SSL_MODE options (note that this option is currently not supported when using MariaDB).

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);

(See the connection and data binding documentation for general information on using the session class.)

SOCI Feature Support

Dynamic Binding

The MySQL backend supports the use of the SOCI row class, which facilitates retrieval of data which 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 MySQL backend, this type mapping is:

MySQL Data Type SOCI Data Type (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
MySQL Data Type SOCI Data Type (db_type) row::get<T> specializations
FLOAT, DOUBLE, DECIMAL and synonyms db_double double
TINYINT db_int8 int8_t
TINYINT UNSIGNED db_uint8 uint8_t
SMALLINT db_int16 int16_t
SMALLINT UNSIGNED db_uint16 uint16_t
MEDIUMINT db_int32 int32_t
MEDIUMINT UNSIGNED db_uint32 uint32_t
INT db_int32 int32_t
INT UNSIGNED db_uint32 uint32_t
BIGINT db_int64 int64_t
BIGINT UNSIGNED db_uint64 uint64_t
CHAR, VARCHAR, BINARY, VARBINARY, TINYBLOB, MEDIUMBLOB, BLOB,LONGBLOB, TINYTEXT, MEDIUMTEXT, TEXT, LONGTEXT, ENUM db_string std::string
TIMESTAMP (works only with MySQL >= 5.0), DATE, TIME, DATETIME db_date std::tm

(See the dynamic resultset binding documentation for general information on using the Row class.)

Binding by Name

In addition to binding by position, the MySQL backend supports binding by name, via an overload of the use() function:

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.

Bulk Operations

Transactions

Transactions 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.

BLOB Data Type

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.

RowID Data Type

The rowid functionality is not supported by the MySQL backend.

Nested Statements

Nested statements are not supported by the MySQL backend.

Stored Procedures

MySQL version 5.0 and later supports two kinds of stored routines: stored procedures and stored functions (for details, please consult the Defining Stored Programs). Stored functions can be executed by using SOCI's procedure class. There is currently no support for stored procedures.

Native API Access

SOCI provides access to underlying datbabase APIs via several get_backend() functions, as described in the Beyond SOCI documentation.

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

Accessor Function Concrete Class
session_backend * session::get_backend() mysql_session_backend
statement_backend * statement::get_backend() mysql_statement_backend

Backend-specific extensions

None.

Configuration options

None.