List:MySQL++« Previous MessageNext Message »
From:Daniel Thorpe Date:January 7 2007 3:49pm
Subject:inserting a vector into a MySQL blob field
View as plain text  
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




Thread
inserting a vector into a MySQL blob fieldDaniel Thorpe7 Jan
  • Re: inserting a vector into a MySQL blob fieldMichael Hanselmann7 Jan
    • Re: inserting a vector into a MySQL blob fieldBill K9 Jan
  • Re: inserting a vector into a MySQL blob fieldWarren Young9 Jan