22.4.9. MySQL Connector/C++ Usage Notes

The MySQL Connector/C++ sql::DataType class defines the following JDBC standard data types: UNKNOWN, BIT, TINYINT, SMALLINT, MEDIUMINT, INTEGER, BIGINT, REAL, DOUBLE, DECIMAL, NUMERIC, CHAR, BINARY, VARCHAR, VARBINARY, LONGVARCHAR, LONGVARBINARY, TIMESTAMP, DATE, TIME, GEOMETRY, ENUM, SET, SQLNULL.

MySQL Connector/C++ does not support the following JDBC standard data types: ARRAY, BLOB, CLOB, DISTINCT, FLOAT, OTHER, REF, STRUCT.

DatabaseMetaData::supportsBatchUpdates() returns true because MySQL supports batch updates in general. However, the MySQL Connector/C++ API provides no API calls for batch updates.

Two non-JDBC methods let you fetch and set unsigned integers: getUInt64() and getUInt(). These are available for ResultSet and Prepared_Statement:

The corresponding getLong() and setLong() methods have been removed.

The DatabaseMetaData::getColumns() method has 23 columns in its result set, rather than the 22 columns defined by JDBC. The first 22 columns are as described in the JDBC documentation, but column 23 is new:

23. IS_AUTOINCREMENT: A string which is "YES" if the column is an auto-increment column, "NO" otherwise.

MySQL Connector/C++ may return different metadata for the same column, depending on the method you call.

Suppose that you have a column that accepts a character set and a collation in its specification and you specify a binary collation, such as:

VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_bin

The server sets the BINARY flag in the result set metadata of this column. The ResultSetMetadata::getColumnTypeName() method uses the metadata and will report, due to the BINARY flag, that the column type name is BINARY. This is illustrated here:

mysql> CREATE TABLE varbin (a VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);Query OK, 0 rows affected (0.00 sec)mysql> select * from varbin;Field   1:  `a`Catalog:    `def`Database:   `test`Table:      `varbin`Org_table:  `varbin`Type:       VAR_STRINGCollation:  latin1_swedish_ci (8)Length:     20Max_length: 0Decimals:   0Flags:      BINARY0 rows in set (0.00 sec)mysql> SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='varbin'\G*************************** 1. row ***************************           TABLE_CATALOG: NULL            TABLE_SCHEMA: test              TABLE_NAME: varbin             COLUMN_NAME: a        ORDINAL_POSITION: 1          COLUMN_DEFAULT: NULL             IS_NULLABLE: YES               DATA_TYPE: varcharCHARACTER_MAXIMUM_LENGTH: 20  CHARACTER_OCTET_LENGTH: 60       NUMERIC_PRECISION: NULL           NUMERIC_SCALE: NULL      CHARACTER_SET_NAME: utf8          COLLATION_NAME: utf8_bin             COLUMN_TYPE: varchar(20)              COLUMN_KEY:                   EXTRA:              PRIVILEGES: select,insert,update,references          COLUMN_COMMENT:1 row in set (0.01 sec)

However, INFORMATION_SCHEMA gives no hint in its COLUMNS table that metadata will contain the BINARY flag. DatabaseMetaData::getColumns() uses INFORMATION_SCHEMA and will report the type name VARCHAR for the same column. It also returns a different type code.

When inserting or updating BLOB or TEXT columns, MySQL Connector/C++ developers are advised not to use setString(). Instead, use the dedicated setBlob() API function.

The use of setString() can cause a Packet too large error message. The error occurs if the length of the string passed to the connector using setString() exceeds max_allowed_packet (minus a few bytes reserved in the protocol for control purposes). This situation is not handled in MySQL Connector/C++, because it could lead to security issues, such as extremely large memory allocation requests due to malevolently long strings.

If setBlob() is used, this problem does not arise because setBlob() takes a streaming approach based on std::istream. When sending the data from the stream to MySQL Server, MySQL Connector/C++ splits the stream into chunks appropriate for MySQL Server using the current max_allowed_packet setting.

This difference from the JDBC specification ensures that MySQL Connector/C++ is not vulnerable to memory flooding attacks.

