MySQL C API returns zero instead of NULL for NULL values

Question

We are attempting to take data from a file and insert it into a mysql table with this table format. And then later retrieve the data and put it back into the same format that the data was in the original file. This is how we create the table.

create_query = "CREATE TABLE IF NOT EXISTS ";
string date = "20170223";
string table_format = " (IDpk INT NOT NULL auto_increment,"
                      " symbol CHAR(8) NOT NULL,"
                      " number_1 BIGINT unsigned DEFAULT NULL,"
                      " number_2 BIGINT unsigned NOT NULL,"
                      " PRIMARY KEY(IDpk))";

This is how we are create the strings that are being inserted into the database.

 uint32_t item_type = stoi(tokens[2]);
      query = insert_query;

      switch(item_type)
      {
        case 0: //bid
        case 1: //ask
        {           "###,%s,%s,"
                    "NULL,"
                    "%s,%s",
                    tokens[0], tokens[1], tokens[2]);
          break;
        }

And then we are passing the data to this function where we actually are inserting the data into the table.

void read_in_data(string date, string file, MYSQL* conn)
{
string query = "LOAD DATA INFILE " + file +
              "INTO TABLE " + date +
              "FIELDS TERMINATED BY ','"
              "LINE STARTING BY '###'";

mysql_query(conn, query.c_str());
}

The issue arises when we are attempting to get the data back from the database later and reformat it to be like the original input file. We noticed that our NULL values were represented by 0 and not NULL. We are curious how we would got about inserting them so that they are NULL and not 0. This is our current extraction method.

ofstream out_file;
out_file.open("test.txt", fstream::in);
if(out_file.good())
{
  res = mysql_store_result(conn);
  int num_fields = mysql_num_fields(res);
  while((row = mysql_fetch_row(res)))
  {
    unsigned long * lengths;
    lengths = mysql_fetch_lengths(res);
    for(int i = 0; i < num_fields; i++)
    {
      if(row[i])
      {
        out_file << row[i] << " ";
      }
    }
    out_file << endl;
  }
}else
cout << strerror(errno) << endl;

Iharob Al Asimi · Answer 1 · 2017-03-17T16:48:09.053

1

For NULL values, you need to check is_null from the results, I believe the MYSQL_BIND structure.

Since in your case you are using mysql_fetch_row(), I recommend reading this

The number of values in the row is given by mysql_num_fields(result). If row holds the return value from a call to mysql_fetch_row(), pointers to the values are accessed as row[0] to row[mysql_num_fields(result)-1]. NULL values in the row are indicated by NULL pointers.

As you can see, the problem is that this is a c library, and NULL in c++ is defined as 0^*, so that's why you are getting 0 for the NULL values in the database.

The only way around this, is to use the knowledge of the column type to determine whether it's a 0 (integer) or a NULL value, but for NULL integer values you have no way to know it.

If you can, use a prepared statement and test for the is_null member of the MYSQL_BIND structure, or write the whole thing in c, because this is a c library.

^*_{I think that's why the newer standards created the nullptr.}

edited Mar 17 '17 at 16:48

answered Mar 16 '17 at 21:09

Iharob Al Asimi

52,653
6
59
97

We are not using mysql_stmt_bind_result() or mysql_stmt_fetch() in our code so is there a way to test for NULL in the loop that we are currently using or do you have to bind the structure in that way? We were attempting to do this from the docs: https://dev.mysql.com/doc/refman/5.7/en/mysql-fetch-row.html – Shakey Jay Mar 16 '17 at 21:41
@ShakeyJay You see, the main reason why I hate c++ is because it's naturally unreadable. Is the definition/declaration of `row` anywhere in your code? – Iharob Al Asimi Mar 16 '17 at 21:43
Yeah sorry for not including it but this is the declaration: MYSQL *conn = mysql_init(nullptr); MYSQL_RES *res = nullptr; MYSQL_ROW row = nullptr; – Shakey Jay Mar 16 '17 at 21:59
@ShakeyJay I got an answer for you, and I think this time it will be satisfying! The problem is that, it's not a c++ library. – Iharob Al Asimi Mar 16 '17 at 22:09
Sorry, you seem to be making a mess of your C++. Your ideas about `NULL` just don't work that way. In C++, `0` _as an integral constant expression_ can be _converted_ to a null pointer. That's only in that direction, and only at compile time. Runtime nullpointer values don't convert to 0. – MSalters Mar 17 '17 at 15:55
@MSalters Conversion from/to `void *` in c++ is not possible without casting, the only way to compare a pointer to `NULL` is if `NULL` is defined like this `#define NULL 0`, [read this for further information](http://stackoverflow.com/a/177007/1983495). Otherwise, you would end up casting `NULL` to the pointer type or the pointer type to `void *`. Oh, and also, integers and pointers are inter-convertible in c, the fact that they are in c++ probably comes from the c heritage. – Iharob Al Asimi Mar 17 '17 at 16:41
@IharobAlAsimi: conversion _to_ `void*` is possible without casting. therefore, comparison to `(void*)0` also can work (common type). And no, pointers and integers haven't been interchangeable in C either. Famously, the 8086 segmented architecture had pointers which were _two_ integers. – MSalters Mar 17 '17 at 18:23
You really need to read the standard, 1. Attempt `int *x = malloc(somesize)` in c++, and tell me if it compiles. 2. Read the c standard regarding conversion between pointers and integers. 3. C is not C++. – Iharob Al Asimi Mar 17 '17 at 18:25

score 0 · Answer 2 · answered Apr 15 '23 at 14:05

I do normally do

auto   lengths = mysql_fetch_lengths(result);

and check for

if (row[i] == nullptr && lengths[i] == 0)

so you differentiate if is a 0 string (length 1) or an empty string (length 0), or NULL (both are 0)

But yes the api is orrible and there should be a better way

MySQL C API returns zero instead of NULL for NULL values

2 Answers2