Re: Uploading blobs, performance, some C++

I found identical problems in my application, in a similar environment. I
solved it in this way (ok, it's a big trick, but if you don't have
time...):

// I have to blob into MySql my file_name file with mysql++, so...
// boiled code for connection etc.
// ...
      Query query = connection_.query();
      query << "INSERT INTO " << MY_TABLE << " (" << MY_FIELD << ") VALUES
(LOAD_FILE(\"" << file_name << "\"))";
      query.store();

That't work and it's quite quick. No more problems about escape!

Ale

P.S. Remember to check the "max_allowed_packet" mysql parameter, if you
have big files.




                                                                                
                                                       
                      "Franz Alt"                                               
                                                       
                      <franz.alt@pfaffe        To:       
<plusplus@xxxxxxxxxxxxxxx>                                                    
                      nhofen.de>               cc:       "Franz Alt" 
<franz.alt@xxxxxxxxxxx>                                           
                                               Subject:  Uploading blobs, 
performance, some C++                                        
                      24/09/2002 16.15                                          
                                                       
                                                                                
                                                       
                                                                                
                                                       




oh, it's the BLOB thing again. don't wonder me writing so much postings,
I'm
working really hard on this. It's for my "diploma thesis" in civil
engineering...

I want to make an MySQL-Server based Application to simplify documentation
out of an air flow simulation. Therefore I have to handle text with linked
"media-objects". The upload should be under C++, the download via perl or
php (which I hope makes less trouble).

I tried out the example from
http://www.mysql.de/documentation/mysql++/4_Tutorial.html#SECTION03161000000

000000000

My system environment:
Win98, VisualC++6 introductory Version, 320MB, Celeron 526 or PII266,
mysql++V1.7.1 for VisualC++,  Mysql3.23.52, phpMyAdmin, Apache1.3...

There were several problems:
+ manipulator "escape" isn't working neither with ostrstream nor
stringstream type for "strbuf"
 ~ causes SQL-errors when "INSERTing"
-> I took the source from the C API of MySQL (happy to work with
OpenSource!)  "mysql_escape()" and adapted it.
+ ostrstream often caused an excetption wenn converted to string
("strbuf.str()")
+ the length of the string produced by ostrstream is totally wrong (522
instead of 9). ( Variable "l")
+when I want to #include <strstrea.h> , which is according to the online
help of visualC++ the suitable include file, then there are many errors
like:
null2.hh(7) : error C2872: 'ostream' : Mehrdeutiges Symbol   ~ "symbol with
multiple meanings / no unique meaning"
=> I just changed ostrstream to stringstream, which I'm more used to

? Is there a reason for using ostrstream instead of stringstream?

? Why does the manipulator "escape << " not work ?

I don't know if somebody wants to discuss ostrstreams here, but is there
anyone who got this example running without major changes?

Next, I did some performance-check and I found, that
  strbuf  << "INSERT INTO " << MY_TABLE << " (" << MY_FIELD << ") VALUES
(\""
<< fill  << "\")";
( 72 seconds )
is about 12 times slower than
int MMDPrintMedia::saveString(const std::string& TempString, std::ofstream&
AusgabeStream )
{    for (int i = 0; i<TempString.size();i++)  {
             AusgabeStream.put(TempString[i]);
        }
 return i;
}
( 5 seconds )
With 0,6 MB ascii-data.

reading from harddisc lasts about  1s  ~ 2,4s and escaping costs ~0,4s

For the really interested now the 2 Sources, first the escape() function,
then the adapted load_blob.cpp  from the manual and then my own ( using
another layer between mysql++ )

/////////
// escape()
/////////
// adapted from Sources of  mysql-C-API
void DatenbankMySQL::escape_blob (std::string& s) {
 if (!s.size()) return;
 std::string cS;
 const int howbig=s.size() ;
 cS.reserve(howbig*1.10);

 for (unsigned int i = 0;i<howbig;i++)
 {
    switch (s[i]) {
    case '\0':    /* Must be escaped for "mysql" */
    cS += "\\0";
      break;
    case '\\':
    cS += "\\\\";
      break;
    case '\"':
    cS += "\\\"";
      break;
    case '\'':    /* Better safe than sorry */
    cS += "\\'";
      break;
    /*
 case '\032':   // This gives problems on Win32
      s[i] = '\\';
      s.insert(++i,"Z",1);
      break;
   */
  default:
       cS += s[i];
   break;
    };
 }
 s.reserve(cS.size());
 s = cS;
}

/////////////////
// load_blob , load_file.cpp
///////////////
#include <sys/stat.h>
#include <iomanip>
#include <fstream>

// include for "ostrstream" ?
// #include <strstrea.h>

#include <string>
#include <iostream>
#include <sstream>
#include <conio.h>

// Benchmarking
// /*
#define _PERFORMANCE
#include <time.h>
#include <cstdlib>
#include <vector>
#include <algorithm>
// */

#include <mysql++>

extern int errno;

const char  MY_HOST[]="localhost";
const char  MY_USER[]="franz";
const char  MY_PASSWORD[]="";

const char  MY_DATABASE[]="blobtest";
const char  MY_TABLE[]="blobs";
const char  MY_FIELD[]="blob_data"; // BLOB field

namespace std {}     // !!
using namespace std;

void escape_blob(std::string& s);

int main(int argc, char *argv[]) {
#ifdef _DEBUG // compiled for "debugging"
  // std::string datei = "abc";
  // Zeilenparameter koenen in VisualC++ unter Projekt/Einstellungen/debug
angegeben werden!
  std::string datei = argv[1];
  cout << "debug: loading ... " << datei<< endl;
  // argc=2;
#endif

#ifndef _DEBUG  // compiled for "release"

 // Zeilenparameter koenen in VisualC++ unter Projekt/Einstellungen/debug
angegeben werden!
 std::string datei = argv[1];
 if (argc < 2) {
  cerr << "Usage : load_file full_file_path" << endl << endl;
  return -1;
 }
 cout << "release: loading ... " << datei<< endl;
#endif

 Connection con(use_exceptions);
 try {
  con.real_connect
(MY_DATABASE,MY_HOST,MY_USER,MY_PASSWORD,3306,(int)0,60,NULL);
  Query query = con.query();
  // std::ostrstream strbuf; // org
  std::stringstream strbuf; // working, but no manipulator "escape" is
working
  std::ifstream In (datei.c_str(),ios::in | ios::binary);
  struct stat for_len;
  if ((In.rdbuf())->is_open()) {
   if (stat (datei.c_str(),&for_len) == -1) return -1;
   unsigned int blen = for_len.st_size;  if (!blen) return -1;

#ifdef _PERFORMANCE
 // Performance Test
 clock_t Start, End;
 double Elapsed;
 Start = clock();
#endif

 char  *read_buffer = new char[blen];
 In.read(read_buffer,blen);
 std::string fill(read_buffer,blen);

#ifdef _PERFORMANCE
 End = clock();
 Elapsed = static_cast<double> ( End - Start ) / CLOCKS_PER_SEC ;
 cout << "\"load data file:\" " << Elapsed << "s" << endl;
 cout << "Length:"<< blen << endl;
 cout << "Bytes/second: " << blen / Elapsed << endl;

 // 2nd Performance Test
 Start = clock();
#endif
  // escapen mit globaler Funktion
  escape_blob (fill);
  strbuf  << "INSERT INTO " << MY_TABLE << " (" << MY_FIELD << ") VALUES
(\""
<< fill  << "\")";

  // Escapen mit manipulator
  // strbuf  << "INSERT INTO " << MY_TABLE << " (" << MY_FIELD << ")
VALUES(\""  << escape << fill  << "\")";

 // more temporary variables for debugging
 // /*
   file://strbuf.put(0); // don't know, found somewhere ...no effect
   std::string tmp_str = strbuf.str();  // to have variable in debugger
available
   // strbuf.rdbuf()->freeze(0); file://WITHOUT THIS, YOU HAVE A MEMORY
LEAK
in ostrstream
   int l = tmp_str.length();     //
   cout << endl << "Length SQL-query: " << l;
  //  cout << endl << "Query:" << tmp_str << endl;
   query.exec(tmp_str);
 // */
   // query.exec(strbuf.str());
   // strbuf.rdbuf()->freeze(0); file://WITHOUT THIS, YOU HAVE A MEMORY
LEAK
after strbuf.str()
   // or      // only with ostrstream
   // strbuf.freeze(false);
#ifdef _PERFORMANCE
 End = clock();
 Elapsed = static_cast<double> ( End - Start ) / CLOCKS_PER_SEC ;
 cout << "\"INSERT in MySQL:\" " << Elapsed << "s" << endl;
 cout << "Length:"<< blen << endl;
 cout << "Bytes/second: " << blen / Elapsed << endl;
#endif
   delete[] read_buffer;
   cout << "Ende gut alles gut ( task ok, press any key ) ";
#ifdef _DEBUG
   getch();   // press "anykey"
#endif
  }
  else
   cerr << "Your binary file " << datei.c_str() << "could not be open,
errno
= " << errno;
  return 0;
  } catch (BadQuery er)

    cerr << "Error: " << er.error << " " << con.errnum() << endl;
    return -1;
 }
 cout << "???";
    getch();
}


//////////////////////
// upload files in DB
/////////////////////

#include <stdlib.h>  #include <string> #include <sstream>  #include
<iostream> #include <fstream> #include <conio.h>

bool MMDPrintMedia::uploadMediaBinary()
{
 bool ok=true;   ifstream EingDatei;
 std::string readme = this->local_directory + this->filename;
 InputFile.open(readme.c_str(), ios::binary);
 if (!InputFile)
 {
   this->status = "InputFile con't be opened!.\n";
   return false;
 }
    InputFile.seekg(0,ios::end);
    int length = InputFile.tellg();
    InputFile.seekg(0, ios::beg);

 std::string str_blob;
 str_blob.reserve(length);   // ! Impotant for performance !

#ifdef _PERFORMANCE    // do the measurement
 // Performance Test
 clock_t Start, End;
 double Elapsed;
 Start = clock();
#endif

 int Anz = loadString(str_blob, EingDatei);
 EingDatei.close();

#ifdef _PERFORMANCE
 End = clock();
 Elapsed = static_cast<double> ( End - Start ) / CLOCKS_PER_SEC ;
 cout << "\"Load File:\" " << Elapsed << "s" << " & ";
 cout << "Length:"<< length << " & "; // " - Anz" << Anz << "Bytes" <<
endl;
 cout << "Bytes/second: " << length / Elapsed << endl;
#endif

#ifdef _PERFORMANCE
 Start = clock();
#endif

 DatenbankMySQL::escape_blob(str_blob);

#ifdef _PERFORMANCE
 End = clock();
 Elapsed = static_cast<double>( End - Start ) / CLOCKS_PER_SEC ;
 std::cout << "\"Escape:\" time " << Elapsed_esc << "s" << " & ";
 cout << "Bytes:"<< length << " - Anz" << Anz << " & ";
 cout << "Bytes/second: " << length / Elapsed << endl;
#endif

 // use strings instead of streams -> efficiency !?
 std::string& SQL_insertBLOB = str_blob;

 SQL_insertBLOB = "REPLACE INTO media_binaerdaten SET
media_binaerdaten.Daten='"+str_blob;
 SQL_insertBLOB += "', FKMedia_ID=" + int2string( this->IDinDB );

 ok = mysql->verbinden(pDB_prefs->DBName, pDB_prefs->DBHost,
pDB_prefs->DBUser, pDB_prefs->DBPassword );
 if(!ok) return false;

#ifdef _PERFORMANCE
 // Performance Test
 Start = clock();
#endif
  ok =mysql->ausfuehren(SQL_insertBLOB);   // is a simple layer-class
between this function and mysql++

#ifdef _PERFORMANCE
 End = clock();
 Elapsed = static_cast<double> ( End - Start ) / CLOCKS_PER_SEC ;
 cout << "Datei INSERT: " << Elapsed << "s" << endl;
 cout << "Length:"<< length << " - Anz" << Anz << "Bytes" << endl;
 cout << "Bytes/second: " << length / Elapsed ;
#endif

return ok;
}



--
franz.alt@xxxxxxxxxxxxxxx