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
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
-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-
[perl5-dbi/dbi] 4fd6ec: Hardcoded version strings
Branch: refs/heads/master Home: https://github.com/perl5-dbi/dbi Commit: 4fd6ec60fbe40e9e6923e287ca8bdb83fcc633b0 https://github.com/perl5-dbi/dbi/commit/4fd6ec60fbe40e9e6923e287ca8bdb83fcc633b0 Author: H.Merijn Brand - Tux h.m.br...@xs4all.nl Date: 2013-06-03 (Mon, 03 Jun 2013) Changed paths: M Changes M dbiprof.PL M lib/Bundle/DBI.pm M lib/DBD/ExampleP.pm M lib/DBD/Gofer.pm M lib/DBD/Gofer/Policy/Base.pm M lib/DBD/Gofer/Policy/classic.pm M lib/DBD/Gofer/Policy/pedantic.pm M lib/DBD/Gofer/Policy/rush.pm M lib/DBD/Gofer/Transport/Base.pm M lib/DBD/Gofer/Transport/null.pm M lib/DBD/Gofer/Transport/pipeone.pm M lib/DBD/Gofer/Transport/stream.pm M lib/DBD/Multiplex.pm M lib/DBD/NullP.pm M lib/DBD/Sponge.pm M lib/DBI/Const/GetInfo/ANSI.pm M lib/DBI/Const/GetInfo/ODBC.pm M lib/DBI/Const/GetInfoReturn.pm M lib/DBI/Const/GetInfoType.pm M lib/DBI/DBD.pm M lib/DBI/DBD/Metadata.pm M lib/DBI/FAQ.pm M lib/DBI/Gofer/Execute.pm M lib/DBI/Gofer/Request.pm M lib/DBI/Gofer/Response.pm M lib/DBI/Gofer/Serializer/Base.pm M lib/DBI/Gofer/Serializer/DataDumper.pm M lib/DBI/Gofer/Serializer/Storable.pm M lib/DBI/Gofer/Transport/Base.pm M lib/DBI/Gofer/Transport/pipeone.pm M lib/DBI/Gofer/Transport/stream.pm M lib/DBI/Profile.pm M lib/DBI/ProfileData.pm M lib/DBI/ProfileDumper.pm M lib/DBI/ProfileDumper/Apache.pm M lib/DBI/ProfileSubs.pm M lib/DBI/PurePerl.pm M lib/DBI/SQL/Nano.pm M lib/DBI/Util/CacheMemory.pm M lib/DBI/Util/_accessor.pm Log Message: --- Hardcoded version strings Now that $Revision$ is useless in git, use hardcoded $VERSION strings, which are easier to update. Removed trailing whitespace from touched files
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.