Jan,
Thanks a million!!! I appreciate you taking the time to help with this.
As soon as I added $dbh->{ora_ph_type}=96 to my code it work right.
I'm surprised I couldn't find any other mention to this problem other than
Xavier's e-mail... Maybe I missed it in documentation.
Thanks again!
Dave.
--
David D. Anderson
[EMAIL PROTECTED]
On Mon, 22 Oct 2001, Jan Matejka wrote:
> Date: Mon, 22 Oct 2001 12:13:33 +0200
> From: Jan Matejka <[EMAIL PROTECTED]>
> To: 'David D. Anderson' <[EMAIL PROTECTED]>
> Subject: RE: BUG (?) in DBI with ORACLE CHAR column datatype and
> placeholders
>
> Not really bug, only rather silly default setting ...
>
> Have a look at a file "Changes" in DBD-Oracle distribution !
>
> ...
> Changes in DBD::Oracle 1.05 13th July 2000
>
> Added $dbh->{ora_ph_type} attribute to define default bind type:
> 1=> VARCHAR2, does strip trailing spaces, embedded \0 bytes okay
> 5=> STRING, doesn't strip trailing spaces, embedded \0 ends string
> 96=> CHAR, doesn't strip trailing spaces, embedded \0 okay
> 97=> CHARZ, doesn't strip trailing spaces, embedded \0 ends string?
> The two CHAR types force 'blank-padded comparison semantics'.
> ...
>
> Your problem can be easily solved by setting
>
> $dbh->{ora_ph_type}=96;
>
> MaT
>
> > -----Original Message-----
> > From: David D. Anderson [mailto:[EMAIL PROTECTED]]
> > Sent: Monday, October 22, 2001 11:46 AM
> > To: [EMAIL PROTECTED]
> > Cc: [EMAIL PROTECTED]
> > Subject: Re: BUG (?) in DBI with ORACLE CHAR column datatype
> > and placeholders
> >
> >
> > 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,{AutoCommi
> > t=>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]
> >
> >
>
>
