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);

Instead of using sizeof() try using strlen(row[0]) + 1 to give the length of the string including the null terminator. — Galik
– Galik, Commented Mar 5, 2015 at 4:13
@Galik wow worked like a charm. thank you so so much. anyway can you give me some documentation you use about what you just said? — Duc Nguyen
– Duc Nguyen, Commented Mar 5, 2015 at 4:17

Galik · Accepted Answer · 2015-03-05 04:43:05Z

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.

edited Mar 5, 2015 at 4:43

answered Mar 5, 2015 at 4:23

Galik

49k5 gold badges85 silver badges126 bronze badges

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

4 Comments

Duc Nguyen Over a year ago

thank you very much. at first i intended to convert them into numeric types so it is easier to read later. but i think writing as string is still good because i can cast the right type to the data when reading them from the file, cant i?

Galik Over a year ago

@DucAnhNguyen Writing as strings is preferable because it very portable and network friendly. However I will post a method you can use to write everything out in binary but its very non-portable (even between versions compiled using different flags on the same compiler).

Galik Over a year ago

@DucAnhNguyen As you can see using binary is more complex, harder to get right, un-portable and generally to be avoided unless its an absolute requirement.

Duc Nguyen Over a year ago

you are right. actually at the beginning i used JSON to avoid binary but my instructor asked me to use binary. i think he wanted me to know how to use different kinds. thank you once again and hope you have a nice day!

Collectives™ on Stack Overflow

Write data read from database to binary file

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related