Re: [Sqlrelay-discussion] Can't bind undef to variable with Perl DBI

[David M. <dav...@fi...>]

Yeah, it looks like my test script doesn't even test NULL binds.  I'll
apply this patch (and any that Tim posts), upgrade my test script and
get them into the next release.

Thanks!

David Muse
dav...@fi...

On Fri, 2007-02-02 at 13:36 +1100, ja...@th... wrote:
> Hi all,
> 
> I've recently started using sqlrelay's DBI driver to connect Perl to
> Oracle (sqlrelay 0.38). I find that I can't bind NULL values (undef,
> in Perl) to a prepared statement.
> 
> For instance, the following example produces the warning "ORA-01008:
> not all variables bound" when run against a test_table.
> 
> # ------------------------------------------------------------
> DBI->trace(1);
> 
> $sql = "insert into test_table ( count ) values ( ? )";
> 
> $c = DBI->connect(...) ;
> 
> $s = $c->prepare($sql);
> 
> $s->bind_param(1, undef);
> $s->execute;
> # ------------------------------------------------------------
> 
> Looking at the source code, I see that src/api/perl/Cursor/Cursor.xs
> tries to detect NULLness of bind values by comparing the SV's address
> to &sv_undef. I don't think that this is right - on my machine (Perl
> 5.8.4 on Linux) the comparison fails even thought the bind value is
> null.
> 
> I think the right way to check whether the SV structure contains undef
> is to use the SvOK macro. 
> 
> I tried making this change and it appears to solve the problem.
> 
> 
> --- Cursor.xs-orig      2007-02-02 13:04:15.000000000 +1100
> +++ Cursor.xs   2007-02-02 13:05:15.000000000 +1100
> @@ -114,7 +114,7 @@
>                                                 (uint32_t)SvIV(ST(4)));
>                 } else if (SvPOK(ST(2))) {
>                         THIS->substitution(variable,SvPV(ST(2),na));
> -               } else if (ST(2)==&sv_undef) {
> +               } else if (!SvOK(ST(2))) {
>                         THIS->substitution(variable,(const char *)NULL);
>                 } else {
>                         RETVAL=0;
> @@ -141,7 +141,7 @@
>                                                 (uint32_t)SvIV(ST(4)));
>                 } else if (SvPOK(ST(2))) {
>                         THIS->inputBind(variable,SvPV(ST(2),na));
> -               } else if (ST(2)==&sv_undef) {
> +               } else if (!SvOK(ST(2))) {
>                         THIS->inputBind(variable,(const char *)NULL);
>                 } else {
>                         RETVAL=0;
> @@ -159,7 +159,7 @@
>                 if (SvPOK(ST(2))) {
>                         THIS->inputBindBlob(variable,value,size);
>                         RETVAL=1;
> -               } else if (ST(2)==&sv_undef) {
> +               } else if (!SvOK(ST(2))) {
>                         THIS->inputBindBlob(variable,NULL,0);
>                 }
>         OUTPUT:
> @@ -175,7 +175,7 @@
>                 if (SvPOK(ST(2))) {
>                         THIS->inputBindClob(variable,value,size);
>                         RETVAL=1;
> -               } else if (ST(2)==&sv_undef) {
> +               } else if (!SvOK(ST(2))) {
>                         THIS->inputBindClob(variable,NULL,0);
>                 }
>         OUTPUT:
> 
> Does anyone have any further thoughts on this?
> 
> Cheers,
> James
> 
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier.
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>