Updating/inserting Binary data using DBIx::Class

[hrr]

Dear Monks:

I use DBIx::Class to access a DB2 database with a VARCHAR column containing binary data.

If I do not specify a data type, the DBD driver reduces character codes larger than 127 to a smaller value. To prevent this, when using DBI directly, I can update/insert binary data via

$sth->bind_param(1, $binaryData, {TYPE => DBI::SQL_BINARY} );

Now, how can set the proper type for writing binary data using DBIx::Class? I tried using

__PACKAGE__->add_columns(col_name => {data_type => DBI::SQL_BINARY});

however, this did not work...

Please let me know about your solution to this problem!

Thank you,
Hansres

[jesuashok]

Hi hrr

* what version of DB2 you are using ?

Note: Binary Data type will be supported from DB2 version 9.

however, this did not work...
are you getting any error message ?

__PACKAGE__->add_columns(col_name => {data_type => DBI::SQL_BINARY});

Tell something about the above statement's result ? In what OS you are running the above program ?

[hrr]

Hi jesuashok:

Thank you for thinking about my problem! More details are as follows.
• DB2 Version 8.1.2. (It seems that I can insert binary strings using this DB2 version, since bind_param(... {TYPE => DBI::SQL_BINARY}) works well.)
• OS: Linux, openSUSE 10.1.
• There is no error message. However, character codes larger thatn 127 are reduced to a smaller value. For example, chr(0xe3) is changed to chr(0x1a). I find out the latter code by using a SELECT ASCII(col_name) FROM ... query.

[hrr]

After browsing through the DBIx::Class source code, it seems to me that DBIx::Class is less flexible that I hoped it to be... maybe it is not possible / very difficult to do what I originally intended.

Concretely, DBIx::Class::Storage::DBI handles insert/update statements, using

  sub _execute {
     ...
     my ($sql, @bind) = $self->sql_maker->$op($ident, @args);
     ...
     my $sth = $self->sth($sql,$op);
     ...
     $rv = $sth->execute(@bind)
     ...
  }

In particular, this means that because the bind parameters are passed to execute as array, no bind attributes can be specified! In this case, according to the documentation of DBI:
If any arguments are given, then execute will effectively call "bind_param" for each value before executing the statement. Values bound in this way are usually treated as SQL_VARCHAR types unless the driver can determine the correct type (which is rare), or unless bind_param (or bind_param_inout) has already been used to specify the type.

It would be possible to define the data type prior to executing the statement, because according to the documentation of DBI::bind_param(..., \%attr), the whole \%attr parameter is 'sticky' in the sense that a driver only needs to consider the \%attr parameter for the first call, for a given $sth and parameter.
However, even when using bind_param, this would still would still require changes to DBIx::Class in some form.

[siracusa]

Yes, this is a common issue with binary column data and DBI. I recently added support for explicit bind_param() calls to Rose::DB::Object in order to support Postgres's BYTEA column type. (Unfortunately, Rose::DB::Object does not currently support DB2.)

Calling bind_param() explicitly incurs a small performance hit, since it must be called on every value in a given query, even if it's only strictly needed on just one. Although the DBI docs say that calling execute() with arguments calls bind_param() on every value anyway, my testing has shown that there is a difference between allowing DBI to do it and doing it manually.

The compromise I came up with in Rose::DB::Object is to explicitly call bind_param() only if one or more columns in a particular table requires it. This eliminates the performance hit for the common case of tables without any binary columns.

[hrr]

It is very interesting that you identify this as a common issue with binary column data! That, after a rigorous analysis, you were able to come up with an elegant compromise, raises my hopes that an implementation in DBIx::Class is not too far away :)

[rafl]

I know this solution is quite hacky, but I don't really have another idea right now. Try to add the following accessor to your resultsource class

sub col_name {
    my $self = shift;

    if (@_) {
        my $data = shift;
        my $sth = $self->result_source->schema->storage->dbh->prepare(
+"update table set col_name = ? where primary_key = ?");
        $sth->bind_param(1, $data, {TYPE => DBI::SQL_BINARY});
        $sth->bind_param(2, $self->primary_key);
        $sth->execute;
    }

    my $sth = $self->result_source->schema->storage->dbh->prepare("sel
+ect col_name from table where primary_key = ?");
    $sth->execute( $self->primary_key );

    my $result = $sth->fetchrow_hashref;
    return $result->{col_name};
}

I know that this piece of code somehow undermines the principles of DBIx::Class because the resultsource already knows what the table name is and how the primary_key is called. You may be able to change the hard-coded SQL queries to something smarter or you will probably also be able to use SQL::Abstract, which DBIx::Class uses internal as well, to build that query, but I didn't bother with that.

Also note that the code is completely untested.

Cheers, Flo

[hrr]

Great, thank you very much for this hack! I will try it out and report about its results here.

[hrr]

One very simple solution might be to declare the columns as VARCHAR FOR BIT DATA. Preliminary tests show that in this case, I can store binary strings without explicitly specifying SQL_BINARY!

Update: After testing for a few weeks, I conclude that this solution works well for me. The only non-trivial part was to convert the VARCHAR datatype of the existing columns to VARCHAR FOR BIT DATA, as this requires dropping and re-creating the corresponding tables (as well as all related objects such as indexes and foreign keys). DB2 control center "db2cc" offers to do all that for you; however, be aware that it sometimes gets stuck in the middle of this conversion process---I highly recommend that you make a backup of your database before attempting the conversion.