Can't fetch using 2 placeholders in SELECT statement

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

I have a problem fetching data from a Database!
I have searched some O'Reilly Books (from the Perl CD Bookshelf - does not include the DBI book)
and read your Tutorials ("DBI Tricks" and "Reading from a Database") but still I can't do what I want.
Can someone give me a hand here? Thanks!!

Here's a piece of the code:

$sql ="select serial_number, item_id from serial_item_inv where serial
+_number=? and item_id=?";

$dbh = DBI->connect ($dbname, $user, $password, 'Oracle');
$sth = $dbh->prepare($sql);

print "Enter a Serial Number and an Item ID: ";
while (<>) {
        chomp;
        ($sn, $ii) = split /\s/;
      $sth->execute($sn, $ii);
      ($s, $i)  = $sth->fetchrow_array();
        print "Serial Number: $s Item ID: $i\n";
};

$sth->finish();
$dbh->disconnect;
[download]

Funny enough (or perhaps not), this code works fine if I use one sole constrain / placeholder in the SQL statement.

Comment on Can't fetch using 2 placeholders in SELECT statement Download Code

Replies are listed 'Best First'.
Re: Can't fetch using 2 placeholders in SELECT statement by mpeppler (Vicar) on Jan 18, 2002 at 22:40 UTC
First - your connect() call is incorrect. You should have something like: `$dbh = DBI->connect('dbi:Oracle:', $user, $password, $attr);` [download] where $attr is a hash reference to connection attributes that you want to set (such as RaiseError, PrintError, etc). Beyond that, however, your code looks OK, so I have to ask: are you sure that there are rows in the table that will satisfy the WHERE condition for both serial_number and item_id? Michael	[reply] [d/l]
Re: Can't fetch using 2 placeholders in SELECT statement by chromatic (Archbishop) on Jan 19, 2002 at 02:06 UTC
Let Perl help with your debugging. Enable warnings and strictures. `use strict; use warnings; # or add the -w flag on your shebang line my $sql = "..."; my $dbh = ...; my $sth = ...; while (<>) { chomp; next unless $_; my ($sn, $ii) = split(/\s+/, $_, 2); $sth->execute($sn, $ii) or warn $DBI::errstr; if (my ($s, $i) = $sth->fetchrow_array()) { print "Serial Number: $s Item ID: $i\n"; } else { print "Select failed for ($sn) <$ii>\n"; } }` [download]	[reply] [d/l]
Re: Can't fetch using 2 placeholders in SELECT statement by trs80 (Priest) on Jan 18, 2002 at 23:35 UTC
Is this a trick question? You are passing the query string the values you are wanting back, you aren't getting any new informaiton. Was this just an example? I tested just the while loop and it seems fine, the only thing I could think is that maybe there are more then one whitespace characters between the two values.	[reply]