In general, MySQL Connector/C++ works with MySQL 5.0, but it is not completely supported. Some methods may not be available when connecting to MySQL 5.0. This is because the Information Schema is used to obtain the requested information. There are no plans to improve the support for 5.0 because the current GA version of MySQL Server is 5.6. MySQL Connector/C++ is primarily targeted at the MySQL Server GA version that is available on its release.

The following methods throw a sql::MethodNotImplemented exception when you connect to a MySQL server earlier than 5.1:

MySQL Connector/C++ includes a Connection::getClientOption() method that is not included in the JDBC API specification. The prototype is:

void getClientOption(const std::string & optionName, void * optionValue)

The method can be used to check the value of connection properties set when establishing a database connection. The values are returned through the optionValue argument passed to the method with the type void *.

Currently, getClientOption() supports fetching the optionValue of the following options:

The connection property can be set either when establishing the connection through the connection property map, or using void Connection::setClientOption(const std::string & optionName, const void * optionValue) where optionName is assigned the value metadataUseInfoSchema.

Some examples:

bool isInfoSchemaUsed;conn->getClientOption("metadataUseInfoSchema", (void *) &isInfoSchemaUsed);int defaultStmtResType;int defaultPStmtResType;conn->getClientOption("defaultStatementResultType", (void *) &defaultStmtResType);conn->getClientOption("defaultPreparedStatementResultType", (void *) &defaultPStmtResType);

MySQL Connector/C++ supports the following methods not found in the JDBC API standard:

std::string MySQL_Connection::getSessionVariable(const std::string & varname)

void MySQL_Connection::setSessionVariable(const std::string & varname, const std::string & value)

These methods get and set MySQL session variables. Both are members of the MySQL_Connection class.

getSessionVariable() is equivalent to executing the following and fetching the first return value:

SHOW SESSION VARIABLES LIKE "<varname>"

You can use the "%" and "_" SQL pattern characters in <varname>.

setSessionVariable() is equivalent to executing:

SET SESSION <varname> = <value>

Fetching the value of a column can sometimes return different values depending on whether the call is made from a Statement or Prepared Statement. This is because the protocol used to communicate with the server differs depending on whether a Statement or Prepared Statement is used.

To illustrate this, consider the case where a column has been defined as type BIGINT. The most negative BIGINT value is then inserted into the column. If a Statement and Prepared Statement are created that perform a GetUInt64() call, then the results will be different in each case. The Statement returns the maximum positive value for BIGINT. The Prepared Statement returns 0.

The difference results from the fact that Statements use a text protocol, and Prepared Statements use a binary protocol. With the binary protocol in this case, a binary value is returned from the server that can be interpreted as an int64. In the preceding scenario, a very large negative value is fetched with GetUInt64(), which fetches unsigned integers. Because the large negative value cannot be sensibly converted to an unsigned value, 0 is returned.

In the case of the Statement, which uses the text protocol, values are returned from the server as strings, and then converted as required. When a string value is returned from the server in the preceding scenario, the large negative value must be converted by the runtime library function strtoul(), which GetUInt64() calls. The behavior of strtoul() is dependent upon the specific runtime and host operating system, so the results can be platform dependent. In the case, given a large positive value was actually returned.

Although it is very rare, there are some cases where Statements and Prepared Statements can return different values unexpectedly, but this usually only happens in extreme cases such as the one mentioned.

The JDBC documentation lists many fields for the DatabaseMetaData class. JDBC also appears to define certain values for those fields. However, MySQL Connector/C++ does not define certain values for those fields. Internally enumerations are used and the compiler determines the values to assign to a field.

To compare a value with the field, use code such as the following, rather than making assumptions about specific values for the attribute:

// dbmeta is an instance of DatabaseMetaDataif (myvalue == dbmeta->attributeNoNulls) {    ...}

Usually myvalue will be a column from a result set holding metadata information. MySQL Connector/C++ does not guarantee that attributeNoNulls is 0. It can be any value.

When programming stored procedures, JDBC has available an extra class, an extra abstraction layer for callable statements, the CallableStatement class. As this class is not present in MySQL Connector/C++, use the methods from the Statement and PreparedStatement classes to execute a stored procedure using CALL.