Fix MYSQL truncation using C api

Question

I use prepared statements with the MYSQL C API, and I'm having problems truncating. mysql_stmt_fetch()always returns MYSQL_DATA_TRUNCATED, and I do not understand why. Can someone explain to me the reasons why the data will be truncated to help me fix this problem? The only reason I can think of is because my buffer variables are not of the right type, but I think they are correct. If you are interested, the code I'm using is below. I also showed a copy of my table and its structure, which I took directly from the commands in the MYSQL client.

Some notes about the code:

I modified it to print the values ​​even when it returns truncated data. When I do this, the first field seems to print correctly, but the float field and datetime field are distorted. Also, it seems to be reading the correct number of lines.

Most of the code is error checking of various function calls. None of these error checks work - or at least none of them prints messages as you expected. The only sign of error is the value MYSQL_DATA_TRUNCATEDreturned by the fetch call.

Describe the measurement table:

+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| nodeId     | int(11)  | NO   | PRI | NULL    |       |
| recordtime | datetime | NO   | PRI | NULL    |       |
| slevel     | float    | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+

Current contents of the measurement table:

+--------+---------------------+--------+
| nodeId | recordtime          | slevel |
+--------+---------------------+--------+
|      1 | 2015-09-22 19:33:50 |      2 |
|      1 | 2015-09-24 21:10:20 |      2 |
|      2 | 2015-09-22 19:33:53 |      5 |
|      3 | 2015-09-22 19:33:55 |      2 |
|      3 | 2015-09-22 19:45:42 |      4 |
|      3 | 2015-09-24 21:12:12 |      2 |
|      3 | 2015-09-24 21:13:30 |    3.4 |
|      4 | 2015-09-22 19:33:57 |      7 |
|      4 | 2015-09-24 21:05:53 |      5 |
|      4 | 2015-09-24 21:07:27 |      3 |
|      4 | 2015-09-24 21:22:52 |    9.9 |
|      4 | 2015-09-24 21:35:53 |      5 |
|      6 | 2015-09-24 21:26:01 |    2.2 |
|      6 | 2015-09-24 21:28:15 |    5.4 |
+--------+---------------------+--------+
14 rows in set (0.00 sec)

My code is:

static void select_rows (MYSQL_STMT *stmt){
char          *stmt_str = "SELECT nodeId, recordtime, slevel FROM Measurements";
MYSQL_BIND    param[3];
int           my_int;
float         my_float;
MYSQL_TIME    my_datetime;
my_bool       is_null[3];

  if (mysql_stmt_prepare (stmt, stmt_str, strlen (stmt_str)) != 0){
    print_stmt_error (stmt, "Could not prepare SELECT statement");
    return;}

  if (mysql_stmt_field_count (stmt) != 3){
    print_stmt_error (stmt, "Unexpected column count from SELECT");
    return;}

  memset ((void *) param, 0, sizeof (param)); /* zero the structures */

  /* set up INT parameter */

  param[0].buffer_type = MYSQL_TYPE_LONG;
  param[0].buffer = (void *) &my_int;
  param[0].is_unsigned = 0;
  param[0].is_null = &is_null[0];

  /* set up FLOAT parameter */

  param[1].buffer_type = MYSQL_TYPE_FLOAT;
  param[1].buffer = (void *) &my_float;
  param[1].buffer = (void *) &my_float;
  param[1].is_null = &is_null[1];

  /* set up DATETIME parameter */

  param[2].buffer_type = MYSQL_TYPE_DATETIME;
  param[2].buffer = (void *) &my_datetime;
  param[2].is_null = &is_null[2];

  if (mysql_stmt_bind_result (stmt, param) != 0){
    print_stmt_error (stmt, "Could not bind parameters for SELECT");
    return;}

  if (mysql_stmt_execute (stmt) != 0){
    print_stmt_error (stmt, "Could not execute SELECT");
    return;}

  if (mysql_stmt_store_result (stmt) != 0){
    print_stmt_error (stmt, "Could not buffer result set");
    return;}
  else{
    printf ("Number of rows retrieved: %lu\n",
            (unsigned long) mysql_stmt_num_rows (stmt));}

  int ii = mysql_stmt_fetch (stmt);
  while (ii == 0 || ii==MYSQL_DATA_TRUNCATED)  /* fetch each row */
{
    /* display row values */
    printf ("%d  ", my_int);
    printf ("%.2f  ", my_float);
    printf ("%04d-%02d-%02d %02d:%02d:%02d\n",
            my_datetime.year,
            my_datetime.month,
            my_datetime.day,
            my_datetime.hour,
            my_datetime.minute,
            my_datetime.second);
  ii=mysql_stmt_fetch (stmt);
  }
  mysql_stmt_free_result (stmt);      /* deallocate result set */
}

Answer 1

. MYSQL_BIND , mysql_stmt_bind_param(), . , float 3 , param[]. MYSQL_BIND .

, , MYSQL , , .