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

[<ja...@th...>]

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