Query works in sqlplus but not DBI

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

This query that will work via sqlplus and even MS Access linked via ODBC but not DBI::Oracle. It returns "0E0" (no rows) but succeeds in executing. I have tried rewriting it with a "LIKE" and countless other possibilities. What is happening here? The important line is the variable it seems because nothing is wrong with the joins. That field is a VARCHAR. The variable is currently this string (where $l is some ID I want to use):

$myvar="location.location_code = '${l}' "
[download]

my $dbh = DBI->connect('DBI:Oracle:','user', 'pass', { RaiseError => 1
+ }) or die "Could not connect to Oracle backend: $DBI::errstr";
        
        my $query = 
        "select bib_mfhd.bib_id 
        from mfhd_master, bib_mfhd, bib_location, location
        where ( $myvar )
        and bib_mfhd.mfhd_id = mfhd_master.mfhd_id
        and bib_location.location_id = location.location_id
        and bib_mfhd.bib_id = bib_location.bib_id
        and mfhd_master.suppress_in_opac='N'
        and display_call_no is not null
        order by bib_id asc";
        
        print "$query\n" if ($ARGV[0] eq "-d");
        my $sth = $dbh->prepare($query) || die "Failed preparing the q
+uery. WTF?";
        
        my $retcode = $sth->execute;
[download]

$retcode is always 0E0. It should be >1200 rows. $query prints out fine, and I cut and pasted that printed value into sqlplus to make sure I was using the same query. What am I missing?

Comment on Query works in sqlplus but not DBI - Solved Select or Download Code

Replies are listed 'Best First'.
Re: Query works in sqlplus but not DBI by moritz (Cardinal) on Feb 29, 2008 at 14:09 UTC
Print the output of `$myvar`, I guess the `${1}` interpolates in a way you don't expect it to. BTW the line `my $sth = $dbh->prepare($query) \|\| die "Failed preparing the query. WTF?";` looks suspicious - `\|\|` has a tight precedence, you should use `or` instead. Or just leave it, because the RaiseError option takes care of throwing an error.	[reply] [d/l] [select]
Re: Query works in sqlplus but not DBI by olus (Curate) on Feb 29, 2008 at 14:07 UTC
If you want to access and navigate through the results look at the `fetchrow_*` methods on the DBI docs.	[reply] [d/l]
Re: Query works in sqlplus but not DBI by stiller (Friar) on Feb 29, 2008 at 14:10 UTC
Try replacing `WTF?` with `$dbh->errstr` What do you get then?	[reply] [d/l] [select]
Re^2: Query works in sqlplus but not DBI by shadowfae (Initiate) on Feb 29, 2008 at 14:33 UTC
Thanks for the responses! Note in the code that I print out the entire query before it gets passed into the dbh->prepare. This is the query I cut and pasted into sqlplus just for accuracy. All interpolation is done correctly it looks like. The $retcode should be accurate...there is no point in replacing the WTF because that is never printed. The execute works, and the die never occurs. It simply think there are no rows to return. In sqlplus, over 1200 are returned. The $l resolves to two capital letters in the final run, printed just before the prepare statement. I tried messing with all that.	[reply]
Re^3: Query works in sqlplus but not DBI by moritz (Cardinal) on Feb 29, 2008 at 14:42 UTC
How do you try to determine the number of rows that DBI returned? The DBI manpage says A successful "execute" always returns true regardless of the number of rows affected, even if it’s zero ... For "SELECT" statements, execute simply "starts" the query within the database engine. Use one of the fetch methods to retrieve the data after calling "execute". The "execute" method does not return the number of rows that will be returned by the query (because most databases can’t tell in advance), it simply returns a true value.	[reply]
Re^4: Query works in sqlplus but not DBI by shadowfae (Initiate) on Feb 29, 2008 at 14:58 UTC