[prev in list] [next in list] [prev in thread] [next in thread] 

List:       dbi-dev
Subject:    Re: Oracle & Trailing Blanks
From:       Tim Bunce <Tim.Bunce () ig ! co ! uk>
Date:       1999-07-29 20:49:38
[Download RAW message or body]

    *** From dbi-users -- To unsubscribe, see the end of this message. ***

On Thu, Jul 29, 1999 at 09:33:55AM -0500, Scott T. HIldreth wrote:
> 
> Hi all, I wonder if someone can let me know if I got this right.
> I have a key to match which can contain trailing blanks.  The 
> field in the database is CHAR(18).  If I match the key with 
> sqlplus, Oracle finds a match, with or without the trailing
> blank.  When I do an sth->execute( $key ), the key is not 
> found.  I abstract the key with substr, so the trailing blank
> is in the key, but no match is found.  Do I need to place qoutes
> around the value in $key?

Somewhat hiddedn in the Oraperl.pm docs it says this:

---
B<DBD:> Substitution variables are now bound as type 1 (VARCHAR2)
and not type 5 (STRING) by default. This can alter the behaviour of
SQL code which compares a char field with a substitution variable.
See the String Comparison section in the Datatypes chapter of the
Oracle OCI manual for more details.
 
You can work around this by using DBD::Oracle's ability to specify
the Oracle type to be used on a per field basis:
 
  $char_attrib = { ora_type => 5 }; # 5 = STRING (ala oraperl2.4)
  $csr = ora_open($dbh, "select foo from bar where x=:1 and y=:2");
  $csr->bind_param(1, $value_x, $char_attrib);
  $csr->bind_param(2, $value_y, $char_attrib);
  ora_bind($csr);  # bind with no parameters since we've done bind_param()'s
---

Ignoring the Oraperl specifics there the key point is to use

  $csr->bind_param($idx, $value, { ora_type => 5 });

I'll add something to the DBD::Oracle docs.

Tim.

------------------------------------------------------------------------------
To unsubscribe from this list, please visit: http://www.isc.org/dbi-lists.html
If you are without web access, or if you are having trouble with the web page,
please send mail to dbi-users-request@isc.org with the subject line of
'unsubscribe'.
------------------------------------------------------------------------------

[prev in list] [next in list] [prev in thread] [next in thread]