Skip Menu |
 

Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the DBD-Oracle CPAN distribution.

Report information
The Basics
Id: 18541
Status: resolved
Priority: 0/
Queue: DBD-Oracle

People
Owner: Nobody in particular
Requestors: HMBRAND [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Critical
Broken in: (no value)
Fixed in: (no value)



Download (untitled) / with headers
text/plain 5.4k
Trailing \r characters are trimmed on varchar2 fields. HP-UX 11.11 Oracle 9.2.0 perl-5.8.5-64all-dor DBI-1.50 DBD::Oracle-1.17 DBD::Oracle states: --8<--- ORA_VARCHAR2 Strip trailing spaces and allow embedded \0 bytes. This is the normal default placeholder type. -->8--- No mention of \r Script: --8<--- dd.pl #!/pro/bin/perl use strict; use warnings; use DBI; my %attr = ( RaiseError => 1, PrintError => 1, AutoCommit => 0, ChopBlanks => 0, ShowErrorStatement => 1, FetchHashKeyName => "NAME_lc", ); my $up = $ENV{DBUSER} || $ENV{ORACLE_USERID}; my $dbh = DBI->connect ("DBI:Oracle:", (split m:/: => $up), \%attr) or die "connect: $!"; $dbh->do (q; create table x_cr ( k_xx varchar2 (8), c_xx numeric (9), xx varchar2 (255) );); $dbh->commit; foreach my $xx ( (pack "C", 13), (pack "CC", 1, 13), (pack "CC", 13, 13), (pack "CCC", 1, 13, 0), ) { my $sth = $dbh->prepare ("insert into x_cr values (?, ?, ?)"); $sth->execute ("zzz", 13, $xx); $dbh->commit; } my $sts = $dbh->prepare ("select * from x_cr"); my ($k, $c, $x); $sts->execute; $sts->bind_columns (\($k, $c, $x)); while ($sts->fetch) { for ($k, $c, $x) { my $l = length $_; print "'$_', ($l)\n"; my @x = split //, $_, -1; print ">> ", (map { sprintf "0x%02x ", ord $x[$_] } 0 .. ($l - 1 )), "\n"; } print "---\n"; } $sts->finish; $dbh->commit; $dbh->do ("drop table x_cr"); $dbh->commit; $dbh->disconnect; -->8--- Output: bev r3:/tmp 136 > perl dd.pl 'zzz', (3) Show quoted text
>> 0x7a 0x7a 0x7a
'13', (2) Show quoted text
>> 0x31 0x33
Use of uninitialized value in length at dd.pl line 46. Use of uninitialized value in concatenation (.) or string at dd.pl line 47. '', (0) Use of uninitialized value in split at dd.pl line 48. Show quoted text
>>
--- 'zzz', (3) Show quoted text
>> 0x7a 0x7a 0x7a
'13', (2) Show quoted text
>> 0x31 0x33
'', (1) Show quoted text
>> 0x01
--- 'zzz', (3) Show quoted text
>> 0x7a 0x7a 0x7a
'13', (2) Show quoted text
>> 0x31 0x33
Use of uninitialized value in length at dd.pl line 46. Use of uninitialized value in concatenation (.) or string at dd.pl line 47. '', (0) Use of uninitialized value in split at dd.pl line 48. Show quoted text
>>
--- 'zzz', (3) Show quoted text
>> 0x7a 0x7a 0x7a
'13', (2) Show quoted text
>> 0x31 0x33
', (3) Show quoted text
>> 0x01 0x0d 0x00
--- A DBI trace shows me for the first (pack "C", 13) insert: --8<--- DBI 1.50-nothread default trace level set to 0x0/9 (pid 18857) Show quoted text
>> execute DISPATCH (DBI::st=HASH(0x80000001002dc208) rc1/1 @4 g
0 ima1041 pid#18857) at dd.pl line 37 ') -> execute for DBD::Oracle::st (DBI::st=HASH(0x80000001002dc208)~0x 80000001002db970 'zzz' 13 ' bind :p1 <== 'zzz' (type 0) rebinding :p1 (not-utf8, ftype 1, csid 0, csform 0, inout 0) bind :p1 <== 'zzz' (size 3/4/0, ptype 4, otype 1) bind :p1 <== 'zzz' (size 3/3, otype 1, indp 0, at_exec 1) OCIBindByName(8000000100336140,800000010034c5c8,800000010032d5a 8,":p1",3,80000001003c3d20,3,1,800000010034c5e2,0,800000010034c5e0,0,0, 2)=SUCCESS OCIBindDynamic(8000000100335b38,800000010032d5a8,800000010034c 590,800003fffef2a2a0,800000010034c590,800003fffef2a610)=SUCCESS OCIAttrGet(8000000100335b38,OCI_HTYPE_BIND,800000010034c5ac,0, 31,800000010032d5a8)=SUCCESS bind :p1 <== 'zzz' (in, not-utf8, csid 1->0->1, ftype 1, csform 0 ->0, maxlen 3, maxdata_size 0) OCIAttrSet(8000000100335b38,OCI_HTYPE_BIND,800003fffeff2c0a,0, 31,800000010032d5a8)=SUCCESS bind :p2 <== 13 (type 0) rebinding :p2 (not-utf8, ftype 1, csid 0, csform 0, inout 0) bind :p2 <== 13 (size 2/3/0, ptype 5, otype 1) bind :p2 <== '13' (size 2/2, otype 1, indp 0, at_exec 1) OCIBindByName(8000000100336140,80000001003c2670,800000010032d5a 8,":p2",3,80000001003c41a8,2,1,80000001003c268a,0,80000001003c2688,0,0, 2)=SUCCESS OCIBindDynamic(8000000100335a80,800000010032d5a8,80000001003c 2638,800003fffef2a2a0,80000001003c2638,800003fffef2a610)=SUCCESS OCIAttrGet(8000000100335a80,OCI_HTYPE_BIND,80000001003c2654,0, 31,800000010032d5a8)=SUCCESS bind :p2 <== 13 (in, not-utf8, csid 1->0->1, ftype 1, csform 0-> 0, maxlen 2, maxdata_size 0) OCIAttrSet(8000000100335a80,OCI_HTYPE_BIND,800003fffeff2c0a,0, 31,800000010032d5a8)=SUCCESS ' (type 0)d :p3 <== ' rebinding :p3 (not-utf8, ftype 1, csid 0, csform 0, inout 0) ' (size 1/2/0, ptype 4, otype 1) ' (size 1/1, otype 1, indp 0, at_exec 1) OCIBindByName(8000000100336140,80000001003c2b10,800000010032d5a 8,":p3",3,80000001003c41e8,1,1,80000001003c2b2a,0,80000001003c2b28,0,0, 2)=SUCCESS OCIBindDynamic(80000001003359c8,800000010032d5a8,80000001003c2ad 8,800003fffef2a2a0,80000001003c2ad8,800003fffef2a610)=SUCCESS OCIAttrGet(80000001003359c8,OCI_HTYPE_BIND,80000001003c2af4,0, 31,800000010032d5a8)=SUCCESS ' (in, not-utf8, csid 1->0->1, ftype 1, csform 0->0, maxlen 1, maxdata_ size 0) OCIAttrSet(80000001003359c8,OCI_HTYPE_BIND,800003fffeff2c0a,0, 31,800000010032d5a8)=SUCCESS dbd_st_execute INSERT (out0, lob0)... in ':p1' [0,0]: len 3, ind 0 in ':p2' [0,0]: len 2, ind 0 in ':p3' [0,0]: len 1, ind 0 OCIStmtExecute(800000010032d4d8,8000000100336140,800000010032d5a 8,1,0,0,0,0)=SUCCESS OCIAttrGet(8000000100336140,OCI_HTYPE_STMT,800003fffeff2980,0,9, 800000010032d5a8)=SUCCESS OCIAttrGet(8000000100336140,OCI_HTYPE_STMT,800003fffeff29c8,0, 10,800000010032d5a8)=SUCCESS dbd_st_execute INSERT returned (SUCCESS, rpc1, fn3, out0) <- execute= 1 at dd.pl line 37 -->8--- A clear length of 1 for :p3, and a success. A trace on the login gives me charsetid=1 ncharsetid=1 (csid: utf8=871 al32utf8=873)
Download (untitled) / with headers
text/plain 1012b
Extra Info: Columns: 1st column is what I insert, second is what I get back I hope RT doesn't compress blanks, as they are important here Oracle-9.2.0/64 on HP-UX 11.11 char (4) {ChopBlanks} = 0 undef NULL "" NULL " " NULL "\r" ' ' " \r" ' ' {ChopBlanks} = 1 undef NULL "" NULL " " NULL "\r" '' " \r" '' varchar2 (4) {ChopBlanks} = 0 undef NULL "" NULL " " NULL "\r" NULL " \r" NULL {ChopBlanks} = 1 undef NULL "" NULL " " NULL "\r" NULL " \r" NULL Oracle-8.1.7/32 on HP-UX 11.00 char (4) {ChopBlanks} = 0 undef NULL "" NULL " " NULL "\r" '\r ' " \r" ' \r ' {ChopBlanks} = 1 undef NULL "" NULL " " NULL "\r" '\r' " \r" ' \r' varchar2 (4) {ChopBlanks} = 0 undef NULL "" NULL " " NULL "\r" '\r' " \r" ' \r' {ChopBlanks} = 1 undef NULL "" NULL " " NULL "\r" '\r' " \r" ' \r'
Attached should be a plain text file. Hope that keeps the format/spacing
Download OraNull.txt
text/plain 1.4k
Columns: 1st column is what I insert, second is what I get back I hope RT doesn"t compress blanks, as they are important here Oracle-9.2.0/64 on HP-UX 11.11 insert char (4) undef "" " " "\r" " \r" select {ChopBlanks} = 0 NULL NULL NULL " " " " select {ChopBlanks} = 1 NULL NULL NULL "" "" insert varchar2 (4) undef "" " " "\r" " \r" select {ChopBlanks} = 0 NULL NULL NULL NULL NULL select {ChopBlanks} = 1 NULL NULL NULL NULL NULL Oracle-8.1.7/32 on HP-UX 11.00 insert char (4) undef "" " " "\r" " \r" select {ChopBlanks} = 0 NULL NULL NULL "\r " " \r " select {ChopBlanks} = 1 NULL NULL NULL "\r" " \r" insert varchar2 (4) undef "" " " "\r" " \r" select {ChopBlanks} = 0 NULL NULL NULL "\r" " \r" select {ChopBlanks} = 1 NULL NULL NULL "\r" " \r" What I expect in *ANY* (or every) Database: insert char (4) undef "" " " "\r" " \r" select {ChopBlanks} = 0 NULL " " " " "\r " " \r " select {ChopBlanks} = 1 NULL "" "" "\r" " \r" insert varchar2 (4) undef "" " " "\r" " \r" select {ChopBlanks} = 0 NULL "" " " "\r" " \r" select {ChopBlanks} = 1 NULL "" "" "\r" " \r"
From: JScoles
Download (untitled) / with headers
text/plain 501b
On Mon May 15 08:35:22 2006, HMBRAND wrote: Looking at the pod I found this ora_ph_type The default placeholder data type for the database session. The TYPE or "ora_type" attributes to "bind_param" in DBI and "bind_param_inout" in DBI override the data type for individual placeholders. The most frequent reason for using this attribute is to permit trailing spaces in values passed by placeholders. Seems if you use this trailing spaces will be included otherwise Oracle will cut them off.
Download (untitled) / with headers
text/plain 196b
Looking at some other stuff on this is seems that some installs of 9.0.2 will strip the trailing spaces off. Cna your give me the exact oracle install number with any pathces that may be involved.
Seems this was an Oracle version specific issue.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.