9.1.9 C API Handling of Date and Time Values

The new binary protocol available in MySQL 4.1 and above 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 9.1.5 C API Prepared Statement Datatypes.

To send temporal data values, you create a prepared statement with mysql_prepare(). Then, before calling mysql_execute() to execute the statement, use the following procedure to set up each temporal parameter:

1. 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.
2. Set the buffer member of the MYSQL_BIND structure to the address of the MYSQL_TIME structure in which you will pass the temporal value.
3. Fill in the members of the MYSQL_TIME structure that are appropriate for the type of temporal value you're passing.

Use mysql_bind_param() to bind the parameter data to the statement. Then you can call mysql_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_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_prepare(mysql, query, strlen(query))); 

  /* setup 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_bind_param(stmt, bind);

  /* supply the data to be sent is the ts structure */
  ts.year= 2002;
  ts.month= 02;
  ts.day= 03;

  ts.hour= 10;
  ts.minute= 45;
  ts.second= 20;

  mysql_execute(stmt);
  .. 

User Comments

Add your own comment.