Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > DBI::DB2 non ascii character issue.

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 11-19-10, 17:55
thack111 thack111 is offline
Registered User
  
Join Date: Nov 2010
Posts: 3
Question DBI::DB2 non ascii character issue.
Hello,

I have run into an issue where one field of one record in a DB2 table has a non-ascii character 0xA6. I have run across this in the past on other platforms, and just did a basic substitute in perl to remove it before I processed that record of the recordset. But this one will not get past the fetchrow_array call. I cannot even loop through the recordset with an empty loop. It will also fail if I use fetchrow_hash or any others I have tried. I am not sure if this could be an error in DBI, or DB2, or something I am doing wrong.
Code:
DBI 1.615
DBD::DB2 1.80
perl v5.8.8 

Here is my test script & results:
#!/usr/bin/perl
print "\nStarting Test Script...\n";
use strict;
use DBI;
use DBD::DB2;

print localtime(time)."Importing info \n";
my ($dbh_db2,$stht,@row,$sth,$insSql,$sql);
my $db2User = 'user';
my $db2Pass = '******';
$dbh_db2 = DBI->connect( 'dbi:DB2:IMSC5',$db2User,$db2Pass,
                        {
                        RaiseError => 1
                        }
                     ) || die ("Database connection not made: $DBI::errstr");
$sql = "select  substr(cl_summary, 36, 1)
        from    DBC5VJD1.VJDB_OTR_MISC_T m
        where   m.tr# = 'PW065846'";

#The value = 0xA6.  ¦

$stht = $dbh_db2->prepare($sql) || die ("Database statment not made: $@\n");
$stht->execute() || die ("Error: $@\n");
print $stht->rows." rows\n";
eval
{
    print "found: ".$stht->fetchrow_array()."\n"; 
};

if( $@ )
{
    print "Unknown error: $@\n";
}
$stht->finish();
$dbh_db2->disconnect();


OUTPUT with trace

    DBI 1.615-ithread default trace level set to 0x0/5 (pid 26996 pi 1ae21010) at test.pl line 8
    Note: perl is running without the recommended perl -w option
Fri Nov 19 16:31:17 2010Importing MOKA Pending Ticket info
    -> DBI->connect(dbi:DB2:IMSC5, user, ****, HASH(0x1ae222a0))
    -> DBI->install_driver(DB2) for linux perl=5.008008 pid=26996 ruid=500 euid=500
       install_driver: DBD::DB2 version 1.80 loaded from /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/DB2.pm
    New 'DBI::dr' (for DBD::DB2::dr, parent='', id=undef)
    dbih_setup_handle(DBI::dr=HASH(0x1af1fe80)=>DBI::dr=HASH(0x1af1fef0), DBD::DB2::dr, 0, Null!)
    dbih_make_com(Null!, 0, DBD::DB2::dr, 168, 0) thr#1ae21010
    dbih_setup_attrib(DBI::dr=HASH(0x1af1fef0), Err, Null!) SCALAR(0x1b2c42e0) (already defined)
    dbih_setup_attrib(DBI::dr=HASH(0x1af1fef0), State, Null!) SCALAR(0x1af35320) (already defined)
    dbih_setup_attrib(DBI::dr=HASH(0x1af1fef0), Errstr, Null!) SCALAR(0x1af35350) (already defined)
    dbih_setup_attrib(DBI::dr=HASH(0x1af1fef0), TraceLevel, Null!) 0 (already defined)
    dbih_setup_attrib(DBI::dr=HASH(0x1af1fef0), FetchHashKeyName, Null!) 'NAME' (already defined)
    <- install_driver= DBI::dr=HASH(0x1af1fe80)
    !! warn: 0 CLEARED by call to connect method
    -> connect for DBD::DB2::dr (DBI::dr=HASH(0x1af1fe80)~0x1af1fef0 'IMSC5' 'user' **** HASH(0x1af44e30)) thr#1ae21010
    New 'DBI::db' (for DBD::DB2::db, parent=DBI::dr=HASH(0x1af1fef0), id=undef)
    dbih_setup_handle(DBI::db=HASH(0x1af203c0)=>DBI::db=HASH(0x1af20310), DBD::DB2::db, 1afa88c0, Null!)
    dbih_make_com(DBI::dr=HASH(0x1af1fef0), 1b0bbbb0, DBD::DB2::db, 176, 0) thr#1ae21010
    dbih_setup_attrib(DBI::db=HASH(0x1af20310), Err, DBI::dr=HASH(0x1af1fef0)) SCALAR(0x1afa8ad0) (already defined)
    dbih_setup_attrib(DBI::db=HASH(0x1af20310), State, DBI::dr=HASH(0x1af1fef0)) SCALAR(0x1afa8b50) (already defined)
    dbih_setup_attrib(DBI::db=HASH(0x1af20310), Errstr, DBI::dr=HASH(0x1af1fef0)) SCALAR(0x1afa8b10) (already defined)
    dbih_setup_attrib(DBI::db=HASH(0x1af20310), TraceLevel, DBI::dr=HASH(0x1af1fef0)) 0 (already defined)
    dbih_setup_attrib(DBI::db=HASH(0x1af20310), FetchHashKeyName, DBI::dr=HASH(0x1af1fef0)) 'NAME' (already defined)
    dbih_setup_attrib(DBI::db=HASH(0x1af20310), HandleSetErr, DBI::dr=HASH(0x1af1fef0)) undef (not defined)
    dbih_setup_attrib(DBI::db=HASH(0x1af20310), HandleError, DBI::dr=HASH(0x1af1fef0)) undef (not defined)
    dbih_setup_attrib(DBI::db=HASH(0x1af20310), ReadOnly, DBI::dr=HASH(0x1af1fef0)) undef (not defined)
    dbih_setup_attrib(DBI::db=HASH(0x1af20310), Profile, DBI::dr=HASH(0x1af1fef0)) undef (not defined)
