From: Daniel Thorpe
Date: January 7 2007 3:49pm
Subject: inserting a vector into a MySQL blob field
List-Archive: http://lists.mysql.com/plusplus/6249
Message-Id: <6D4FF04F-C2DC-4EAF-8C32-5E3AB02D012A@gmail.com>
MIME-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=UTF-8; delsp=yes; format=flowed
Content-Transfer-Encoding: quoted-printable

Hi, I've searched all over the internet for information about =20
inserting data into blob fields, and I've looked at the mysql++ =20
examples. However I still can't seem to solve my problem, so I'm =20
posting here...

I'm trying to store a vector of doubles into a blob field in a mysql =20
database, along with the size of the vector in another field. I'm =20
using C++ with MySQL++  2.1.1 and MySQL 5.0.27.

My code looks something like this....

		char dataBuff[500];
		int dataLength =3D 0;
		int bufferLength =3D 0;
		int vectorLength =3D this->data.size();				=
// this->data is an STL =20
vector of doubles
	=09
		memset(dataBuff, 0, 500);					=
	// zero out the buffer
		dataLength =3D sizeof(double);					=
// the length of a double

		for(int i=3D0; 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 +=3D dataLength;
		}
	=09
		string blob(dataBuff, bufferLength);				=
// Copy the length of the =20
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 =3D con.query();

			// We need a string stream
			ostringstream strbuf;		=09
			strbuf << "INSERT INTO centralisedMoment (order, =
data, com_x, =20
com_y) VALUES (" << this->order << ", \"" << mysqlpp::escape << blob =20
<< mysqlpp::escape << "\", " << this->com->x << ", " << this->com->y =20
<< ")";

			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?=D9=93???tB^????M7Z[?jR<f??Wc:??>???c.X@>vQ?;O=20=

\???r??!?>=D0=A1?R?&1>J5.??Y>C?y?)?
INSERT INTO centralisedMoment (order, data, com_x, com_y) VALUES (4, =20
"\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?=20
(D?=D9=93???tB^????M7\ZZ[?jR<f??Wc:??>???c.X@>vQ?;O\\???r??!?>=D0=A1?R?&1>=
J5.??=20
Y>C?y?)?", 259, 257)
Query error: You have an error in your SQL syntax; check the manual =20
that corresponds to your MySQL server version for the right syntax to =20=

use near 'order, data, com_x, com_y) VALUES (4, "\0\0\0\0\0\0\0\0\0\0=20
\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 =20=

do with escaping the blob - I don't really get what the =20
mysqlpp::escape does...

Any help on how to solve this problem is much appreciated.

Cheers
Dan