Re: Storing BLOB with C++

I solved the part with uploading a blob into MySQL with mysql++

here some hints maybe interesting for other users, since nobody seems to
have the right answer :-(

( For the part of "downloading" a blod, I'll start a new thread. There's a
German saying, like "new game new luck..." )

***
Some commands to test Blob-Table on local Server:
( Table called "media_binaerdaten", blob row is "Daten"

-- Insert a File located locally on MySQL-Serverinto a blob-column
INSERT INTO media_binaerdaten (Daten) values(load_file("c:/tmp/test.zip");
-- get length of blob-fields
SELECT Key,length(Daten) FROM `media_binaerdaten` WHERE 1
-- DUMP Files back to
SELECT Daten  FROM `media_binaerdaten` WHERE FKMedia_ID=47 INTO OUTFILE
"C:/tmp/test.zip
   -> gets data WITH escapes, can be insertet again
SELECT Daten  FROM `media_binaerdaten` WHERE FKMedia_ID=47 INTO DUMPFILE
"C:/tmp/test.zip
  -> gets raw data (WITHout) escapes, can be used as "binary", e.g. *.zip or
*.exe
***

About strings and escaping in the manual:
---------------------------------------------------------------
If you want to insert binary data into a BLOB column, the following
characters must be represented by escape sequences:

NUL ASCII 0. You should represent this by `\0' (a backslash and an
ASCII `0' character).
\  ASCII 92, backslash. Represent this by `\\'.
'  ASCII 39, single quote. Represent this by `\''.
"  ASCII 34, double quote. Represent this by `\"'.
If you write C code, you can use the C API function mysql_escape_string
() to escape characters for the INSERT statement. See section 22.3 C
API function overview. In Perl, you can use the quote method of the DBI
package to convert special characters to the proper escape sequences.
See section 22.5.2 The DBI interface.

You should use an escape function on any string that might contain any
of the special characters listed above!
------------------------------------------------------------------

***
My function to escape BLOBS befor upload to MySQL-Server via "INSERT"

// aus den Sourcen von mysql++
void DatenbankMySQL::escape_blob (std::string& s) {
 if (!s.size()) return;
 for (unsigned int i = 0;i<s.size();i++)
 {
    switch (s[i]) {
    case '\0':    /* Must be escaped for "mysql" */
      s[i] = '\\';
      s.insert(++i,"0",1);
      break;
    case '\\':
      s[i] = '\\';
      s.insert(++i,"\\",1);
      break;
    case '\"':
      s[i] = '\\';
      s.insert(++i,"\"",1);
      break;
    case '\'':    /* Better safe than sorry */
      s[i] = '\\';
      s.insert(++i,"\'",1);
      break;
    /*
 case '\032':   // This gives problems on Win32
      s[i] = '\\';
      s.insert(++i,"Z",1);
      break;
   */
  default: break;
    };
 }
}

***

I found the code in  th Source of C++ API (mysql++)
available at
http://www.mysql.com/Downloads/mysql++/mysql++-1.7.9.tar.gz
in file "string_util.cc"
-> void escape_string (string& s)

but I had to change some lines, e.g.
    case '\"':
      s[i] = '\\';
      s.insert(i,"\"",1);  i++;
      break;

became

    case '\"':
      s[i] = '\\';
      s.insert(++i,"\"",1);
      break;

look at the ++!

the reson was, it produced "/ out of an " instead of /"

I don't know why, but i guess the original "escape-manipulator" is working
in the compiled msql++ API.

Greetings to the MySQL und mysql++ teams!

--
Franz
"mysql++ Newbie"