The binary (prepared statement) 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 21.9.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_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); ..
User Comments
Add your own comment.