Description:
When I tried to INSERT a TIME value using prepared statement and the C API, it turned out that the day field of the MYSQL_TIME structure was multipled by 24 and added to the hour field. This is contrary to the following statement in the section 22.2.5. "C API Prepared Statement Data types" of the manual:
"Only those parts of a MYSQL_TIME structure that apply to a given type of temporal value are used: The year, month, and day elements are used for DATE, DATETIME, and TIMESTAMP values. The hour, minute, and second elements are used for TIME, DATETIME, and TIMESTAMP values."
How to repeat:
Below is a complete C source for a tiny program that creates a test table consisting of just one column of type TIME and inserts one row into it. Hour, minute and second field are set to 23, 58 and 59 respectively. However, inserted value was 47:58:59:
mysql> select * from timebug;
+----------+
| timecol |
+----------+
| 47:58:59 |
+----------+
1 row in set (0.00 sec)
Here is the program's source:
-------------------------------------------------------------------------------
#include <memory.h>
#include <mysql/mysql.h>
int main ()
{
MYSQL conn;
char query[]= "INSERT INTO timebug VALUES (?)";
MYSQL_STMT *stmt;
MYSQL_TIME time;
MYSQL_BIND bind;
unsigned long length;
// Init
mysql_init (&conn);
mysql_real_connect (
&conn,
NULL, // localhost
NULL, // default user
NULL, // no password
"testdb", // database
0, // default port
NULL, // no socket or named pipe name
0); // no client flags
// Create test table
mysql_query (&conn, "DROP TABLE IF EXISTS timebug");
mysql_query (&conn, "CREATE TABLE timebug (timecol TIME)");
// Prepare SELECT query
stmt = mysql_stmt_init (&conn);
mysql_stmt_prepare (stmt, query, sizeof (query) - 1);
// Init parameter
time.day = 1; // THIS GETS CALCULATED INTO HOUR!!!
time.hour = 23;
time.minute = 58;
time.second = 59;
time.second_part = 0;
time.neg = 0;
// Bind parameter
length = sizeof (MYSQL_TIME);
memset (&bind, 0, sizeof (bind));
bind.buffer_type = MYSQL_TYPE_TIME;
bind.buffer = &time;
bind.buffer_length = sizeof (MYSQL_TIME);
bind.length = &length;
// Do insert the value
mysql_stmt_bind_param (stmt, &bind);
mysql_stmt_execute (stmt);
// Cleanup
mysql_stmt_close (stmt);
mysql_close (&conn);
return 0;
}
// eof
-------------------------------------------------------------------------------
Suggested fix:
Perhaps this is a feature and it is the manual that shall be fixed by listing the day field as one which applies to the TIME type.
Description: When I tried to INSERT a TIME value using prepared statement and the C API, it turned out that the day field of the MYSQL_TIME structure was multipled by 24 and added to the hour field. This is contrary to the following statement in the section 22.2.5. "C API Prepared Statement Data types" of the manual: "Only those parts of a MYSQL_TIME structure that apply to a given type of temporal value are used: The year, month, and day elements are used for DATE, DATETIME, and TIMESTAMP values. The hour, minute, and second elements are used for TIME, DATETIME, and TIMESTAMP values." How to repeat: Below is a complete C source for a tiny program that creates a test table consisting of just one column of type TIME and inserts one row into it. Hour, minute and second field are set to 23, 58 and 59 respectively. However, inserted value was 47:58:59: mysql> select * from timebug; +----------+ | timecol | +----------+ | 47:58:59 | +----------+ 1 row in set (0.00 sec) Here is the program's source: ------------------------------------------------------------------------------- #include <memory.h> #include <mysql/mysql.h> int main () { MYSQL conn; char query[]= "INSERT INTO timebug VALUES (?)"; MYSQL_STMT *stmt; MYSQL_TIME time; MYSQL_BIND bind; unsigned long length; // Init mysql_init (&conn); mysql_real_connect ( &conn, NULL, // localhost NULL, // default user NULL, // no password "testdb", // database 0, // default port NULL, // no socket or named pipe name 0); // no client flags // Create test table mysql_query (&conn, "DROP TABLE IF EXISTS timebug"); mysql_query (&conn, "CREATE TABLE timebug (timecol TIME)"); // Prepare SELECT query stmt = mysql_stmt_init (&conn); mysql_stmt_prepare (stmt, query, sizeof (query) - 1); // Init parameter time.day = 1; // THIS GETS CALCULATED INTO HOUR!!! time.hour = 23; time.minute = 58; time.second = 59; time.second_part = 0; time.neg = 0; // Bind parameter length = sizeof (MYSQL_TIME); memset (&bind, 0, sizeof (bind)); bind.buffer_type = MYSQL_TYPE_TIME; bind.buffer = &time; bind.buffer_length = sizeof (MYSQL_TIME); bind.length = &length; // Do insert the value mysql_stmt_bind_param (stmt, &bind); mysql_stmt_execute (stmt); // Cleanup mysql_stmt_close (stmt); mysql_close (&conn); return 0; } // eof ------------------------------------------------------------------------------- Suggested fix: Perhaps this is a feature and it is the manual that shall be fixed by listing the day field as one which applies to the TIME type.