On Fri, Dec 07, 2001 at 02:40:35PM -0600, Fannin, David P. wrote:
> This was exactly what I needed. For those of you who might be inclined to
> use ORA_CHAR instead of SQL_CHAR, that won't work. DBD::Oracle complains
> and treats the data as SQL_VARCHAR -- at least that was the case for my
> application.
But you probably didn't try:
$sth->bind_param(1, $value, { ora_type => ORA_CHAR } );
Plain
$sth->bind_param(1, $value, SQL_CHAR)
is a shorthand for
$sth->bind_param(1, $value, { TYPE => SQL_CHAR } )
Tim.
> Thanks to everyone for the excellent feedback and assistance.
>
> -dpf-
>
> -----------------------
> David P. Fannin
> Database Administrator [EMAIL PROTECTED]
> UM-Rolla Computing and Information Services FAX (573) 341-4216
> URL http://www.umr.edu/~dpf PHONE (573) 341-4841
> -----------------------
>
>
> -----Original Message-----
> From: Stephen Clouse [mailto:[EMAIL PROTECTED]]
> Sent: Friday, December 07, 2001 1:17 PM
> To: Fannin, David P.
> Cc: '[EMAIL PROTECTED]'
> Subject: Re: DBD::Oracle -- bind variables treat string of spaces as
> NULL
>
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Fri, Dec 07, 2001 at 12:52:35PM -0600, Fannin, David P. wrote:
> > I've got an application where I need to use bind variables to populate
> many
> > tables of a reporting server nightly. I'm using DBD::Oracle to access the
> > target server. If I use $sth->bind_param ($num, ' ') Oracle treats the
> > string of spaces as a NULL in my insert statement. Since NULLS aren't
> > allowed but spaces are, I really need a solution. At this point, the only
> > that does work is going through the following loop:
>
> This tip courtesy of Tim Bunce:
>
> use DBI qw(:sql_types);
> $sth->bind_param(1, $value, SQL_CHAR);
>
> The problem is that OCI "smartly" strips trailing blanks off a bind value of
>
> type VARCHAR2. Explicitly casting it to a CHAR datatype avoids this
> "intelligence".
>
> - --
> Stephen Clouse <[EMAIL PROTECTED]>
> Senior Programmer, IQ Coordinator Project Lead
> The IQ Group, Inc. <http://www.theiqgroup.com/>
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP 6.5.8
>
> iQA/AwUBPBEVtAOGqGs0PadnEQIRugCg+DZy1g0aY3hAjV1vMHfB4UTRnW8AoNMM
> bnSTMkwskWI9/WSywYlE39ki
> =EuTz
> -----END PGP SIGNATURE-----
