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 22.9.5, “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 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_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 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); ..