Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




25.2.10. C API Handling of Date and Time Values

The binary 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 25.2.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:

  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 pass the temporal value.

  3. 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_bind_results() 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_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");
  if (mysql_stmt_prepare(mysql, query, strlen(query)))
    fprintf(stderr, "\n mysql_stmt_prepare(), INSERT failed");
    fprintf(stderr, "\n %s", mysql_stmt_error(stmt));

  /* 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; 03;

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


  Published under the terms of the GNU General Public License Design by Interspire