Re: problem with bound columns and fetchall_arrayref with a slice
On Fri, Jun 07, 2013 at 08:29:42AM +0100, Martin J. Evans wrote: > On 04/06/13 06:22, Tim Bunce wrote: > > > >I see the docs don't spell it out but I've always intended the bind_col > >type parameter to be 'sticky' - i.e. a missing or undef value wouldn't > >undo previous type settings. > > > >Feel free to patch the docs to clarify that. > > I have updated the bind_col pod in DBI. > > I've released a new DBD::ODBC which fixes the sticky issue (in DBD::ODBC TYPE > was ok but attributes were not). > > I've sent a pull request to Yanick to fix stickiness in DBD::Oracle. Perfect. Thanks Martin! Tim.
Re: problem with bound columns and fetchall_arrayref with a slice
On 04/06/13 06:22, Tim Bunce wrote: On Mon, Jun 03, 2013 at 10:43:20AM +0100, Martin J. Evans wrote: 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: 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. I see the docs don't spell it out but I've always intended the bind_col type parameter to be 'sticky' - i.e. a missing or undef value wouldn't undo previous type settings. Feel free to patch the docs to clarify that. Tim. I have updated the bind_col pod in DBI. I've released a new DBD::ODBC which fixes the sticky issue (in DBD::ODBC TYPE was ok but attributes were not). I've sent a pull request to Yanick to fix stickiness in DBD::Oracle. Martin
Re: problem with bound columns and fetchall_arrayref with a slice
On Mon, Jun 03, 2013 at 10:43:20AM +0100, Martin J. Evans wrote: > 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: > 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. I see the docs don't spell it out but I've always intended the bind_col type parameter to be 'sticky' - i.e. a missing or undef value wouldn't undo previous type settings. Feel free to patch the docs to clarify that. Tim.
Re: problem with bound columns and fetchall_arrayref with a slice
On 03/06/13 15:31, Greg Sabino Mullane wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
Martin J. Evans wrote:
# $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});
Why doesn't the driver simply do sv_setiv unconditionally if the
column is an integer?
It was a lot more complicated than the simple case I've described here. We
added http://search.cpan.org/~timb/DBI-1.627/DBI.pm#sql_type_cast too and
strict flag.
Some drivers don't always know something is an integer, some integers in the
database can be bigger than what will fit in a perl scalar (but they will fit
in a string), some people have integers in varchar columns but they know they
are integers... my memory is not good enough to remember all the other reasons.
Martin
Re: problem with bound columns and fetchall_arrayref with a slice
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
Martin J. Evans wrote:
> # $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});
Why doesn't the driver simply do sv_setiv unconditionally if the
column is an integer?
- --
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201306031030
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iEYEAREDAAYFAlGsqJIACgkQvJuQZxSWSsjD9QCfc62Pb91oXdpVlinKHkg4YwbL
1R0AoLQTB42qalgZVjCv2T/kCbmOGW6k
=58/n
-END PGP SIGNATURE-
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: 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
RE: problem with bound columns and fetchall_arrayref with a slice
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
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
