Skip navigation links
MySQL 5.0 Reference Manual :: 20 Connectors and APIs :: 20.8 MySQL C API :: 20.8.13 C API Prepared Statement Problems
20.8.14 C API Prepared Statement Handling of Date and Time Values »
Section Navigation      [Toggle]

20.8.13. C API Prepared Statement Problems

Here follows a list of the currently known problems with prepared statements:

  • TIME, TIMESTAMP, and DATETIME do not support parts of seconds (for example, from DATE_FORMAT()).

  • When converting an integer to string, ZEROFILL is honored with prepared statements in some cases where the MySQL server doesn't print the leading zeros. (For example, with MIN(number-with-zerofill)).

  • When converting a floating-point number to a string in the client, the rightmost digits of the converted value may differ slightly from those of the original value.

  • Prepared statements do not use the query cache, even in cases where a query does not contain any placeholders. See Section 7.5.5.1, “How the Query Cache Operates”.

  • Prepared statements do not support multi-statements (that is, multiple statements within a single string separated by “;” characters).

  • In MySQL 5.0, prepared CALL statements cannot invoke stored procedures that return result sets because prepared statements do not support multiple result sets. Nor can the calling application access a stored procedure's OUT or INOUT parameters when the procedure returns. These capabilities are supported beginning with MySQL 5.5.


User Comments

Posted by Yakov Livschitz on February 9 2007 7:57pm[Delete] [Edit]

Be especially careful with the floating-point values in prepared statements when using replication. If a value is not a usual number but something like nan or -inf, it will be successfully stored as NULL on the server you're connected to, but replicated as "nan" (string) to the slaves, which will stop because of a syntax error.

Another issue is checksums of the tables. There are recommendations on the Web to check if master and slave are on sync comparing table checksums. However due to a limited floating point precision in ASCII SQL statements sent between master and slave (if query-level replication is used), the floating-point value on the slave is slightly different which results in a different table checksum.

Add your own comment.