Hi,
I'm having the exact same problem on HP-UX. I cannot use place holders with
DBI:DBD:Oracle on HP-UX 11. I have tried many permutations, but nothing works
other than not using placeholders and putting the values directly in the
prepare statement.
I saw Xavier's message in the archive while I was looking for a solution to
this problem and it describes exactly what I have found.
So I'm just saying that I'm having the same problem and would like to know if
anyone has a solution...
My configuration is:
DBI-1.20
DBD-Oracle-1.12
$ perl -v
This is perl, v5.6.1 built for PA-RISC2.0
$ uname -a
HP-UX hostname B.11.00 U 9000/800 672379373 unlimited-user license
Let me know if you need more details about the problem.
I'll keep researching this and see if I can find a solution.
Thanks,
Dave Anderson.
To: <[EMAIL PROTECTED]>
Subject: BUG (?) in DBI with ORACLE CHAR column datatype and placeholders
From: "Xavier Grosjean" <[EMAIL PROTECTED]>
Date: Thu, 18 Oct 2001 12:49:58 +0200
Delivered-To: mailing list [EMAIL PROTECTED]
Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
--------------------------------------------------------------------------------
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();
--
David D. Anderson
[EMAIL PROTECTED]
