List:MySQL++« Previous MessageNext Message »
From:Dan Cook Date:June 12 2009 10:28pm
Subject:Confused about Blobs and SSQLS ...
View as plain text  
I am trying to insert binary data into a table.  Per Warren's suggestion I
am modeling a BINARY(8) as a sql_blob object in SSQLS and I need to know if
I am doing something fundamentally wrong or if there is something weird with
the mapping between BINARY and sql_blob.

Here is the SQL script to create the table:

CREATE  TABLE IF NOT EXISTS `mydb`.`test` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `binary_data` BINARY(8) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

Note the BINARY(8) field can be NULL.

Here is a very simple program to populate the table:

#include <iostream>
#include <iomanip>
#include <string>

#include <mysql++.h>
#include <ssqls.h>

using namespace std;
using namespace mysqlpp;

sql_create_2(test,1,2,
    sql_int_unsigned, id,
    Null<sql_blob>, binary_data)

int
main(int argc, char *argv[])
{
   char dataChar[8] = { 0x1, 0x2, 0x0, 0x3, 0x4, 0x5, 0x6, 0x7 };
   std::string data_str(dataChar, sizeof(dataChar));

   test testDB;
   testDB.id = 0;
   testDB.binary_data = mysqlpp::null;

   // Manipulate the data and is_null fields to load the binary data.
   cerr << "Binary data IsNull: " << testDB.binary_data.is_null << endl;
   testDB.binary_data.data.assign(data_str.c_str(), data_str.length());
   testDB.binary_data.is_null = false;
   cerr << "Binary data IsNull: " << testDB.binary_data.is_null << endl;

    try {
        mysqlpp::Connection con("mydb",
"/usr/BWhttpd/mysql/data/mysql.sock", "root", "");
        mysqlpp::Query query = con.query();
        query.insert(testDB);
        cerr << query << endl;
        query.execute();
    }
    catch (const mysqlpp::BadQuery& er) {
        // Handle any query errors
        cerr << "Query error: " << er.what() << endl;
        return -1;
    }
    catch (const mysqlpp::BadConversion& er) {
        // Handle bad conversions
        cerr << "Conversion error: " << er.what() << endl <<
                "\tretrieved data size: " << er.retrieved <<
                ", actual size: " << er.actual_size << endl;
        return -1;
    }
    catch (const mysqlpp::Exception& er) {
        // Catch-all for any other MySQL++ exceptions
        cerr << "Error: " << er.what() << endl;
        return -1;
    }

    return 0;
}

Here is the output from a test run:
./binTest
Binary data IsNull: 1
Binary data IsNull: 0
INSERT INTO test (id,binary_data) VALUES (0,'\0')
	
As you can see the binary data does not get inserted into the table.   I was
expecting to see something like:
INSERT INTO test (id,binary_data) VALUES (0,'\1\2\0\3\4\5\6\7').  

A couple of notes about the program:
I don't know if it is bad form to manipulate the "data" and "is_null" fields
of the Null<sql_blob> object to assign the binary data to the underlying
mysqlpp::String object.

Can someone please tell me what I am doing wrong because it is not obvious
to me.

Regards,
Dan


Thread
Confused about Blobs and SSQLS ...Dan Cook13 Jun
  • Re: Confused about Blobs and SSQLS ...Warren Young16 Jun