Hello
I'm new to this list, and not too experienced with DBI, so please forgive me if the
subject is
well known already, or if this is not the right place to post it...
I came across a weird problem while performing a very simple request with one
placeholder,
and I wonder if it might be a bug or just something I did not grasp...
The full perl script is at the end of the message for people who want to try by
themselves.
The request is:
SELECT xav_id FROM xav_next_id WHERE xav_name=?
xav_name is a CHAR(31) column
I use it giving 'execute()' a 9 character string argument that matches one actual line
in the table.
This request does not find anything.
If I use the same request replacing the placeholder with the SAME 9 character string
value, it WORKS.
(calling execute() with no argument, then).
SELECT xav_id FROM xav_next_id WHERE xav_name='MY_STRING'
I do not understand why it makes a difference whether or I use a placeholder or not...
Is it in the way the request is prepared, like, when the value is known, some
pre-conversion
is set to run at execute time... ?
Well, if it's not a bug, it is misleading...
However, I found a workaround:
SELECT xav_id, xav_name FROM xav_next_id WHERE trim(' ' from xav_name)=?
trim give a VARCHAR2 result...
If I use SQLPLUS and type the request :
SELECT id FROM next_id WHERE tblname=&1 ;
and then type the value 'MY_STRING', it works, and I tend to compare this to using
placeholders in DBI (?)
Thanks for your attention
Regards
Xavier Grosjean
PS : details follow:
Here is how I created the table:
CREATE TABLE XAV_NEXT_ID ( XAV_NAME CHAR(31) NOT NULL, XAV_ID NUMBER NOT NULL)
I 'manually' inserted some rows...
one of which containing 'MY_STRING' in its XAV_NAME column.
The script below will not fetch anything (but no error will be triggered).
If I replace the placeholder by the value (in the prepare method) and remove the
parameter in the execute() call, it works
I'm using:
perl, version 5.005_03 built for sun4-solaris
DBI 1.18
Oracle 8.1.7.2
Solaris 2.6
#!/usr/local/bin/perl -w
use strict ;
use DBI ;
my $base = "XXXX";
my $schema = "XX";
my $schema_pwd = "ZZ";
my $dbh = DBI->connect("DBI:Oracle:$base",$schema,$schema_pwd,{AutoCommit=>0}) or die
"open db fails ".DBI->errst
r ;
my $request = "SELECT xav_id FROM xav_next_id WHERE xav_name=?" ;
my $req_select_lock_next_id = $dbh->prepare($request) or die "req_select_lock_next_id:
" . DBI->errstr ;
my $argument = 'MY_STRING';
$req_select_lock_next_id->execute($argument) or print "execute
req_select_lock_next_id: " . DBI->errstr;
my @row = $req_select_lock_next_id->fetchrow_array ;
if($req_select_lock_next_id->err) {
print "fetch req_select_lock_next_id: $req_select_lock_next_id->errstr" ;
}
print "get_next_id: @row\n" ;
$req_select_lock_next_id->finish();
$dbh->disconnect();
