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 · Accepted Answer · 2017-03-17 16:48:09Z

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, 2017 at 16:48

answered Mar 16, 2017 at 21:09

Iharob Al Asimi

53.1k6 gold badges66 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Shakey Jay Over a year ago

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: dev.mysql.com/doc/refman/5.7/en/mysql-fetch-row.html

Iharob Al Asimi Over a year ago

@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?

Shakey Jay Over a year ago

Yeah sorry for not including it but this is the declaration: MYSQL *conn = mysql_init(nullptr); MYSQL_RES *res = nullptr; MYSQL_ROW row = nullptr;

Iharob Al Asimi Over a year ago

@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.

MSalters Over a year ago

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.

|

Roy · Accepted Answer · 2023-04-15 14:05:49Z

0

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

answered Apr 15, 2023 at 14:05

Roy

3323 silver badges7 bronze badges

Collectives™ on Stack Overflow

MySQL C API returns zero instead of NULL for NULL values

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related