GhodMode has asked for the wisdom of the Perl Monks concerning the following question:

I'm using DBI version 1.13 on an Oracle 8.1.7 database with Perl version 5.004_02 and I can't get placeholders and bind values to work. There are no error messages, it just doesn't return any rows. The field in the criteria is a VARCHAR2 data type.

This works...
... 

my @accounts = qw{ 118554 118555 118528 118529 118523 };

for ( @accounts ) {
    my $statement = "
        select distinct
            account,
            account_id,
            company,
            the_date
        from account_information
        where account_id = '$_'
    ";

    my $sth = $dbh->prepare( $statement );

    $sth->execute;
    
    while ( my @row = $sth->fetchrow_array ) {
        print join( "\t", @row ) . "\n";
    }
}

[download]
... but this doesn't ... 
my $statement = "
    select distinct
        account,
        account_id,
        company,
        the_date
    from account_information
    where account_id = ?
";

my $sth = $dbh->prepare( $statement );

my @accounts = qw{ 118554 118555 118528 118529 118523 };

for ( @accounts ) {
    $sth->execute( $_ );
    
    while ( my @row = $sth->fetchrow_array ) {
        print join( "\t", @row ) . "\n";
    }
}

[download]
I have tried setting $sth->bind_param( 1, $_ ) separately, but I didn't have any luck.

Does anyone have any ideas?

Replies are listed 'Best First'.
Re: bind not working with DBI?
by liz (Monsignor) on Jul 29, 2003 at 21:35 UTC
    I'm totally guessing here. Try: 
    $sth->execute( "$_" );

    [download]
    I wonder whether having $_ as a numeric value (or just having $_ as a parameter, period) might be improperly handled by this older DBI and/or Oracle driver.

    Liz

