Re: Strange bind_param issue
On 15/08/12 17:39, Tim Bunce wrote: On Wed, Aug 15, 2012 at 04:14:52PM +0100, Martin J. Evans wrote: I've just been given an rt https://rt.cpan.org/Ticket/Display.html?id=78838 and am at a loss to explain exactly what is happening. I wonder if anyone can help? Some background: DBI says for bind_param: "The bind_param method takes a copy of $bind_value and associates it (binds it) with a placeholder" As far as I am aware DBD::ODBC does not copy the scalar given to it - so perhaps DBI does this. The problem I'm seeing in the provided example is the pointer passed to ODBC's SQLBindParameter at the time bind_param is called no longer points to a valid string when execute is called. However, changing the call to bind_param to pass $obj as "$obj" appears to fix the problem. The driver should take a copy of the value when bind_param is called. Perhaps stored by the ParamValues attribute. (bind_param_inout takes a reference instead and binds at execute.) As pointed out in another reponse DBD::ODBC was taking a copy after all but when I dug deeper into this it has opened a bag of worms. 1. DBD::Oracle seems to work with the test code because it does: if (SvAMAGIC(phs->sv)) /* overloaded. XXX hack, logic ought to be pushed deeper */ sv_pvn_force(phs->sv, &PL_na); This change also fixes DBD::ODBC in this case. I'd be interested in comments on this. 2. The logic in DBD::ODBC when a sql_type was passed meant you could change an in type to out type or increase the size of an output bound param after the first bind without it being caught. Fixed. 3. Without (1 above) things got a little silly because by only changing the example code to add Dumper($sth->{ParamValues}) before execute it made it work - I'd guess because that causes SvPV to be called on the bound parameters - oops. Thanks for the pointers. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
Re: Strange bind_param issue
On 16/08/12 16:37, Martin J. Evans wrote: On 16/08/12 16:13, Charles Jardine wrote: I thought it might be interesting to see what DBD::Oracle does. I ran use strict; use DBI; my $dbh = DBI->connect("dbi:Oracle:", '', ''); my $obj = new Object(); my $sql = q(SELECT ? AS result from dual); my $sth = $dbh->prepare($sql); $sth->bind_param(1, $obj); $sth->execute(); while (my $row = $sth->fetchrow_hashref()) { print $row->{'RESULT'}, "\n"; } package Object; use overload '""' => 'to_s'; sub new() { bless { }, shift }; sub to_s() { my $self = shift; ref($self); } and it printed Object Because DBD::Oracle does not bind the parameters until execute time whereas DBD::ODBC binds them at bind_param time. If I change DBD::ODBC to bind at execute time (there is an internal flag defer_bind which is no longer used) I get the same result. I'm trying to understand why this makes a difference here. The following test re-blesses $obj between the bind and the execute. It still prints 'Object' even though, at the time of the execute, $obj is of class Subject. This demonstrates that DBD::Oracle does not just copy the reference value it has been given. If must do the actual stringifcation as bind time. use strict; use DBI; my $obj = Object->new; my $dbh = DBI->connect('dbi:Oracle:', '', ''); my $sth = $dbh->prepare("select ? from dual"); $sth->bind_param(1, $obj); bless $obj, 'Subject'; $sth->execute; print scalar $sth->fetchrow_array(), "\n"; package Object; use overload '""' => 'to_s'; sub new() { bless { }, shift }; sub to_s() { my $self = shift; ref($self); } package Subject; use overload '""' => 'to_s'; sub new() { bless { }, shift }; sub to_s() { my $self = shift; ref($self); } -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Strange bind_param issue
On 16/08/12 16:13, Charles Jardine wrote: On 16/08/12 14:00, Martin J. Evans wrote: On 15/08/12 17:39, Tim Bunce wrote: On Wed, Aug 15, 2012 at 04:14:52PM +0100, Martin J. Evans wrote: I've just been given an rt https://rt.cpan.org/Ticket/Display.html?id=78838 and am at a loss to explain exactly what is happening. I wonder if anyone can help? Some background: DBI says for bind_param: "The bind_param method takes a copy of $bind_value and associates it (binds it) with a placeholder" As far as I am aware DBD::ODBC does not copy the scalar given to it - so perhaps DBI does this. The problem I'm seeing in the provided example is the pointer passed to ODBC's SQLBindParameter at the time bind_param is called no longer points to a valid string when execute is called. However, changing the call to bind_param to pass $obj as "$obj" appears to fix the problem. The driver should take a copy of the value when bind_param is called. Perhaps stored by the ParamValues attribute. (bind_param_inout takes a reference instead and binds at execute.) Can anyone say if DBD::ODBC should work with either example and explain what might be happening here: use DBI; my $dbh = DBI->connect("dbi:ODBC:DSN=xxx;UID=xx;PWD=yy;MARS_Connection=No;"); my $obj = new Object(); my $sql = q(SELECT ? AS result); my $sth = $dbh->prepare($sql); # interesting - is the following use of $obj really as a string? # in the call to bind_param here, DBD::ODBC binds the pv ptr of the scalar # it is given in a SQLBindParameter call but by the time execute is called # the string at that address is no longer valid. I kind of expect that as # what to_s returns is only required in the bind_param statement and yet # DBI says "bind_param takes a copy". # However if the following is changed to "$obj" it works $sth->bind_param(1, $obj); $sth->execute(); while (my $row = $sth->fetchrow_hashref()) { print $row->{'result'}, "\n"; } package Object; use overload '""' => 'to_s'; sub new() { bless { }, shift }; sub to_s() { my $self = shift; ref($self); } The driver needs to make a copy and ensure that 'get magic' is called. SvPV will call get magic but won't make a copy. Since you need an SV for the ParamValues attribute maybe newSVsv() followed by SvPOK_only_UTF8() on the copy would be a suitable incantation. Tim. It appears dbd_bind_ph does this: /* newvalue is the scalar param passed in to dbd_bind_ph */ /* phs is a structure to hold the param details */ if (!is_inout) {/* normal bind to take a (new) copy of current value */ if (phs->sv == &PL_sv_undef) /* (first time bind) */ phs->sv = newSV(0); sv_setsv(phs->sv, newvalue); } else if (newvalue != phs->sv) { if (phs->sv) SvREFCNT_dec(phs->sv); phs->sv = SvREFCNT_inc(newvalue); /* point to live var */ } so as far as I can see a copy is taken but I don't think sv_setsv does any magic. Similar code exists in DBD::Oracle and a number of other DBDs I looked at. DBD::ODBC does use SvPV later so I'm guessing that is where the magic happens and the scalar is stringified. This made me wonder what happens if you pass a reference without any AMAGIC and DBD::ODBC allowed that but you got rubbish so I've now disallowed references without AMAGIC. So now I'm only not sure why in the example provided what passing $obj (and not "$obj") is doing. it appears when it gets the sv_setsv above SvPV on phs->sv is 'Object' and it is address passed to SQLBindParameter but later when execute is called the contents of that address have changed. It seems every time you call SvPV on the copied scalar which is the object you get a different address and because DBD::ODBC binds at bind time and not execute time by execute time the original address has changed. I thought it might be interesting to see what DBD::Oracle does. I ran use strict; use DBI; my $dbh = DBI->connect("dbi:Oracle:", '', ''); my $obj = new Object(); my $sql = q(SELECT ? AS result from dual); my $sth = $dbh->prepare($sql); $sth->bind_param(1, $obj); $sth->execute(); while (my $row = $sth->fetchrow_hashref()) { print $row->{'RESULT'}, "\n"; } package Object; use overload '""' => 'to_s'; sub new() { bless { }, shift }; sub to_s() { my $self = shift; ref($self); } and it printed Object Because DBD::Oracle does not bind the parameters until execute time whereas DBD::ODBC binds them at bind_param time. If I change DBD::ODBC to bind at execute time (there is an internal flag defer_bind which is no longer used) I get the same result. I'm trying to understand why this makes a difference here. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
Re: Strange bind_param issue
On 16/08/12 14:00, Martin J. Evans wrote: On 15/08/12 17:39, Tim Bunce wrote: On Wed, Aug 15, 2012 at 04:14:52PM +0100, Martin J. Evans wrote: I've just been given an rt https://rt.cpan.org/Ticket/Display.html?id=78838 and am at a loss to explain exactly what is happening. I wonder if anyone can help? Some background: DBI says for bind_param: "The bind_param method takes a copy of $bind_value and associates it (binds it) with a placeholder" As far as I am aware DBD::ODBC does not copy the scalar given to it - so perhaps DBI does this. The problem I'm seeing in the provided example is the pointer passed to ODBC's SQLBindParameter at the time bind_param is called no longer points to a valid string when execute is called. However, changing the call to bind_param to pass $obj as "$obj" appears to fix the problem. The driver should take a copy of the value when bind_param is called. Perhaps stored by the ParamValues attribute. (bind_param_inout takes a reference instead and binds at execute.) Can anyone say if DBD::ODBC should work with either example and explain what might be happening here: use DBI; my $dbh = DBI->connect("dbi:ODBC:DSN=xxx;UID=xx;PWD=yy;MARS_Connection=No;"); my $obj = new Object(); my $sql = q(SELECT ? AS result); my $sth = $dbh->prepare($sql); # interesting - is the following use of $obj really as a string? # in the call to bind_param here, DBD::ODBC binds the pv ptr of the scalar # it is given in a SQLBindParameter call but by the time execute is called # the string at that address is no longer valid. I kind of expect that as # what to_s returns is only required in the bind_param statement and yet # DBI says "bind_param takes a copy". # However if the following is changed to "$obj" it works $sth->bind_param(1, $obj); $sth->execute(); while (my $row = $sth->fetchrow_hashref()) { print $row->{'result'}, "\n"; } package Object; use overload '""' => 'to_s'; sub new() { bless { }, shift }; sub to_s() { my $self = shift; ref($self); } The driver needs to make a copy and ensure that 'get magic' is called. SvPV will call get magic but won't make a copy. Since you need an SV for the ParamValues attribute maybe newSVsv() followed by SvPOK_only_UTF8() on the copy would be a suitable incantation. Tim. It appears dbd_bind_ph does this: /* newvalue is the scalar param passed in to dbd_bind_ph */ /* phs is a structure to hold the param details */ if (!is_inout) {/* normal bind to take a (new) copy of current value */ if (phs->sv == &PL_sv_undef) /* (first time bind) */ phs->sv = newSV(0); sv_setsv(phs->sv, newvalue); } else if (newvalue != phs->sv) { if (phs->sv) SvREFCNT_dec(phs->sv); phs->sv = SvREFCNT_inc(newvalue); /* point to live var */ } so as far as I can see a copy is taken but I don't think sv_setsv does any magic. Similar code exists in DBD::Oracle and a number of other DBDs I looked at. DBD::ODBC does use SvPV later so I'm guessing that is where the magic happens and the scalar is stringified. This made me wonder what happens if you pass a reference without any AMAGIC and DBD::ODBC allowed that but you got rubbish so I've now disallowed references without AMAGIC. So now I'm only not sure why in the example provided what passing $obj (and not "$obj") is doing. it appears when it gets the sv_setsv above SvPV on phs->sv is 'Object' and it is address passed to SQLBindParameter but later when execute is called the contents of that address have changed. It seems every time you call SvPV on the copied scalar which is the object you get a different address and because DBD::ODBC binds at bind time and not execute time by execute time the original address has changed. I thought it might be interesting to see what DBD::Oracle does. I ran use strict; use DBI; my $dbh = DBI->connect("dbi:Oracle:", '', ''); my $obj = new Object(); my $sql = q(SELECT ? AS result from dual); my $sth = $dbh->prepare($sql); $sth->bind_param(1, $obj); $sth->execute(); while (my $row = $sth->fetchrow_hashref()) { print $row->{'RESULT'}, "\n"; } package Object; use overload '""' => 'to_s'; sub new() { bless { }, shift }; sub to_s() { my $self = shift; ref($self); } and it printed Object -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Strange bind_param issue
On 15/08/12 17:39, Tim Bunce wrote: On Wed, Aug 15, 2012 at 04:14:52PM +0100, Martin J. Evans wrote: I've just been given an rt https://rt.cpan.org/Ticket/Display.html?id=78838 and am at a loss to explain exactly what is happening. I wonder if anyone can help? Some background: DBI says for bind_param: "The bind_param method takes a copy of $bind_value and associates it (binds it) with a placeholder" As far as I am aware DBD::ODBC does not copy the scalar given to it - so perhaps DBI does this. The problem I'm seeing in the provided example is the pointer passed to ODBC's SQLBindParameter at the time bind_param is called no longer points to a valid string when execute is called. However, changing the call to bind_param to pass $obj as "$obj" appears to fix the problem. The driver should take a copy of the value when bind_param is called. Perhaps stored by the ParamValues attribute. (bind_param_inout takes a reference instead and binds at execute.) Can anyone say if DBD::ODBC should work with either example and explain what might be happening here: use DBI; my $dbh = DBI->connect("dbi:ODBC:DSN=xxx;UID=xx;PWD=yy;MARS_Connection=No;"); my $obj = new Object(); my $sql = q(SELECT ? AS result); my $sth = $dbh->prepare($sql); # interesting - is the following use of $obj really as a string? # in the call to bind_param here, DBD::ODBC binds the pv ptr of the scalar # it is given in a SQLBindParameter call but by the time execute is called # the string at that address is no longer valid. I kind of expect that as # what to_s returns is only required in the bind_param statement and yet # DBI says "bind_param takes a copy". # However if the following is changed to "$obj" it works $sth->bind_param(1, $obj); $sth->execute(); while (my $row = $sth->fetchrow_hashref()) { print $row->{'result'}, "\n"; } package Object; use overload '""' => 'to_s'; sub new() { bless { }, shift }; sub to_s() { my $self = shift; ref($self); } The driver needs to make a copy and ensure that 'get magic' is called. SvPV will call get magic but won't make a copy. Since you need an SV for the ParamValues attribute maybe newSVsv() followed by SvPOK_only_UTF8() on the copy would be a suitable incantation. Tim. It appears dbd_bind_ph does this: /* newvalue is the scalar param passed in to dbd_bind_ph */ /* phs is a structure to hold the param details */ if (!is_inout) {/* normal bind to take a (new) copy of current value */ if (phs->sv == &PL_sv_undef) /* (first time bind) */ phs->sv = newSV(0); sv_setsv(phs->sv, newvalue); } else if (newvalue != phs->sv) { if (phs->sv) SvREFCNT_dec(phs->sv); phs->sv = SvREFCNT_inc(newvalue); /* point to live var */ } so as far as I can see a copy is taken but I don't think sv_setsv does any magic. Similar code exists in DBD::Oracle and a number of other DBDs I looked at. DBD::ODBC does use SvPV later so I'm guessing that is where the magic happens and the scalar is stringified. This made me wonder what happens if you pass a reference without any AMAGIC and DBD::ODBC allowed that but you got rubbish so I've now disallowed references without AMAGIC. So now I'm only not sure why in the example provided what passing $obj (and not "$obj") is doing. it appears when it gets the sv_setsv above SvPV on phs->sv is 'Object' and it is address passed to SQLBindParameter but later when execute is called the contents of that address have changed. It seems every time you call SvPV on the copied scalar which is the object you get a different address and because DBD::ODBC binds at bind time and not execute time by execute time the original address has changed. Martin -- Martin J. Evans Easysoft Limited http://www.easysoft.com
Re: Strange bind_param issue
On 15/08/2012 18:41, Tim Bunce wrote: On Wed, Aug 15, 2012 at 06:02:35PM +0100, Charles Jardine wrote: $obj is not a string. It is an object of a class which has a stringify operator. "$obj" is a string, because "..." stringifies. It is not at all clear how the DBI should take a copy of an object. Without any method attributes in the bind_param call the driver is free to 'do something reasonable' which typically means treat the value as a string (or a number if it knows the field is numric). I think this is a case of user error. It would be reasonable for a driver to complain if passed an object that doesn't have string/numeric overloading, but this one does. All points taken so thanks for that but in this case although the object does have a stringification overload as far as I can see it was not used i.e., it was bind_param(1, $obj) not bind_param(1, "$obj") as I received the code. If you use the latter, DBD::ODBC works but the point I'm still uncertain about is that IF DBD::ODBC has not made a copy of the the scalar passed to bind_param then how is it working. I will look further but as far as I can see DBD::ODBC is simply looking at the pv on the passed scalar and passing that pointer to SQLBindParameter and by the time SQLExecute is called Perl may have removed the now unreferenced scalar passed to bind_param. Having said that, I must surely be wrong on how DBD::ODBC does this or my other example which deliberately passes a scalar which a) is changed after the bind_param call and b) goes out of scope before the execute call and yet inserts the value in the scalar passed to bind_param at the time bind_param is called (that is why I added that example). Hopefully, this explains why this confused me a little. I did not write the original bind_param code and need to work my way through it. Martin
Re: Strange bind_param issue
On Wed, Aug 15, 2012 at 06:02:35PM +0100, Charles Jardine wrote: > > $obj is not a string. It is an object of a class which has a > stringify operator. "$obj" is a string, because "..." stringifies. > > It is not at all clear how the DBI should take a copy of an object. Without any method attributes in the bind_param call the driver is free to 'do something reasonable' which typically means treat the value as a string (or a number if it knows the field is numric). > I think this is a case of user error. It would be reasonable for a driver to complain if passed an object that doesn't have string/numeric overloading, but this one does. Tim.
Re: Strange bind_param issue
On 15/08/12 16:14, Martin J. Evans wrote: I've just been given an rt https://rt.cpan.org/Ticket/Display.html?id=78838 and am at a loss to explain exactly what is happening. I wonder if anyone can help? Some background: DBI says for bind_param: "The bind_param method takes a copy of $bind_value and associates it (binds it) with a placeholder" As far as I am aware DBD::ODBC does not copy the scalar given to it - so perhaps DBI does this. The problem I'm seeing in the provided example is the pointer passed to ODBC's SQLBindParameter at the time bind_param is called no longer points to a valid string when execute is called. However, changing the call to bind_param to pass $obj as "$obj" appears to fix the problem. Can anyone say if DBD::ODBC should work with either example and explain what might be happening here: use DBI; my $dbh = DBI->connect("dbi:ODBC:DSN=xxx;UID=xx;PWD=yy;MARS_Connection=No;"); my $obj = new Object(); my $sql = q(SELECT ? AS result); my $sth = $dbh->prepare($sql); # interesting - is the following use of $obj really as a string? # in the call to bind_param here, DBD::ODBC binds the pv ptr of the scalar # it is given in a SQLBindParameter call but by the time execute is called # the string at that address is no longer valid. I kind of expect that as # what to_s returns is only required in the bind_param statement and yet # DBI says "bind_param takes a copy". # However if the following is changed to "$obj" it works $sth->bind_param(1, $obj); $sth->execute(); while (my $row = $sth->fetchrow_hashref()) { print $row->{'result'}, "\n"; } package Object; use overload '""' => 'to_s'; sub new() { bless { }, shift }; sub to_s() { my $self = shift; ref($self); } Output when using $obj: value passed to DBD::ODBC's bind_param = "Object" pointer at execute time seems to point to rubbish output of script: 8�k�8 When "$obj" passed to bind_param value passed to DBD::ODBC's bind_param = "Object" pointer at execute time points to "Object" output of script: Object As a quick test I did the following and it seems to work so I guess there is something about the above Perl I don't get. $obj is not a string. It is an object of a class which has a stringify operator. "$obj" is a string, because "..." stringifies. It is not at all clear how the DBI should take a copy of an object. I think this is a case of user error. -- Charles Jardine - Computing Service, University of Cambridge c...@cam.ac.ukTel: +44 1223 334506, Fax: +44 1223 334679
Re: Strange bind_param issue
On Wed, Aug 15, 2012 at 04:14:52PM +0100, Martin J. Evans wrote: > I've just been given an rt https://rt.cpan.org/Ticket/Display.html?id=78838 > and am at a loss to explain exactly what is happening. I wonder if anyone can > help? > > Some background: > > DBI says for bind_param: > > "The bind_param method takes a copy of $bind_value and associates it (binds > it) with a placeholder" > > As far as I am aware DBD::ODBC does not copy the scalar given to it - > so perhaps DBI does this. The problem I'm seeing in the provided > example is the pointer passed to ODBC's SQLBindParameter at the time > bind_param is called no longer points to a valid string when execute > is called. However, changing the call to bind_param to pass $obj as > "$obj" appears to fix the problem. The driver should take a copy of the value when bind_param is called. Perhaps stored by the ParamValues attribute. (bind_param_inout takes a reference instead and binds at execute.) > Can anyone say if DBD::ODBC should > work with either example and explain what might be happening here: > use DBI; > my $dbh = > DBI->connect("dbi:ODBC:DSN=xxx;UID=xx;PWD=yy;MARS_Connection=No;"); > my $obj = new Object(); > my $sql = q(SELECT ? AS result); > my $sth = $dbh->prepare($sql); > > # interesting - is the following use of $obj really as a string? > # in the call to bind_param here, DBD::ODBC binds the pv ptr of the scalar > # it is given in a SQLBindParameter call but by the time execute is called > # the string at that address is no longer valid. I kind of expect that as > # what to_s returns is only required in the bind_param statement and yet > # DBI says "bind_param takes a copy". > # However if the following is changed to "$obj" it works > $sth->bind_param(1, $obj); > $sth->execute(); > > while (my $row = $sth->fetchrow_hashref()) { > print $row->{'result'}, "\n"; > } > > package Object; > use overload '""' => 'to_s'; > sub new() { bless { }, shift }; > sub to_s() { my $self = shift; ref($self); } The driver needs to make a copy and ensure that 'get magic' is called. SvPV will call get magic but won't make a copy. Since you need an SV for the ParamValues attribute maybe newSVsv() followed by SvPOK_only_UTF8() on the copy would be a suitable incantation. Tim.