connect 'IMSC5', 'user', '***'    <- connect= DBI::db=HASH(0x1af203c0) at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBI.pm line 665
    -> STORE for DBD::DB2::db (DBI::db=HASH(0x1af20310)~INNER 'RaiseError' 1) thr#1ae21010
    STORE DBI::db=HASH(0x1af20310) 'RaiseError' => 1
    <- STORE= 1 at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBI.pm line 717
    -> STORE for DBD::DB2::db (DBI::db=HASH(0x1af20310)~INNER 'PrintError' 1) thr#1ae21010
    STORE DBI::db=HASH(0x1af20310) 'PrintError' => 1
    <- STORE= 1 at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBI.pm line 717
    -> STORE for DBD::DB2::db (DBI::db=HASH(0x1af20310)~INNER 'AutoCommit' 1) thr#1ae21010
    <- STORE= 1 at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBI.pm line 717
    -> STORE for DBD::DB2::db (DBI::db=HASH(0x1af20310)~INNER 'Username' 'user') thr#1ae21010
    STORE DBI::db=HASH(0x1af20310) 'Username' => 'user'
    <- STORE= 1 at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBI.pm line 720 via  at ./test.pl line 15
    <> FETCH= 'user' ('Username' from cache) at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBI.pm line 720
    -> connected in DBD::_::db for DBD::DB2::db (DBI::db=HASH(0x1af203c0)~0x1af20310 'dbi:DB2:IMSC5' 'user' ****' HASH(0x1ae222a0)) thr#1ae21010
    <- connected= undef at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBI.pm line 726
    <- connect= DBI::db=HASH(0x1af203c0)
    -> STORE for DBD::DB2::db (DBI::db=HASH(0x1af20310)~INNER 'dbi_connect_closure' CODE(0x1af1fdb0)) thr#1ae21010
    STORE DBI::db=HASH(0x1af20310) 'dbi_connect_closure' => CODE(0x1af1fdb0)
    <- STORE= 1 at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBI.pm line 735 via  at ./test.pl line 15
    -> prepare for DBD::DB2::db (DBI::db=HASH(0x1af203c0)~0x1af20310 'select  substr(cl_summary, 36, 1)
        from    DBC5VJD1.VJDB_OTR_MISC_T m
        where   m.tr# = 'PW065846'') thr#1ae21010
    New 'DBI::st' (for DBD::DB2::st, parent=DBI::db=HASH(0x1af20310), id=undef)
    dbih_setup_handle(DBI::st=HASH(0x1af204e0)=>DBI::st=HASH(0x1af203a0), DBD::DB2::st, 1afa8bc0, Null!)
    dbih_make_com(DBI::db=HASH(0x1af20310), 1af215b0, DBD::DB2::st, 272, 0) thr#1ae21010
    dbih_setup_attrib(DBI::st=HASH(0x1af203a0), Err, DBI::db=HASH(0x1af20310)) SCALAR(0x1afa8ad0) (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x1af203a0), State, DBI::db=HASH(0x1af20310)) SCALAR(0x1afa8b50) (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x1af203a0), Errstr, DBI::db=HASH(0x1af20310)) SCALAR(0x1afa8b10) (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x1af203a0), TraceLevel, DBI::db=HASH(0x1af20310)) 0 (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x1af203a0), FetchHashKeyName, DBI::db=HASH(0x1af20310)) 'NAME' (already defined)
    dbih_setup_attrib(DBI::st=HASH(0x1af203a0), HandleSetErr, DBI::db=HASH(0x1af20310)) undef (not defined)
    dbih_setup_attrib(DBI::st=HASH(0x1af203a0), HandleError, DBI::db=HASH(0x1af20310)) undef (not defined)
    dbih_setup_attrib(DBI::st=HASH(0x1af203a0), ReadOnly, DBI::db=HASH(0x1af20310)) undef (not defined)
    dbih_setup_attrib(DBI::st=HASH(0x1af203a0), Profile, DBI::db=HASH(0x1af20310)) undef (not defined)
    dbd_st_prepare'd sql f65537
        select  substr(cl_summary, 36, 1)
        from    DBC5VJD1.VJDB_OTR_MISC_T m
        where   m.tr# = 'PW065846'
