25.2.10. C API Handling of Date and Time Values
The binary protocol allows you to send and receive date and time
values (DATE, TIME,
DATETIME, and TIMESTAMP),
using the MYSQL_TIME structure. The members
of this structure are described in
Section 25.2.5, “C API Prepared Statement Data types”.
To send temporal data values, create a prepared statement using
mysql_stmt_prepare(). Then, before calling
mysql_stmt_execute() to execute the
statement, use the following procedure to set up each temporal
parameter:
In the MYSQL_BIND structure associated
with the data value, set the buffer_type
member to the type that indicates what kind of temporal
value you're sending. For DATE,
TIME, DATETIME, or
TIMESTAMP values, set
buffer_type to
MYSQL_TYPE_DATE,
MYSQL_TYPE_TIME,
MYSQL_TYPE_DATETIME, or
MYSQL_TYPE_TIMESTAMP, respectively.
Set the buffer member of the
MYSQL_BIND structure to the address of
the MYSQL_TIME structure in which you
pass the temporal value.
Fill in the members of the MYSQL_TIME
structure that are appropriate for the type of temporal
value to be passed.
Use mysql_stmt_bind_param() to bind the
parameter data to the statement. Then you can call
mysql_stmt_execute().
To retrieve temporal values, the procedure is similar, except
that you set the buffer_type member to the
type of value you expect to receive, and the
buffer member to the address of a
MYSQL_TIME structure into which the returned
value should be placed. Use
mysql_bind_results() to bind the buffers to
the statement after calling
mysql_stmt_execute() and before fetching the
results.
Here is a simple example that inserts DATE,
TIME, and TIMESTAMP data.
The mysql variable is assumed to be a valid
connection handle.
MYSQL_TIME ts;
MYSQL_BIND bind[3];
MYSQL_STMT *stmt;
strmov(query, "INSERT INTO test_table(date_field, time_field,
timestamp_field) VALUES(?,?,?");
stmt = mysql_stmt_init(mysql);
if (!stmt)
{
fprintf(stderr, " mysql_stmt_init(), out of memory\n");
exit(0);
}
if (mysql_stmt_prepare(mysql, query, strlen(query)))
{
fprintf(stderr, "\n mysql_stmt_prepare(), INSERT failed");
fprintf(stderr, "\n %s", mysql_stmt_error(stmt));
exit(0);
}
/* set up input buffers for all 3 parameters */
bind[0].buffer_type= MYSQL_TYPE_DATE;
bind[0].buffer= (char *)&ts;
bind[0].is_null= 0;
bind[0].length= 0;
...
bind[1]= bind[2]= bind[0];
...
mysql_stmt_bind_param(stmt, bind);
/* supply the data to be sent in the ts structure */
ts.year= 2002;
ts.month= 02;
ts.day= 03;
ts.hour= 10;
ts.minute= 45;
ts.second= 20;
mysql_stmt_execute(stmt);
..
