22.5.8. MySQL Connector/C++ Usage Notes

See the JDBC overview for information on JDBC 4.0. Please also check the examples/ directory of the download package.

Notes on using the MySQL Connector/C++ API

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

  • Two non-JDBC methods have been introduced for fetching and setting unsigned integers: getUInt64() and getUInt(). These are available for ResultSet and Prepared_Statement:

    • ResultSet::getUInt64()

    • ResultSet::getUInt()

    • Prepared_Statement::setUInt64()

    • Prepared_Statement::setUInt()

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

  • The method DatabaseMetaData::getColumns() 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: String which is “YES” if the column is an auto-increment column. Otherwise the string contains “NO”.

  • MySQL Connector/C++ may return different metadata for the same column.

    When you have any column that accepts a charset and a collation in its specification and you specify a binary collation, such as:

     CHAR(250) CHARACTER SET 'latin1' COLLATE 'latin1_bin'
    

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

    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. It will report the type name CHAR for the same column. Note, a different type code is also returned.

  • The MySQL Connector/C++ class sql::DataType 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.

    However, the following JDBC standard data types are not supported by MySQL Connector/C++: ARRAY, BLOB, CLOB, DISTINCT, FLOAT, OTHER, REF, STRUCT.

  • When inserting or updating BLOB or TEXT columns, MySQL Connector/C++ developers are advised not to use setString(). Instead it is recommended that the dedicated API function setBlob() be used instead.

    The use of setString() can cause a Packet too large error message. The error will occur 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++, as this could lead to security issues, such as extremely large memory allocation requests due to malevolently long strings.

    However, if setBlob() is used, this problem does not arise. This is because setBlob() takes a streaming approach based on std::istream. When sending the data from the stream to MySQL Server, MySQL Connector/C++ will split the stream into chunks appropriate for MySQL Server and observe the max_allowed_packet setting currently being used.

    Caution

    When using setString() it is not possible to set max_allowed_packet to a value large enough for the string, prior to passing it to MySQL Connector/C++. The MySQL 5.1 documentation for max_allowed_packet states: “As of MySQL 5.1.31, the session value of this variable is read only. Before 5.1.31, setting the session value is permitted but has no effect.

    This difference with 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.1. As a new product, MySQL Connector/C++ is primarily targeted at the MySQL Server GA version that was available on its release.

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

    • DatabaseMetadata::getCrossReference()

    • DatabaseMetadata::getExportedKeys()

  • MySQL Connector/C++ includes a method Connection::getClientOption() which 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:

    • metadataUseInfoSchema

    • defaultStatementResultType

    • defaultPreparedStatementResultType

    The connection option metadataUseInfoSchema controls whether to use the Information_Schemata for returning the meta data of SHOW commands. In the case of metadataUseInfoSchema the optionValue argument should be interpreted as a boolean upon return.

    In the case of both defaultStatementResultType and defaultPreparedStatementResultType, the optionValue argument should be interpreted as an integer upon return.

    The connection property can be either set 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 are given below:

    int defaultStmtResType;
    int defaultPStmtResType;
    conn->getClientOption("defaultStatementResultType", (void *) &defaultStmtResType);
    conn->getClientOption("defaultPreparedStatementResultType", (void *) &defaultPStmtResType);
    
    bool isInfoSchemaUsed;
    conn->getClientOption("metadataUseInfoSchema", (void *) &isInfoSchemaUsed);
  • MySQL Connector/C++ also 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)

    Note that both methods are members of the MySQL_Connection class. The methods get and set MySQL session variables.

    setSessionVariable() is equivalent to executing:

    SET SESSION <varname> = <value>

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

    SHOW SESSION VARIABLES LIKE "<varname>"

    You can use “%” and other placeholders in <varname>, if the underlying MySQL server supports this.

  • 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 of 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 found to be different in each case. The Statement returns the maximum positive value for BIGINT. The Prepared Statement returns 0.

    The reason for the different results is due to 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 above scenario a very large negative value was fetched with GetUInt64(), which fetches unsigned integers. As 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 above scenario the large negative value will need to 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 variable. 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, code such as the following should be used, 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. 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. This is not present in MySQL Connector/C++. You therefore need to use the methods from the Statement and Prepared Statement classes to run a Stored Procedure using CALL.

Copyright © 2010-2020 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout