27.7.16 C API Prepared Statement Handling of Date and Time Values
The binary (prepared statement) protocol enables 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 27.7.8, “C API Prepared Statement Data Structures”.
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 thebuffer_type
member to the type that indicates what kind of temporal value you're sending. ForDATE
,TIME
,DATETIME
, orTIMESTAMP
values, setbuffer_type
toMYSQL_TYPE_DATE
,MYSQL_TYPE_TIME
,MYSQL_TYPE_DATETIME
, orMYSQL_TYPE_TIMESTAMP
, respectively.Set the
buffer
member of theMYSQL_BIND
structure to the address of theMYSQL_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 pass.
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_stmt_bind_result()
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 handler.
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);
..