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