fbh 0: '1' , type 1,  1, dsize 1, p0 s456039644
   out: ftype 1, indp 0, bufl 2, rlen 2
    <- prepare= DBI::st=HASH(0x1af204e0) at ./test.pl line 28
    -> execute for DBD::DB2::st (DBI::st=HASH(0x1af204e0)~0x1af203a0) thr#1ae21010
    <- execute= -1 at ./test.pl line 29
    -> rows for DBD::DB2::st (DBI::st=HASH(0x1af204e0)~0x1af203a0) thr#1ae21010
    <- rows= -1 at ./test.pl line 30
-1 rows
    -> fetchrow_array for DBD::DB2::st (DBI::st=HASH(0x1af204e0)~0x1af203a0) thr#1ae21010
    dbih_setup_fbav alloc for 1 fields
    dbd_st_fetch 1 fields
Unknown error: Error: Data in column 0 has been truncated to 1 bytes.  A maximum of 2 bytes are available at ./test.pl line 33.

    -> finish for DBD::DB2::st (DBI::st=HASH(0x1af204e0)~0x1af203a0) thr#1ae21010
    <- finish= 1 at ./test.pl line 40
    -> disconnect for DBD::DB2::db (DBI::db=HASH(0x1af203c0)~0x1af20310) thr#1ae21010
    <- disconnect= 1 at ./test.pl line 41
    <> DESTROY(DBI::st=HASH(0x1af204e0)) ignored for outer handle (inner DBI::st=HASH(0x1af203a0) has ref cnt 1)
    -> DESTROY for DBD::DB2::st (DBI::st=HASH(0x1af203a0)~INNER) thr#1ae21010
    <- DESTROY= undef
    dbih_clearcom 0x1af203a0 (com 0x1b2ea2d0, type 3) done.

    <> DESTROY(DBI::db=HASH(0x1af203c0)) ignored for outer handle (inner DBI::db=HASH(0x1af20310) has ref cnt 1)
    -> DESTROY for DBD::DB2::db (DBI::db=HASH(0x1af20310)~INNER) thr#1ae21010
    <- DESTROY= undef
    dbih_clearcom 0x1af20310 (com 0x1af215b0, type 2) done.

    -- DBI::END ($@: , $!: )
    -> disconnect_all for DBD::DB2::dr (DBI::dr=HASH(0x1af1fe80)~0x1af1fef0) thr#1ae21010
    <- disconnect_all= 1 at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBI.pm line 744 via  at ./test.pl line 0
!   <> DESTROY(DBI::dr=HASH(0x1af1fe80)) ignored for outer handle (inner DBI::dr=HASH(0x1af1fef0) has ref cnt 1)
!   -> DESTROY in DBD::_::common for DBD::DB2::dr (DBI::dr=HASH(0x1af1fef0)~INNER) thr#1ae21010
!   <- DESTROY= undef during global destruction
    dbih_clearcom 0x1af1fef0 (com 0x1b0bbbb0, type 1) done.
Thank you in advance for any help you can give, this one has me stumped

Todd
Reply With Quote
  #2 (permalink)  
Old 11-19-10, 23:36
ARWinner ARWinner is offline
Registered User
  
Join Date: Jan 2003
Posts: 3,575
Why not translate it on the select statement:

select translate(col1,' ','0xA6') as col1 from mytable

Andy
Reply With Quote
  #3 (permalink)  
Old 12-09-10, 09:12
thack111 thack111 is offline
Registered User
  
Join Date: Nov 2010
Posts: 3
translate
Hello, thanks for the reply. I was able to get translate to work, and wanted to post the syntax in case anyone else needs it. The bad thing is i have to translate each invalid character on each field. I don't think DB2 supports regular expressions or translate allows a list of items to "look for" So still searching for a better solution.

Here is actually what i did get to work:

select translate(col1,' ',X'A6') as col1 from mytable

Very small difference, but thought i would share.

Thanks,
Todd
Reply With Quote
  #4 (permalink)  
Old 12-16-10, 11:44
thack111 thack111 is offline
Registered User
  
Join Date: Nov 2010
Posts: 3
Talking Best Solution
Hello,
I thought i would post a better solution that i have found to this issues, in case anyone else is having this problem.

The translate worked, but only for one specific character in one column. Turns out there are other specific characters in multiple columns, so this did not work out.

I found that if you set an environment variable it will correct this for everything.

In Linux you need to set : DB2CODEPAGE=819
in Windows you need to set : DB2CODEPAGE=1252

This does not get rid of the characters but prevents the error from DBD:B2, and allows you to handle the non-ascii characters in perl as you loop through the record set.

Basically this changes DBD:B2 to allow 4-byte characters and pass them through, rather than throwing the truncated field error.

I found this solution at :
https://rt.cpan.org/Public/Bug/Display.html?id=47429

Just thought i would share.

Thanks,
Reply With Quote
Reply

Tags
db2, dbd, dbi, non-ascii, perl

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.