Ah, you are right. I was being lazy. When I use the correct syntax, it now works. I think Friday was a bad day. My apologies to the list.
-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: Tim Bunce [mailto:[EMAIL PROTECTED]] Sent: Monday, December 10, 2001 6:54 AM To: Fannin, David P. Cc: '[EMAIL PROTECTED]' Subject: Re: DBD::Oracle -- bind variables treat string of spaces as NULL 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-----
