Write data read from database to binary file

Question

I want to write the data I read from database to binary file. In the query I need to read a lot of data and some of them are group_concat. Now I want to write them into a binary file. The question is how can I define the size of them. At first I used put() but the result was wrong because put requires the type of char while row[] is char*. Then I tried write and defined the size by sizeof(int)*number of elements in group but it was still wrong. Please show me a way.

MYSQL_RES* res = GetDBManager()->Query("select ls.time, count(ws.id) , group_concat(wc.id) , group_concat(ws.SIGNAL_STRENGTH) , ul.LONGITUDE , ul.LATITUDE , ul.ALTITUDE  from user_location_scan ls, wifi_scan ws, wifi_cell wc, user_location ul where ls.id = ws.user_scan and ws.wifi_cell = wc.id and ls.time = ul.time group by ls.id order by ls.id");
    if (res == NULL) return false;

MYSQL_ROW row;

ofstream myFile("data.bin", ios::out | ios::binary | ios::app);

while (row = mysql_fetch_row(res)) {
    myFile.write(row[0], sizeof(char));
    myFile.write(row[1], sizeof(int));
    myFile.write(row[2], sizeof(int)*atoi(row[1]));
    myFile.write(row[3], sizeof(int)*atoi(row[1]));
    myFile.write(row[4], sizeof(double));
    myFile.write(row[5], sizeof(double));
    myFile.write(row[6], sizeof(double));
    //myFile.write(row[0], sizeof(row[0]));
    //myFile.write(row[1], sizeof(row[1]));
    //myFile.write(row[2], sizeof(row[2]));
    //myFile.write(row[3], sizeof(row[3]));
    //myFile.write(row[4], sizeof(row[4]));
    //myFile.write(row[5], sizeof(row[5]));
    //myFile.write(row[6], sizeof(row[6]));
    //myFile.put((char)row[0]);
    //myFile.put((char)row[1]);
    //myFile.put((char)row[2]);
    //myFile.put((char)row[3]);
    //myFile.put((char)row[4]);
    //myFile.put((char)row[5]);
    //myFile.put((char)row[6]);
    //myFile << row[0] << row[1] << row[2] << row[3] << row[4] << row[5] << row[6];

}
mysql_free_result(res);

Answer 1

The MySql function mysql_fetch_row() returns an array of null-terminated strings (character arrays). You can use std::strlen() to get the length of these null-terminated character arrays.

If you want to write everything out as strings into the file then it is sufficient to do something like this:

while((row = mysql_fetch_row(res)))
    for(unsigned col = 0; col < 7; ++col)
        myFile.write(row[col], std::strlen(row[col]) + 1);

If you want to write the numeric columns as binary numbers (harder to make portable) then you need to convert the strings and write the values out differently.

Much less recommended is writing each record out in binary. This is very non-portable.

struct record
{
    // choose better column names
    char col_0[32]; // largest possible string
    int col_1;
    int col_2;
    int col_3;
    double col_4;
    double col_5;
    double col_6;
};

while((row = mysql_fetch_row(res)))
{
    record r; // store our binary values here

    //copy the string
    std::strncpy(r.col_0, row[0], sizeof(r.col_0));

    r.col_1 = std::atoi(row[1]); // convert the integers
    r.col_2 = std::atoi(row[2]);
    r.col_3 = std::atoi(row[3]);

    r.col_4 = std::atof(row[4]); // convert the floats
    r.col_5 = std::atof(row[5]);
    r.col_6 = std::atof(row[6]);

    // write the whole object out in binary (very non-portable)
    myFile.write(reinterpret_cast<char*>(&r), sizeof(r));
}

And to read the record in again:

// reading back

record r; // store our binary values here

if(myFile.read(reinterpret_cast<char*>(&r), sizeof(r)))
{
    // success, use the record here
    std::cout << r.col_0 << '\n';
    std::cout << r.col_1 << '\n';
    std::cout << r.col_2 << '\n';
    // ... etc
}

NOTE: Using binary is more complex, harder to get right, un-portable and generally to be avoided unless its an absolute requirement.