How do you serialize a struct and store it in a MySQL database?

Question

I am using C++ for an online game server project and I need to store some structs as binary blobs in a MySQL database. I am using RakNet for networking and have tried using its BitStream class to serialize the data but I don't know how to do it right. My question is, how do you turn a structure into a stream of bytes that you can pass into a MySQL database blob with an insert/update query? I am using the MySQL C libraries that come with the MySQL server download.

Answer 1

If there are no pointers in your struct, you can store it as a blob directly, something like:

void insertBlob(MYSQL *conn, MyStruct *myData)
{
    char myDataEscaped[2 * sizeof(MyStruct) + 1];
    char query[100 + 2 * sizeof(MyStruct) + 1];
    int queryLen;

    mysql_real_escape_string(conn, myDataEscaped, myData, sizeof(MyStruct)); 
    queryLen = snprintf(query, sizeof(query), 
                       "INSERT INTO my_table (id, blobdata) VALUES (NULL, '%s')",
                       myDataEscaped);

    mysql_real_query(conn, query, queryLen);
}

If there are pointers in your struct (to other structs or to strings), then you'll have to pull each field out separately and serialize it, or create a new structure without pointers to store the data (just don't store the pointer values themselves, since they will be meaningless when you deserialize the data). There are some libraries mentioned here which may be of some help.

Answer 2

First off, I don't suggest serializing C++ structures. The compiler is allowed to insert padding between members and there are other issues such as Endianness and bit-widths of types.

The preferred method is to write each field separately, preferably in human readable form such as ASCII or Unicode. This allows fields to be added, removed or changed in the future with minimal changes to the database.

That said, data is sent to the MySQL database using a query. I suggest that each class support methods for obtaining its SQL data type and attributes and its SQL value as a string. This will make the database I/O more generic.

I am using the C++ libraries that come with the MySQL download. I suggest you use them also since you are using C++.

I have designed my database package to use the Visitor design pattern. One of the visitors is an object that writes to the database.

Binary Large Objects (BLOBs) are best inserted using a prepared statement. See: http://stackoverflow.com/questions/3134532/mysql-connector-prepared-statement-only-transfers-64-bytes