On Wed, Apr 17, 2002 at 02:07:36PM +0100, Gaul, Ken wrote: > If you are calling bind_param you don't need to call execute with parameters > this will rebind using the defaults. take out @cdr of the execute line and > see if that works.
It shouldn't matter. Using $dbh->trace(...) would yield more useful information about what's actually going on. Tim. > Ken. > > -----Original Message----- > From: Chris Fuhrman [mailto:[EMAIL PROTECTED]] > Sent: 17 April 2002 14:04 > To: [EMAIL PROTECTED] > Cc: Jim Curran > Subject: Trouble binding fields with spaces in Oracle OCI > > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > 'Morning, > > Been struggling with a not-so-convient "feature" of DBD::Oracle/Oracle > OCI. Best to get the relevant information out of the way first: > > Oracle Server Software: JServer Release 8.1.7.2.0 - Production > Oracle Client Software: SQL*Plus: Release 8.1.7.0.0 - Production > DBI Version : DBI-1.14 > DBD::Oracle Version : DBD-Oracle-1.06 > Perl : 5.6.1 > Operating System : Solaris 8 (SunOS 5.8) on Intel > Solaris 8 (SunOS 5.8) on SPARC > > What the problem is is that in DBD::Oracle, if you insert a field > that's right-justified, left-space filled, then the resultant field is > left-justified and right-space filled. > > This isn't too much of a problem as far as our operations go. > > But, when you insert a field containing nothing but spaces, Oracle > inserts a NULL. This *is* a problem since some of our software will > look for spaces in the field instead of a NULL. > > A search through the dbi-users archive reveal that this isn't a > completely unknown problem. The *usual* solution is to cast the > offending field like so: > > $sth->bind_param($position, $value, SQL_CHAR); > > Now a brief test showed that the above worked. Unfortunately, when I > modified my script similarly, it didn't. I've an array with a bunch > of values that I do some processing based on the field "type". Here's > my block of code: > > /^a_type/ && do { > foreach $position (keys $bighash->{"a_type"}) { > > $cdr[$position] =~ s/\'/-/g; > $$sth->bind_param($position + 1, > $cdr[$position], > SQL_CHAR) > if ($cdr[$position] =~ /^\s+/); > > } # foreach $position (keys ... ) > last TYPE; > }; # Alphanumeric processing > > @cdr contains the array I'm processing > $position refers the position in the array > $bighash a_type is the list of positions I need to process for this > type > > Note that the binding position is offset-base-1 and that position > refers to an array that's offset-base-0, so I add 1 to $position. > > Then, when all is said and done, I do this: > > $$sth->execute(@cdr); > > Please note that I'm referencing the statement handler via a referant > (Tom Christensen loves to tell how people complained when he called it > a "thingy" in _Programming Perl_, 2nd addition. So it's called a > "referant" now ... I'm not going to comment further on this ;) > > For whatever reason, I still have problems with spaces getting > "clobbered". 95% of the fields being inserted are type CHAR. Spaces > are clobbered whether I cast SQL_CHAR or SQL_VARCHAR. > > Now, *this* code works under the same environment: > > $sth->bind_param(1, $array[0], SQL_CHAR); > $sth->bind_param(2, $array[1], SQL_CHAR); > > $sth->execute(@array); > > Needless to say this is frustrating and I can't figure out what I'm > doing wrong here. > > Has anyone encountered this? I'm fresh out of ideas! > > - -- > Chris Fuhrman | Twenty First Century Communications > [EMAIL PROTECTED] | Software Engineer > (W) 614-442-1215 x271 | > (F) 614-442-5662 | PGP/GPG Public Key Available on Request > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.0.6 (GNU/Linux) > Comment: PGPEnvelope - http://pgpenvelope.sourceforge.net > > iD8DBQE8vXKvtZTBgtmnGNERAtSaAKDCU64XKiDR23XKXZHP0WNem6cyGACgqgwG > eFP0bXb+eJaTPj7AglU/YW4= > =EtG4 > -----END PGP SIGNATURE-----
