| Subject: | glitches with using binary (bytea) columns in PG driver |
When using PGSQL, SQLEngine fails to handle binary (bytea) columns correctly. The problem is not entirely SQLEngine's fault; a lot of it has to do with DBD::Pg itself. There has been a bit of a debate as to whether this problem is a bug, feature, or limitation in DBD::Pg; my opinion is that it is a BUG, because it's really annoying and behaves differently than every other DBD driver out there.
The problem is that binary columns have to specifically be bound as such in each SQL query in order for them to be escaped properly. The query will fail with "DBD::Pg::st execute failed: ERROR: invalid input syntax for type bytea" if a binary column is changed as part of it.
The patch I have attached builds off of the patch submitted in change 13177 :
- sql_update is now invoked with a ColumnSet object instead of an array of columns to update.
- when this information is available, sql_update calls a new method, "set_bind_param" on each bound parameter, using the result of that method as the actual bound parameter.
- in the PG driver, the set_bind_param method checks to see if we are dealing with a binary column, and if so, sets the appropriate pg_type attribute so that the DBD::Pg driver escapes the data properly.
There may be a cleaner way of doing this, I'm not sure... I tried 5 or 6 different approaches in SQLEngine and this is the one that seemed to work best with the least amount of extra code.
Thanks,
Tyler
p4 edit //depot/main/Appliance/src/pmx/cpan-packages/DBIx-SQLEngine/SQLEngine/Driver.pm
Index: SQLEngine/Driver.pm
--- SQLEngine/Driver.pm.~1~ Thu Jun 9 17:05:27 2005
+++ SQLEngine/Driver.pm Thu Jun 9 17:05:27 2005
@@ -1622,7 +1622,11 @@
}
} elsif ( ! ref($v) ) {
push @v_literals, '?';
- push @v_params, $v;
+ if($c) {
+ push @v_params, $self->set_bind_param($c, $v);
+ } else {
+ push @v_params, $v;
+ }
} elsif ( ref($v) eq 'SCALAR' ) {
push @v_literals, $$v;
} else {
@@ -1643,6 +1647,11 @@
return( $sql, @params );
}
+sub set_bind_param {
+ my($self, $c, $v) = @_;
+ return $v;
+}
+
########################################################################
=pod
@@ -1879,14 +1888,15 @@
if ( ! $columns and UNIVERSAL::isa( $clauses{'values'}, 'HASH' ) ) {
$columns = $clauses{'values'}
}
- my @columns;
+ my(@columns, @columnso);
if ( ! $columns or $columns eq '*' ) {
croak("Column names are missing or empty");
} elsif ( ! ref( $columns ) and length( $columns ) ) {
# should be one or more comma-separated column names
@columns = split /,\s?/, $columns;
- } elsif ( UNIVERSAL::can($columns, 'column_names') ) {
+ } elsif ( UNIVERSAL::can($columns, 'columns') ) {
@columns = $columns->column_names;
+ @columnso = $columns->columns;
} elsif ( ref($columns) eq 'HASH' ) {
@columns = sort keys %$columns;
} elsif ( ref($columns) eq 'ARRAY' ) {
@@ -1911,12 +1921,19 @@
( scalar @value_args ) or croak("Values are missing or empty");
my @values;
my @v_params;
+
foreach my $v ( @value_args ) {
+ my $c = shift(@columnso);
+
if ( ! defined($v) ) {
push @values, 'NULL';
} elsif ( ! ref($v) ) {
push @values, '?';
- push @v_params, $v;
+ if($c) {
+ push @v_params, $self->set_bind_param($c, $v);
+ } else {
+ push @v_params, $v;
+ }
} elsif ( ref($v) eq 'SCALAR' ) {
push @values, $$v;
} else {
p4 edit //depot/main/Appliance/src/pmx/cpan-packages/DBIx-SQLEngine/SQLEngine/Driver/Pg.pm
Index: SQLEngine/Driver/Pg.pm
--- SQLEngine/Driver/Pg.pm.~1~ Thu Jun 9 17:05:27 2005
+++ SQLEngine/Driver/Pg.pm Thu Jun 9 17:05:27 2005
@@ -129,6 +129,22 @@
'field number \d+ is out of range 0\.\.\-1',
}
+sub set_bind_param {
+ my($self, $c, $v) = @_;
+
+ if($c->type eq 'binary') {
+ if(!ref($v)) {
+ $v = [ $v ];
+ }
+ if(!UNIVERSAL::isa($v->[1], 'HASH')) {
+ $v->[1] = {};
+ }
+ $v->[1]->{pg_type} ||= DBD::Pg::PG_BYTEA;
+ }
+
+ $v;
+}
+
########################################################################
=head1 SEE ALSO
p4 edit //depot/main/Appliance/src/pmx/cpan-packages/DBIx-SQLEngine/SQLEngine/Schema/Table.pm
Index: SQLEngine/Schema/Table.pm
--- SQLEngine/Schema/Table.pm.~1~ Thu Jun 9 17:05:27 2005
+++ SQLEngine/Schema/Table.pm Thu Jun 9 17:05:27 2005
@@ -470,8 +470,22 @@
sub update_row {
my($self, $row) = @_;
+ my @columns_in;
+ if(UNIVERSAL::can($row, 'field_columns')) {
+ @columns_in =
+ map { DBIx::SQLEngine::Schema::Column->new(name => $_->{name}, type => $_->{type}) } ($row->field_columns);
+ } else {
+ @columns_in = $self->columns;
+ };
+
+ my $primary = $self->column_primary_name;
+ my @columns = grep { $_->name eq $primary or defined $row->{$_->name} }
+ @columns_in;
+
+ my $columns = DBIx::SQLEngine::Schema::ColumnSet->new(@columns);
+
$self->sqlengine_do('do_update',
- columns => [ $self->column_names ],
+ columns => $columns,
where => $self->primary_criteria( $row ),
values => $row,
);
End of Patch.