Strange problem with DBI placeholders

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

I am facing a strange situation and do not have a clue of why this is happenning. Any help will be really appreciated.
Here's the code which works:

my $h_sql1 = $db_inv->prepare_cached( "select items from item_a where 
+item_code = ? and item_stock = 8" ) or die $DBI::errstr;

$h_sql1->execute( "A0001" );
[download]

After this I fetch the rows and get 1 row as result (which is what I should).
Then I changed this sql to following:

my $h_sql1 = $db_inv->prepare_cached( "select items from item_a where 
+item_code = ? and item_stock = ?" ) or die $DBI::errstr;

$h_sql1->execute( "A0001", 8 );
[download]

This does not returns any rows. I have checked it with tracelevel set to 1, 2, and 3.
Seems like the binding is done correctly but the oracle database simply does not returns a row in the second sql (where I have 2 placeholders).
I've tried all different combinations with bind_param, etc and nothing seems to work.
As long as there is only 1 placeholder (? or even :var1 ) it works. The moment I add a second placeholder, there are no rows returned.
Can anyone help here?
Regards

Comment on Strange problem with DBI placeholders Select or Download Code

Replies are listed 'Best First'.
Re: Strange problem with DBI placeholders by runrig (Abbot) on Sep 12, 2006 at 16:39 UTC
Set the RaiseError attribute on the connect, then you don't have to put 'or die $DBI::errstr' after everything, and it will check for errors on every DBI function, even the execute() where you are not checking for errors.	[reply]
Re: Strange problem with DBI placeholders by jdtoronto (Prior) on Sep 12, 2006 at 15:54 UTC
Did you try quoting the 8? Oracle does some funny things with placeholders - I havent used it for a while but I recall we had to usequotes where you might not expect to otherwise. jdtoronto	[reply]
Re: Strange problem with DBI placeholders by gefilte (Initiate) on Sep 12, 2006 at 14:57 UTC
Can you tell us what DBD you are using, and what versions of DBI and DBD::*?	[reply]
Re^2: Strange problem with DBI placeholders by ravim (Initiate) on Sep 12, 2006 at 15:24 UTC
DBD::Oracle DBI version is 1.42	[reply]
Re: Strange problem with DBI placeholders by rir (Vicar) on Sep 12, 2006 at 19:47 UTC
I concurr with all the previous respondents; a few other points: Occam's razor applies well to programming; I would try: `my $h_sql1 = $db_inv->prepare_cached( "select items from item_a where " ." item_stock = ? " or die $DBI::errstr ); $h_sql1->execute( 8 );` [download] Go to the DBI users mailing list. Some go tos are good. I forget the syntax but you can define the SQL type of placeheld arguments; where possible, I'd avoid the need to use that solution, instead changing my schema to use more compatible types. My determinations on the issue are a couple of years old, DBI and associated db drivers may have evolved. Be well, rir	[reply] [d/l]
Re: Strange problem with DBI placeholders by skx (Parson) on Sep 12, 2006 at 20:18 UTC
Have you tryed coercing the type correctly? I know that I've done this in the past, since I have code samples in front of me where it is present but honestly I can't remember why I believed it was necessary right now! Here is my suggestion: `use DBI qw/ :sql_types / ; # Database interface my $h_sql1 = $db_inv->prepare_cached( "select items from item_a where item_code = ? and item_stock = ?" ) or die $DBI::errstr; $h_sql1->bind_param( 1, "A0001" ); $h_sql1->bind_param( 2, 8, SQL_INTEGER ); $h_sql1->execute();` [download] Steve --	[reply] [d/l]
Re^2: Strange problem with DBI placeholders by Anonymous Monk on Sep 13, 2006 at 14:54 UTC
Thanks for all your replies. Unfortunately, I've tried everything you people suggested and nothing worked.So, I've kind of given up on it for now. What I'm doing now is to retrieve all combinations of item code and item stock, and store these in a hash table. This way I can query the hash table later on. Not the best way to do I know, but I've spent a few days on this issue and need to move on. Thanks for all your inputs anyway.	[reply]