0

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?

Jay
  • 2,656
  • 1
  • 16
  • 24

2 Answers2

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);
Randall Cook
  • 6,728
  • 6
  • 33
  • 68
  • This looks interesting! I think I may try this! – Jay May 15 '14 at 00:08
  • 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)’ – Jay May 15 '14 at 00:19
  • Oh I just figured out this is not what I want. I don't want the 0's escaped I still need them when I take the data out of the database. – Jay May 15 '14 at 00:27
  • 1
    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 May 15 '14 at 00:55
  • Yeah I did that before you edited thank you for the edit. I gave you a +1 on code because it actually works but having weird anomalies when I retrieve the data. Lets say I have my array start at 33 which in ascii is = "!". when I print out row[0] I get just "!" as my value, but when I print out row[1] I get all the escaped 0's starting at "\0\0\0]" which would be 93. where did 33-93 go? And why is my retrieval from the db so odd? row[0] = "!" and row[2] = everything else? – Jay May 15 '14 at 01:35
  • the syntax for retrieval is as follows: `mysql_query(con, "Select FeatureData from SiftFeatures where M_Id = '1'"); MYSQL_RES *myresult; MYSQL_ROW row; myresult = mysql_use_result(con); row = mysql_fetch_row(myresult); std::cout << row[0] << " " << row[1] << std::endl;` – Jay May 15 '14 at 01:42
  • And sometimes for row[1] print out if gives me `LETE CASCADE)` – Jay May 15 '14 at 01:45
  • 1
    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 May 15 '14 at 17:34
  • ok Thank makes sense but then why does 1: The first 3 zeros get cut out. 2: after int 127 everything goes crazy here is the hex dump. ` – Jay May 15 '14 at 19:44
  • `2100000022000000230000002400000025000000260000002700000028000000290000002A0000002B0000002C0000002D0000002E0000002F000000300000003100000032000000330000003400000035000000360000003700000038000000390000003A0000003B0000003C0000003D0000003E0000003F000000400000004100000042000000430000004400000045000000460000004700000048000000490000004A0000004B0000004C0000004D0000004E0000004F000000500000005100000052000000530000005400000055000000560000005700000058000000590000005A0000005B0000005C0000005D0000005E0000005F000000600000006100000062000000630000006400000065000000660000006700000068000000690000006A0000006B – Jay May 15 '14 at 19:45
  • 0000006C0000006D0000006E0000006F000000700000007100000072000000730000007400000075000000760000007700000078000000790000007A0000007B0000007C0000007D0000007E0000007F000000FFFFFF80000000FFFFFF81000000FFFFFF82000000FFFFFF83000000FFFFFF84000000FFFFFF85000000FFFFFF86000000FFFFFF87000000FFFFFF88000000FFFFFF89000000FFFFFF8A000000FFFFFF8B000000FFFFFF8C000000FFFFFF8D000000FFFFFF8E000000FFFFFF8F000000FFFFFF90000000FFFFFF91000000FFFFFF92000000FFFFFF93000000FFFFFF94000000FFFFFF95000000FFFFFF96000000FFFFFF97000000FFFFFF98000000FFFFFF99000000FFFFFF9A000000FFFFFF9B – Jay May 15 '14 at 19:47
  • 000000FFFFFF9C000000FFFFFF9D000000FFFFFF9E000000FFFFFF9F000000FFFFFFA0000000 – Jay May 15 '14 at 19:48
  • Well it works when I put it back into an array i.e `memcpy(arr, row[0], n);` so thank you you are the best I wish I could give more +1s – Jay May 15 '14 at 19:57
  • The first three zeroes are not being cut off. They come *after* the nonzero number (e.g. 21 in your comment) because Intel architectures are *little-endian* (lsb first), and so the four bytes of an integer are stored in memory in the opposite order of how you would read them if printed in a book (42 00 00 00 instead of 00 00 00 42 for the number 66). After 127 everything *should* go crazy because you are reading beyond the end of the data that was retrieved (this often triggers a segmentation fault). What you will find there is whatever earlier programs and computations left behind. – Randall Cook May 15 '14 at 20:06
  • I knew it was little endian but I thought that was for the whole array 21 starting at the end of the hex dump. But that makes sense thank you. On another note. Wait why would after 127 everything go crazy? I start at 33 and go + 128. So It should go crazy after 161 not 127. You see it there it continues to be fine until 7F. Then when it hits 8. We have 3 bytes of 1's. Is that some weird sign extension going on from the 8? I don't know... that wouldn't make any sense either but just an idea. – Jay May 15 '14 at 20:12
  • Its almost as if the values turn into a double. 8 bytes of information FF FF FF 80 00 00 00 – Jay May 15 '14 at 20:14
  • 1
    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. – Randall Cook May 15 '14 at 21:29
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".

Thomas Matthews
  • 56,849
  • 17
  • 98
  • 154