Re: problem with bound columns and fetchall_arrayref with a slice

On 03/06/13 11:43, John Scoles wrote:
> Hmm you always come up with the head scratchers,
>
> You are most likely on the right track.  Somewhere way in the back of
> my mind I recall that the fetchall_arrayref  was optimized for speed,
> and so the binding was left out but I am going back almost 10 years
> by memory.
>
>
> Do the other DBD do the same thing??

Only DBD::Oracle and DBD::ODBC support DiscardString as I added it to both of them.
Very few DBDs even have a bind_col entry point.

DBD::ODBC does not allow a column bound with a type to have that type changed but does allow type=0 through even if the type was previously set. DBD::ODBC suffers from the same issue wrt attributes - it sets them to 0 on every call to bind_col.

Martin

> Cheers John
>
>> Date: Mon, 3 Jun 2013 10:43:20 +0100 From: [email protected] To:
>> [email protected] Subject: problem with bound columns and
>> fetchall_arrayref with a slice
>>
>> Hi,
>>
>> I've just hit a problem with bind_col and fetchall_arrayref when a
>> slice is used and I'm wondering how I might fix it. I'm using
>> DBD::Oracle and setting a bind type and some attributes but as soon
>> as a slice is used in fetchall_arrayref, DBI rebinds the columns
>> and I lose the column type and attribute. Here is an example:
>>
>> # $sth is just a select with 4 column # the first column is an
>> integer and we want to keep it that way # as the result will be
>> JSONified and we don't want JSON to think it # is a string and put
>> quotes around it $sth->bind_col (1, undef, {TYPE => SQL_INTEGER,
>> DiscardString => 1});
>>
>> my $list = $sth->fetchall_arrayref({}); print Dumper ($list);
>>
>> Without the slice it produces:
>>
>> $VAR1 = [ [ 11, 'Abandoned', '1358247475.860400',
>> '1358247475.860400' ],
>>
>> and with the slice it produces:
>>
>> $VAR1 = [ { 'modified_date_time' => '1358247475.860400',
>> 'market_status_id' => '11', 'name' => 'Abandoned',
>> 'created_date_time' => '1358247475.860400' },
>>
>> Notice the slice caused the market_status_id to look like a string.
>> This happens because DBI binds the columns when you use a slice and
>> it is overriding what was set for column 1 in the bind this code
>> does.
>>
>> So this is how bind_col ends up being called:
>>
>> BIND COL 1 (TYPE => SQL_INTEGER, DiscardString => 1) BIND COL 1 (no
>> type (i.e. type = 0) and no attrs) BIND COL 2 (no type and no
>> attrs) BIND COL 3 (no type and no attrs) BIND COL 4 (no type and no
>> attrs)
>>
>> The code in DBD::Oracle is possibly flawed in that every time
>> bind_col is called it does:
>>
>> imp_sth->fbh[field-1].req_type = type;
>> imp_sth->fbh[field-1].bind_flags = 0; /* default to none */
>>
>> regardless of whether bind_col has been called before and set a
>> type or attributes. As type is a parameter to dbd_st_bind_col
>> anyone not wishing to set a type has to say 0.
>>
>> I could fix my usage case by simply saying if bind_col has been
>> called for a column which already has a type set and the incoming
>> type is 0 don't touch it and if no attributes are passed don't
>> clear any existing ones. It would work for me but I'd like to hear
>> any comments.
>>
>> Martin