how to handle MySQL NULL in C++ using mysql.h?

Question

I'm trying to check for certain field values in an if. Most of these values are NULL in MySQL.

It seems to be screwing everything up.

I do

while ((row = mysql_fetch_row(res)) !=NULL){
    if(row[1] != "-1"){
        rows.push_back(row[0]);
    }
}

The field for row[1] is an INT LENGTH 1 equal to 1, -1, or NULL. Most are NULL, but a few are -1. I think that mysql.h outputs all values as char* (at least that's the only way I've been able to get it to work so far).

Anyways, strangely, rows gets filled, but it's filled with "nothing". I'm not even sure if it's an empty string or what.

Please help.

Many thanks in advance!

I put dashes in front and behind the std::cout of rows[i] in a for loop. It outputs a ton of --s.

If I std::cout the raw row[0], it outputs fine.

For us2012:

std::vector< char* > rows;

while ((row = mysql_fetch_row(res)) !=NULL){
    std::cout << "-" << row[0] << "-" << std::endl;
    if(row[1] != "-1"){
        rows.push_back(row[0]);
    }
}

std::cout << "Valids:" << std::endl;
for(int i = 0; i < rows.size(); ++i)
{
    std::cout << "id: -" << rows[i] << "-" << std::endl;
}

When I use this if

if(row[1] && row[1] != "-1")

The if works in reverse.

Note: I incremented an int in the while, couted that and rows and invalidRows. The counter gives 389, rows.size() 2, and invalidRows.size() 387.

I'm going to see if I can cout row[0] after I do the if...

couting row[0] after the if outputs the correct data.

if(row[1] != "-1") gives ...forbids comparison between pointer and integer...

Why mysql.h:

CentOS. I find hardly anything for it, and nothing my host supports.

if(row[1] && std::string(row[1]).compare("-1") != 0) put everything into invalidRows

Without row[] &&gavewhat(): basic_string::_S_construct NULL not valid Aborted`

Answer

It was if(row[1] && std::string(row[1]).compare("-1") == 0) all along! My logic got screwed up in all of the C++ crash coursing.

And using std::vector< std::string > allows row[x] to be pushed_back.

`I'm not even sure if it's an empty string`... It would be really nice if you found that out. Do some debugging and tell us what actually is in `rows`. By the way, the info about data format is all in the mysql api doc: http://dev.mysql.com/doc/refman/5.1/en/mysql-fetch-row.html , http://dev.mysql.com/doc/refman/5.0/en/c-api-data-structures.html , so there is no need to guess what types the returned values have... — us2012, Jan 24 '13 at 01:39
For starters, check what `rows.size()` is after the `while` loop returns. Does this match with the number of results you are expecting from your query? Also have a look at the example in the first link I gave you. Try it on your query. — us2012, Jan 24 '13 at 01:45
Okay, the bit about `row[0]` printing fine is valuable. In that case, let's see the code that you use to print `rows[i]`, and while you're at it, include the line where you declare and define `rows`. — us2012, Jan 24 '13 at 01:47

score 2 · Accepted Answer · edited May 23 '17 at 10:26

First things first: The API you are using is a C API. There's nothing inherently wrong about that, but it means that you have to deal with a lot of stuff that seems unneccessary because C++ has better tools available for this. If possible, you should therefore use a C++ wrapper like MySQL++ : http://tangentsoft.net/mysql++/ (all credits for this suggestion go to @Non-Stop Time Travel in the comments below)

In your original code, you're not comparing strings, you're comparing addresses. Your condition compares the memory address stored in row[1] with the memory address of the constant string literal "-1". What you actually want (if you stick to char *) is strcmp : C++ Compare char array with string

You're also pushing back pointers to row[0] which is temporary, it is reassigned with every iteration of the while loop! You have to make a copy of these and store the pointers to the copies.

This is a very quick idea of how you could approach this. Not really good C++, though (in fact, if it were not for the vector and the cout, it may as well be C. See above for the reasons.):

#include <string.h>

std::vector<char*> rows;
while ((row = mysql_fetch_row(res)) !=NULL){
    if(strcmp(row[1],"-1") != 0){
        char * store = new char[strlen(row[0])+1];
        strncpy(store ,row[0] ,strlen(row[0])+1);
        rows.push_back(store);
    }
}
std::cout << "Valids:" << std::endl;
for(int i = 0; i < rows.size(); ++i)
{
    std::cout << "id: -" << rows[i] << "-" << std::endl;
}

//clean up
for(int i = 0; i < rows.size(); ++i)
{
    delete [] rows[i];
}

In general, when you deal with strings in C++ I would recommend using std::string and std::string::compare instead, but this is problematic here - if your mysql rows hold binary data, you don't have guaranteed null-terminated strings anymore (see the spec for MYSQL_ROW here: http://dev.mysql.com/doc/refman/5.0/en/c-api-data-structures.html ). As pointed out by Non-Stop Time Travel in the comments below, std::string supports internal \0s, the code example below however doesn't work with them.

#include <string>

//this particular code works only if row[0] and row[1] are nullterminated

std::vector<std::string> rows;
while ((row = mysql_fetch_row(res)) !=NULL){
    if(std::string(row[1]).compare("-1") != 0){
        rows.push_back(std::string(row[0]));
    }
}
std::cout << "Valids:" << std::endl;
for(int i = 0; i < rows.size(); ++i)
{
    std::cout << "id: -" << rows[i] << "-" << std::endl;
}

//cleaning up the vector of strings is not necessary! yay!

)

@JoeCoderGuy I can't really see where I'm producing a segfault here, but I'm really tired so I may be missing something. Maybe your program cleans up the pointers later? Leave out my `clean up` section and try again. — us2012, Jan 24 '13 at 02:44
@JoeCodeGuy There is a huge amount of basics to cover, I'm afraid :). Oh, before I forget: This (a project which interfaces with mysql) is not a good project to start learning C++ with. The mysql API is essentially a C API. — us2012, Jan 24 '13 at 02:50
`std::string` supports strings with NULLs in them, precisely as much as your original `char*` do. `std::string` is **absolutely** the way to go. Better yet, use a C++ MySQL library in the first place (such as MySQL++). — Lightness Races in Orbit, Jan 24 '13 at 03:20
@Non-StopTimeTravel Thanks for the clarification, I'll edit that in my post, however my use of the constructor `std::string(const char *)` in my code sample *does* require a null-terminated string. — us2012, Jan 24 '13 at 03:23
@Non-StopTimeTravel I think you should turn your suggestion of MySQL++ into an answer. — us2012, Jan 24 '13 at 03:29
Why do you have to `clean up` `rows`? Does it stick around after the code has finished? Thanks for getting me over this hump! — , Jan 24 '13 at 15:57
@JoeCoderGuy That's too much to explain in an answer to this question. You should buy a good book about C++ where these things are explained. Until then - as stated above, the solution with `std::string` does not require manual cleanup. — us2012, Jan 24 '13 at 16:12

how to handle MySQL NULL in C++ using mysql.h?

1 Answers1