Bug #55189 for SQL-Abstract: incomplete use of $self->_bindtype when passing an ARRAYREF or ARRAYREFREF value to a column and bindtypes => 'columns' is in use

On Wed Mar 03 12:55:41 2010, DMLOND wrote: Show quoted text
I am not entirely sure why you believe this is not correct behavior. You (or dbix-class or something else) requests a specific bindtype. It is only logical that the arrayrefref is expected to come with a matching bindtype. Please elaborate on your reasoning.

All I know is if you copy the provided code, which is pulled directly from the perldoc for SQL::Abstract 1.61 (so it should work IMHO), into a script, and run it, you get an error. It isnt DBIx class or DBI that is producing the error, as I am not even using these modules in the provided code example, it is pure SQL::Abstract. Here is the actual code in Abstract.pm where I see an inconsistency: ARRAYREF => sub { if ($self->{array_datatypes}) { # if array datatype are activated push @values, '?'; push @all_bind, $self->_bindtype($column, $v); } else { # else literal SQL with bind my ($sql, @bind) = @$v; $self->_assert_bindval_matches_bindtype(@bind); push @values, $sql; push @all_bind, @bind; } }, ARRAYREFREF => sub { # literal SQL with bind my ($sql, @bind) = @${$v}; $self->_assert_bindval_matches_bindtype(@bind); push @values, $sql; push @all_bind, @bind; }, # THINK : anything useful to do with a HASHREF ? HASHREF => sub { # (nothing, but old SQLA passed it through) #TODO in SQLA >= 2.0 it will die instead belch "HASH ref as bind value in insert is not supported"; push @values, '?'; push @all_bind, $self->_bindtype($column, $v); }, SCALARREF => sub { # literal SQL without bind push @values, $$v; }, SCALAR_or_UNDEF => sub { push @values, '?'; push @all_bind, $self->_bindtype($column, $v); }, }); What I see is that, for simple scalar key => value pairs, your dispatch table code (SCALAR_or_UNDEF) calls $self->_bindtype. If you look at the behavior of the provided example code, and follow it through the perl debugger, you can see that the ARRAYREFREF dispatch table code (and ARRAYREF when array_datatypes is false) is attempting to produce an inconsistent @bind array, where all of the key => value pairs for simple scalar values are producing the correct [ 'field' => value ] entries in @bind (as it is supposed to do based on the perldoc for bindtypes => 'column'), but the one with the ARRAYREFREF value is attempting to produce a @bind entry which is simply 'value'. If I add the @bind = map { $self->_bindtype($column, $_) } @bind; process before calling $self->_assert_bindval_matches_bindtype(@bind); not only does this assertion pass, but the returned value for @bindtype is in the correct [ 'field' => 'value' ] format required to iterate over @bind to call the bind_param on the prepared statement handle later on.

I think the source of the confusion is that the perldoc does not provide enough information to help users know how to use both 'bindtype => columns' and apply an oracle (or postgresql) function to a value as it is being inserted into the database. In order to use SQL::Abstract as it is currently distributed to do this, you can do the following (note, no 'bindtype => columns'): use SQL::Abstract; use Data::Dumper; my $sql = SQL::Abstract->new(); my %data = ( name => 'Jimbo Bobson', phone => '123-456-7890', address => '42 Sister Lane', city => 'St. Louis', state => 'Louisiana', blaz => ["to_date(?, 'MM/DD/YYYY')", "03/02/2003"] ); my($stmt, @bind) = $sql->insert('people', \%data); print "STMT ${stmt}\n"; print "BIND ".Dumper(@bind)."\n"; exit; But in order to do the same with 'bindtype => columns' you have to manually pass the [ 'column' => 'value' ] as the bind variable to be associated with the function, e.g. : use SQL::Abstract; use Data::Dumper; #my $sql = SQL::Abstract->new( bindtype => 'columns' ); my %data = ( name => 'Jimbo Bobson', phone => '123-456-7890', address => '42 Sister Lane', city => 'St. Louis', state => 'Louisiana', blaz => ["to_date(?, 'MM/DD/YYYY')", ['blaz' => "03/03/2003"] ); my($stmt, @bind) = $sql->insert('people', \%data); print "STMT ${stmt}\n"; print "BIND ".Dumper(@bind)."\n"; exit; Which, to me, is a bit unintuitive, and certainly doesnt behave the same way as inserting a simple scalar value without applying a function to it does when used in the context of bindtype => columns. It is also not clear from the perldocs that this is required, as it states to pass ['function(?)', $value ] as the value for the field in the hash passed into insert.

but [] doesnt work either when 'bindtypes => columns' is in use. It throws the exact same exception unless you manually pass in the ['field' => $value] instead of ['field' => $value]. I can see what you are trying to say, and maybe the docs and the code could be changed to better reflect this difference, e.g. if you pass in [] it should apply the $self->_bindtype function to each value in @bind, but if \[] it leaves stuff alone.

I still do not understand what you are trying to say. If you are saying that I should be able to do the following to apply an oracle function using bindtype => 'columns' (as the perldocs currently state I should be able to do): use SQL::Abstract; use Data::Dumper; my $sql = SQL::Abstract->new( bindtype => 'columns' ); my %data = ( name => 'Jimbo Bobson', phone => '123-456-7890', address => '42 Sister Lane', city => 'St. Louis', state => 'Louisiana', blaz => \["to_date(?, 'MM/DD/YYYY')", "03/02/2003"] ); my($stmt, @bind) = $sql->insert('people', \%data); print "STMT ${stmt}\n"; print "BIND ".Dumper(@bind)."\n"; exit; Then you are wrong. SQL::Abstract throws an exception. In order to get it to work, I have to do: use SQL::Abstract; use Data::Dumper; my $sql = SQL::Abstract->new( bindtype => 'columns' ); my %data = ( name => 'Jimbo Bobson', phone => '123-456-7890', address => '42 Sister Lane', city => 'St. Louis', state => 'Louisiana', blaz => \["to_date(?, 'MM/DD/YYYY')", ['blaz' => "03/02/2003"]] ); my($stmt, @bind) = $sql->insert('people', \%data); print "STMT ${stmt}\n"; print "BIND ".Dumper(@bind)."\n"; exit; Which is completely unintuitive, and not documented in the perldocs. I would be happy to provide a patch to the perldoc stating that this is the preferred way of applying a function using bindtype => 'columns' if that is what you are saying needs to be done. If you would prefer that the former code example work as-is whether or not bindtype => 'columns' is passed in the constructor (matching the current perldoc API), you will need to apply the patch that I supplied earlier to the code.

Closing bugreport - waiting on docpatch from submitter. Feel free to reopen at your convenience.