syntax for storing blob data in mysql db c++

Question

Just trying to store blob data (int array[128]) in a sql db.

I am having issue with my sql statement

 // temp is a 512 byte char array that was memcpyed from a 512 byte int array
 sprintf(insert, "insert into SiftFeatures(M_Id, FeatureData) values((Select M_Id from Master where M_Id='1'), 'Ab345' )" , temp);
 if(mysql_query(con, insert)){
    fprintf(stderr, "%s\n", mysql_error(con));
 }

The problem here is that when I do this the char * terminates on a null byte (i.e 0000 0000) I don't really know how to do this sql execution statement. Is there another way?

This looks good too: stackoverflow.com/questions/2548130/… — Thomas Matthews
– Thomas Matthews, Commented May 14, 2014 at 23:30
Thanks Thomas you been a great resource. I would upvote If I could — Jay
– Jay, Commented May 15, 2014 at 0:08

Randall Cook · Accepted Answer · 2014-05-15 17:44:47Z

1

You need to escape the data. Here's a rough example:

// your array which is to become a blob
int array[128];

// compute the maximum size of the escaped blob
int escaped_size = 2 * sizeof(array) + 1;

// get some storage for the escaped blob
char chunk[escaped_size];

// now escape the blob into the storage
mysql_real_escape_string(con, chunk, (const char*)array, sizeof(array));

// form a query string template and measure its length
const char* query_template = "INSERT INTO SiftFeatures(M_Id, FeatureData) VALUES((Select M_Id from Master where M_Id='1'), '%s')";
size_t template_len = strlen(query_template);

// provide enough space to hold the rendered query template
// (i.e. the query text and the escaped blob)
int query_buffer_len = template_len + escaped_size;
char query[query_buffer_len];

// now render the final query string (template plus escaped blob)
int query_len = snprintf(query, query_buffer_len, query_template, chunk);

// execute the query
mysql_real_query(con, query, query_len);

edited May 15, 2014 at 17:44

answered May 14, 2014 at 22:59

Randall Cook

6,8066 gold badges36 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

Jay Over a year ago

This looks interesting! I think I may try this!

Jay Over a year ago

I get an error: cannot convert ‘int*’ to ‘const char*’ for argument ‘3’ to ‘long unsigned int mysql_real_escape_string(MYSQL*, char*, const char*, long unsigned int)’

Randall Cook Over a year ago

The escaping is just to satisfy the requirement that mysql_real_query gets a true string with no embedded nulls. MySQL itself will store zeroes as zeroes in its tables, since presumably the FeatureData column is a blob. And you'll need to cast the int* to a const char* when you pass it to mysql_real_escape_string. I'll edit my answer.

Randall Cook Over a year ago

Thanks for the +1, Jay. :) When reading the data out, be aware of how it is encoded, and how you are asking the computer to display it. In a MYSQL_ROW array, each item is a pointer to the data for a field, even binary data. Use mysql_fetch_lengths to determine how long each field is. You can't print binary data directly to cout. You need to convert it to something printable (like ASCII) first, typically by rendering each byte as hex:

const char* p = (const char*)row[0]; unsigned long n = mysql_fetch_lengths(myresult)[0]; for (unsigned long i = 0; i < n; i++) { printf("%02X", p[i]); }

Randall Cook Over a year ago

I think there is an error in your (or my) printing code. If the byte being printed is a (signed) char, when it reaches 0x80, it is treated as -128 and printf prints it as a 32-bit negative number, which is 0xFFFFFF80. If the byte is treated as an unsigned char, then it will stay positive and will be printed as '80', as expected. I should have written const unsigned char* p = (const unsigned char*)row[0]; in my example above. Oops.

|

Thomas Matthews · Accepted Answer · 2014-05-14 23:18:12Z

0

I admit, I cheated on this one.

I wrote my binary data to a file then issued the MySql statement LOAD DATA INFILE:
Syntax MySQL LOAD DATA INFILE

The other method is to use a prepared statement and the setBlob method of the sql::PreparedStatement class, see prepared_statement.h in the cppconn folder.

Search the web for "mysql load blob" or "mysql connector c++ store blob".

answered May 14, 2014 at 23:18

Thomas Matthews

58.1k18 gold badges105 silver badges165 bronze badges

2 Comments

Jay Over a year ago

I have no such file found when I try to include it And I can't find that folder.

Thomas Matthews Over a year ago

I downloaded the source for 1.1.2. They are up to 1.1.3: dev.mysql.com/downloads/connector/cpp

Collectives™ on Stack Overflow

syntax for storing blob data in mysql db c++

2 Answers 2

17 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

17 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related