[reply]
[d/l]
      I tried putting double-quotes around the execute parameter before my first post :(

      GM
[reply]
Re: bind not working with DBI?
by runrig (Abbot) on Jul 29, 2003 at 22:42 UTC
    Try adding the type parameter to bind_param:
    use DBI qw(:sql_types);
...
$sth->bind_param(1, $_, SQL_VARCHAR); # or whatever type it is

    [download]
[reply]
[d/l]
      try making your sql statement like this 
      my $query = q{select distinct
            account,
            account_id,
            company,
            the_date
        from account_information
        where account_id = ?};

my $sth = $dbh->prepare($query);

$sth->execute($_);

      [download]
      That way the quoting is handled by the dbd (if i'm not mistaken)

      If you'd like to not use the q{} construct you can try this. 
      my $query = "select distinct
            account,
            account_id,
            company,
            the_date
        from account_information
        where account_id = ?";

my $sth = $dbh->prepare($query);

$sth->execute($dbh->quote($_));

      [download]

      just some ideas and different ways I've prepared dbi queries for execution. If any of the methods I used aren't in your version of DBI, please know I wasn't trying to be rude. I'm just not familiar with DBI's history other that what I've used.

      Hope some of this helps.
[reply]
[d/l]
[select]
      I'm not familiar with the qw(:sql_types) part. I know that I can quote some subroutine name after the module name on the use line to only import that subroutine into the main package, but I didn't see a sql_types subroutine in the documentation for DBI 1.13. What's the colon supposed to do?

      I did try specifying the type parameter for the bind_param method. I used the number 12 instead of the descriptive name (VARCHAR2). I got that number after printing out all of the types for this database using the type_info_all database handle method.

      I still haven't had any luck.

      GM
[reply]
        I'm not familiar with the qw(:sql_types) part. ... I didn't see a sql_types subroutine in the documentation for DBI 1.13.
        It was added in 0.88, so I believe you should have it. It imports sql type constants such as 'SQL_VARCHAR'. You can see what it imports with this (which is straight from the DBI docs):
        foreach (@{ $DBI::EXPORT_TAGS{sql_types} }) {
  printf "%s=%d\n", $_, &{"DBI::$_"};
}

        [download]
[reply]
[d/l]
Re: bind not working with DBI?
by liz (Monsignor) on Jul 29, 2003 at 21:17 UTC
    I assume upgrading DBI (currently at 1.37) and/or Perl (now almost at 5.8.1) is out of the question?

    Liz

[reply]

      That's right :( Production Server.

      I think it must be some mistake I'm making, rather than a version incompatibility, though. I've used bind parameters in other programs.

      GM
[reply]
Re: bind not working with DBI?
by Cine (Friar) on Jul 29, 2003 at 21:27 UTC
    See the section about DEBUGGING in the DBI manual.

    T I M T O W T D I
[reply]
[d/l]
Re: bind not working with DBI?
by GhodMode (Pilgrim) on Jul 30, 2003 at 16:38 UTC
    So far, still no luck.

    I've tried with quotes and without quotes around the values to be passed. I've tried specifying the data type when I bind the parameter.

    Thanks to Cine for reminding me to check the DEBUGGING section. I did find the trace method useful. After reading that, I thought that it was the wrong data type being passed, but I didn't have any success when specifying the data type.

    The query does return rows when I run it manually from SQLPlus.

    There wasn't any documentation of the trace output that I could find, but it looks like it's using the correct value for the replaceable parameter, then returning 0 rows, judging by the "0E0" status of the execute.

    If anyone can discern anything from the following, I would be grateful, but I think I'll have to re-prepare the statement for each value.

    Here's some of the output generated using the trace method...
        dbd_preparse scanned 1 distinct placeholders
    fbh 1: 'ACCOUNT_NO'    NO null , otype   2->  5, dbsize 22/40, p10
+.s0
      out: ftype 5, bufl 41. indp 0, rlen 0, rcode 0
    fbh 2: 'EXTERNAL_ID'    NO null , otype  96->  5, dbsize 48/48, p4
+8.s0
      out: ftype 5, bufl 49. indp 0, rlen 0, rcode 0
    fbh 3: 'BILL_REF_NO'    NULLable, otype  96->  5, dbsize 1/1, p1.s
+0
      out: ftype 5, bufl 2. indp 0, rlen 0, rcode 0
    fbh 4: 'BILL_COMPANY'    NULLable, otype   1->  5, dbsize 56/56, p
+56.s0
      out: ftype 5, bufl 57. indp 0, rlen 0, rcode 0
    fbh 5: 'BILL_PERIOD'    NULLable, otype  96->  5, dbsize 3/3, p3.s
+0
      out: ftype 5, bufl 4. indp 0, rlen 0, rcode 0
    fbh 6: 'STATEMENT_DATE'    NULLable, otype  12->  5, dbsize 7/75, 
+p0.s0
      out: ftype 5, bufl 76. indp 0, rlen 0, rcode 0
    <- prepare= DBI::st=HASH(0x1b4afc) at paramtest.pl line 57.
    -> quote in DBD::_::db for DBD::Oracle::db (DBI::db=HASH(0x24bdb8)
+~0x1b4aa8 '118554')
    <- quote= ( ''118554'' ) [1 items] at paramtest.pl line 83.
    -> execute for DBD::Oracle::st (DBI::st=HASH(0x1b4afc)~0x27b014 ''
+118554'')
       bind :p1 <== ''118554'' (type 0)
       bind :p1 <== ''118554'' (size 8/9/0, ptype 4, otype 1)
    dbd_st_execute (for sql f4 after oci f62, out0)...
    dbd_st_execute complete (rc1403, w00, rpc0, eod1403, out0)
    <- execute= '0E0' at paramtest.pl line 83.
    -> fetchrow_array for DBD::Oracle::st (DBI::st=HASH(0x1b4afc)~0x27
+b014)
    <- fetchrow_array= ( ) [0 items] at paramtest.pl line 85.
    -> quote for DBD::Oracle::db (DBI::db=HASH(0x24bdb8)~0x1b4aa8 '118
+555')
    <- quote= ( ''118555'' ) [1 items] at paramtest.pl line 83.
    -> execute for DBD::Oracle::st (DBI::st=HASH(0x1b4afc)~0x27b014 ''
+118555'')
       bind :p1 <== ''118555'' (type 0)
       bind :p1 <== ''118555'' (size 8/9/0, ptype 4, otype 1)
    dbd_st_execute (for sql f4 after oci f62, out0)...
    dbd_st_execute complete (rc1403, w00, rpc0, eod1403, out0)
    <- execute= '0E0' at paramtest.pl line 83.
    -> fetchrow_array for DBD::Oracle::st (DBI::st=HASH(0x1b4afc)~0x27
+b014)
    <- fetchrow_array= ( ) [0 items] at paramtest.pl line 85.
    -> quote for DBD::Oracle::db (DBI::db=HASH(0x24bdb8)~0x1b4aa8 '118
+528')
    <- quote= ( ''118528'' ) [1 items] at paramtest.pl line 83.
    -> execute for DBD::Oracle::st (DBI::st=HASH(0x1b4afc)~0x27b014 ''
+118528'')
       bind :p1 <== ''118528'' (type 0)
       bind :p1 <== ''118528'' (size 8/9/0, ptype 4, otype 1)

    [download]

    GM
[reply]
[d/l]
Re: bind not working with DBI?
by GhodMode (Pilgrim) on Jul 30, 2003 at 17:51 UTC
    EUREKA!!!

    The problem was (sort of) in the query. The account_id field is a CHAR(48) field and apparently DBI tries to match against all 48 characters rather than just what's in the field. So, even though the query worked in SQLPlus as written, I had to change a line in the criteria to "where trim(account_id) = ?" for it to work in the Perl program.

    Darn! I wish I could vote myself up.
    GM
[reply]
[d/l]
      EVEN BETTER!!

      My last message was a solution, but not the best one...

      When I tried to set the data type for the bind_param method, I used 12, which is VARCHAR2, but I should have checked more carefully. The data type in the database is CHAR (type #1). I suspected the data type from the start, but I didn't know how to set the data type properly at first. I thought the data type was VARCHAR2 anyway, which the documentation says is the default.

      Instead of using the trim SQL function, it works if I set the data type in the bind_param method to 1, i.e.: 
      $sth->bind_param( 1, $_, 1 )

      [download]
      :)

      GM
[reply]
[d/l]
Re: bind not working with DBI?
by gnu@perl (Pilgrim) on Jul 30, 2003 at 16:15 UTC
    In your second example you have the following: 
    my @accounts = qw{ 118554 118555 118528 118529 118523 };

    [download]
    Each value in @accounts will be placed verbatim into your query at the location of '?'. The problem with this is that if the column (account_id) is a varchar (or some other non numeric) there will be no quotes passed to the query around the item placed into the location of '?'.

    I recommend changing your array population line to the following: 

    my @accounts = qw{ '118554' '118555' '118528' '118529' '118523' };

    [download]
    Now the " ' " characters will be passed as well as the account number to your query. I'm not sure this is the fix for your problem, but it's worth a try.

    -gnu

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom