Chapter 9 Connector/C++ Usage Notes

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

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

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 reports, due to the BINARY flag, that the column type name is BINARY, as 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_STRING
Collation:  latin1_swedish_ci (8)
Length:     20
Max_length: 0
Decimals:   0
Flags:      BINARY

0 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: varchar
CHARACTER_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, 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 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 the MySQL server, Connector/C++ splits the stream into chunks appropriate for the server using the current max_allowed_packet setting.

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

In general, 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 is 5.6. Connector/C++ is primarily targeted at the MySQL 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:

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 metadataUseInfoSchema connection option controls whether to use the Information_Schemata for returning the metadata of SHOW statements:

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

To get and set MySQL session variables, Connector/C++ supports the following MySQL_Connection methods, which are 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)

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

SHOW SESSION VARIABLES LIKE 'var_name'

You can use the % and _ SQL pattern characters in var_name.

setSessionVariable() is equivalent to executing:

SET SESSION var_name = 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, 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 DatabaseMetaData
if (myvalue == dbmeta->attributeNoNulls) {
    ...
}

Usually myvalue will be a column from a result set holding metadata information. 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 Connector/C++, use the methods from the Statement and PreparedStatement classes to execute a stored procedure using CALL.