* 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});
[download]

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.

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

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.

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.

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 :)

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};
}
[download]

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

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