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.

Why do this the hard way? A column in a dbase table can store the value of a field in a structure. — Hans Passant, Sep 08 '10 at 17:00
I am experimenting with an "action" system for my game server where an entity can have a component called "useable" that is linked to an "action". I want the action's type and parameters to be in one table with the parameters being a blob because the parameters are different for every action. Although this may not be a good way to do it, as I said I am experimenting and I like to try things out and learn from it. — Barakat, Sep 08 '10 at 17:25

score 1 · Answer 1 · edited May 23 '17 at 11:47

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.

Is the 'myData' variable supposed to be casted as a (char*) in the mysql_real_escape_string function? — Barakat, Sep 08 '10 at 17:17
That's entirely possible, I didn't run this through a compiler. (My answer is inspired from [this example](http://zetcode.com/tutorials/mysqlcapitutorial/)). — Seth, Sep 08 '10 at 17:58

score 1 · Answer 2 · edited May 23 '17 at 12:19

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: MySql Connector prepared statement only transfers 64 bytes

Would I serialize each field separately by casting each field as a (char*) and concatenating them together? — Barakat, Sep 08 '10 at 17:16
BTW, don't mention `char *` around here, it upsets people. Use `std::string` instead. My field classes return a `std::string` for their *value*, so I can put it into an SQL statement string. — Thomas Matthews, Sep 08 '10 at 20:06

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

2 Answers2