3.6.4 Prepared Statement Handling of Date and Time Values (original) (raw)

3.6.4 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, andTIMESTAMP), using theMYSQL_TIME structure. The members of this structure are described inSection 6.2, “C API Prepared Statement Data Structures”.

To send temporal data values, create a prepared statement usingmysql_stmt_prepare(). Then, before callingmysql_stmt_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. ForDATE,TIME,DATETIME, orTIMESTAMP values, setbuffer_type toMYSQL_TYPE_DATE,MYSQL_TYPE_TIME,MYSQL_TYPE_DATETIME, orMYSQL_TYPE_TIMESTAMP, respectively.
2. Set the buffer member of theMYSQL_BIND structure to the address of the MYSQL_TIME structure in which you pass the temporal value.
3. 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() ormysql_stmt_bind_named_param() to bind the parameter data to the statement. Then you can callmysql_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 thebuffer member to the address of aMYSQL_TIME structure into which the returned value should be placed. Usemysql_stmt_bind_result() to bind the buffers to the statement after callingmysql_stmt_execute() and before fetching the results.

Here is a simple example that insertsDATE,TIME, andTIMESTAMP data. Themysql 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_named_param(stmt, bind, 3, NULL);

  /* 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);
  ..