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??
Cheers
John
> Date: Mon, 3 Jun 2013 10:43:20 +0100
> From: boh...@ntlworld.com
> To: dbi-dev@perl.org
> 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
