Hi, I've searched all over the internet for information about
inserting data into blob fields, and I've looked at the mysql++
examples. However I still can't seem to solve my problem, so I'm
posting here...
I'm trying to store a vector of doubles into a blob field in a mysql
database, along with the size of the vector in another field. I'm
using C++ with MySQL++ 2.1.1 and MySQL 5.0.27.
My code looks something like this....
char dataBuff[500];
int dataLength = 0;
int bufferLength = 0;
int vectorLength = this->data.size(); // this->data is an STL
vector of doubles
memset(dataBuff, 0, 500); // zero out the buffer
dataLength = sizeof(double); // the length of a double
for(int i=0; i<vectorLength; i++) { // Loop through the vector
// Copy each element of the vector to the memory space
memcpy(dataBuff + bufferLength, &data[i], dataLength);
// Increment the length of the buffer.
bufferLength += dataLength;
}
string blob(dataBuff, bufferLength); // Copy the length of the
data buffer to a string
cout << "bufferLength is: " << bufferLength << endl;
cout << "blob: " << blob << endl;
// Establish the connection to the database server.
mysqlpp::Connection con(mysqlpp::use_exceptions);
try {
// Make a connection
con.connect(MYSQL_SHAPE_DB, MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD);
// Create a query object
mysqlpp::Query query = con.query();
// We need a string stream
ostringstream strbuf;
strbuf << "INSERT INTO centralisedMoment (order, data, com_x,
com_y) VALUES (" << this->order << ", \"" << mysqlpp::escape <<
blob
<< mysqlpp::escape << "\", " << this->com->x << ", "
<< this->com->y
<< ")";
cout << strbuf.str() << endl;
query.exec(strbuf.str());
} // End of try
The various outputs look like this....
bufferLength is: 128
blob: ?p?)??R>???m???(D?3?ٓ???tB^????M7Z[?jR<f??Wc:??>???c.X@>vQ?;O
\???r??!?>С?R?&1>J5.??Y>C?y?)?
INSERT INTO centralisedMoment (order, data, com_x, com_y) VALUES (4,
"\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0?p?)??R>???m??\0\0\0\0\0\0\0\0?
(D?ٓ???tB^????M7\ZZ[?jR<f??Wc:??>???c.X@>vQ?;O\\???r??!?>С?R?&1>J5.??
Y>C?y?)?", 259, 257)
Query error: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near 'order, data, com_x, com_y) VALUES (4, "\0\0\0\0\0\0\0\0\0\0
\0\0\0\0\0\0?p?)??R>' at line 1
So, my question is, what am I doing wrong? I figure it's something to
do with escaping the blob - I don't really get what the
mysqlpp::escape does...
Any help on how to solve this problem is much appreciated.
Cheers
Dan
