Strange DBI/DBD::ODBC behaviour (right truncation of data on ODBC call)

jahero has asked for the wisdom of the Perl Monks concerning the following question:

EDIT: Solved by poj (thank you!), node Re^3: Strange DBI/DBD::ODBC behaviour (right truncation of data on ODBC call)

Dear monks, I am seeking your wisdom in regards to a problem with DBD::ODBC.

I am issuing the a statement into a database connected via ODBC, roughly in the following fashion:

$sth = $dbh->prepare('show table"EP_TGT"."NA_DATA_CNTL";');
$sth->execute;
while (my $text = $sth->fetchrow_array) {        
  # a piece of code not relevant to the question
}
[download]

Resultset should be approximately ~30.000 bytes long.

The query (fetchrow_array) fails with the error of DBD::ODBC::st fetchrow_array failed: st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small).

I was tinkering with the problem a little bit, with the following result:

Setting LongReadLen to 65535 does not help.
Setting LongReadLen to 1 yields partial DDL statement for the table

This is the result of DBI trace (the important part).

    SQLPrepare show table"EP_TGT"."NA_DATA_CNTL";
    SQLPrepare = 0
    <- prepare= ( DBI::st=HASH(0x4c04dc8) ) [1 items] at c:/BI_Domain/
+bimain/bin/../lib/App/Reverse/Ddl.pm line 219 via  at C:/Strawberry/p
+erl/vendor/lib/Try/Tiny.pm line 102
    >> execute     DISPATCH (DBI::st=HASH(0x4c04dc8) rc2/1 @1 g2 ima10
+41 pid#5724) at c:/BI_Domain/bimain/bin/../lib/App/Reverse/Ddl.pm lin
+e 220 via  at C:/Strawberry/perl/vendor/lib/Try/Tiny.pm line 102
    -> execute for DBD::ODBC::st (DBI::st=HASH(0x4c04dc8)~0x4c04ca8) t
+hr#d17d58
    +dbd_st_execute_iv(4c04bd0)
    dbd_st_finish(4c04bd0)
    outparams = 0
    SQLExecute/SQLExecDirect(4c92090)=0
    SQLRowCount=0 (rows=1)
    SQLNumResultCols=0 (flds=1)
    dbd_describe done_desc=0
    dbd_describe SQLNumResultCols=0 (columns=1)
    DescribeCol column = 1, name = R, namelen = 12, type = UNICODE VAR
+CHAR(-9), precision/column size = 21333, scale = 0, nullable = 1
     SQL_COLUMN_DISPLAY_SIZE = 21333
     SQL_COLUMN_LENGTH = 21333
     now using col 1: type = UNICODE VARCHAR (-9), len = 42668, displa
+y size = 42668, prec = 21333, scale = 0
    -dbd_describe done_bind=0
    have 1 fields
    -dbd_st_execute_iv(4c04bd0)=1
    <- execute= ( 1 ) [1 items] at c:/BI_Domain/bimain/bin/../lib/App/
+Reverse/Ddl.pm line 220 via  at C:/Strawberry/perl/vendor/lib/Try/Tin
+y.pm line 102
    >> fetchrow_array DISPATCH (DBI::st=HASH(0x4c04dc8) rc1/1 @1 g2 im
+a0 pid#5724) at c:/BI_Domain/bimain/bin/../lib/App/Reverse/Ddl.pm lin
+e 230 via  at c:/BI_Domain/bimain/bin/../lib/App/Reverse/Ddl.pm line 
+176
    -> fetchrow_array for DBD::ODBC::st (DBI::st=HASH(0x4c04dc8)~0x4c0
+4ca8) thr#d17d58
    bind_columns fbh=45833c8 fields=1
    Bind 1: type = UNICODE CHAR(-8), buf=4d18058, buflen=42668
    bind_columns=0
    SQLFetch=1
    dbih_setup_fbav alloc for 1 fields
    dbih_setup_fbav now 1 fields
    fetch num_fields=1
    fetch col#1 R datalen=46496 displ=42668
    !!dbd_error2(err_rc=-999, what=st_fetch/SQLFetch (long truncated D
+BI attribute LongTruncOk not set and/or LongReadLen too small), handl
+es=(4c8c6e0,4ce13e0,4c92090)
    !SQLError(4c8c6e0,4ce13e0,4c92090) = (HY000, 1, st_fetch/SQLFetch 
+(long truncated DBI attribute LongTruncOk not set and/or LongReadLen 
+too small))
    -- HandleSetErr err=1, errstr='st_fetch/SQLFetch (long truncated D
+BI attribute LongTruncOk not set and/or LongReadLen too small) (SQL-H
+Y000)', state='HY000', undef
    !SQLError(4c8c6e0,4ce13e0,4c92090) = (01004, 10160, [Teradata][ODB
+C] (10160) Output string data right truncation: string data is too bi
+g for the output data buffer and has been truncated.)
    -- HandleSetErr err=1, errstr='[Teradata][ODBC] (10160) Output str
+ing data right truncation: string data is too big for the output data
+ buffer and has been truncated. (SQL-01004)', state='01004', undef
    !! ERROR: 1 'st_fetch/SQLFetch (long truncated DBI attribute LongT
+runcOk not set and/or LongReadLen too small) (SQL-HY000) [state was H
+Y000 now 01004]
[Teradata][ODBC] (10160) Output string data right truncation: string d
+ata is too big for the output data buffer and has been truncated. (SQ
+L-01004)' (err#2)
    <- fetchrow_array= ( undef ) [1 items] row1 at c:/BI_Domain/bimain
+/bin/../lib/App/Reverse/Ddl.pm line 230 via  at c:/BI_Domain/bimain/b
+in/../lib/App/Reverse/Ddl.pm line 176
    >> DESTROY     DISPATCH (DBI::st=HASH(0x4c04dc8) rc1/1 @1 g2 ima10
+004 pid#5724) at c:/BI_Domain/bimain/bin/../lib/App/Reverse/Ddl.pm li
+ne 230 via  at c:/BI_Domain/bimain/bin/../lib/App/Reverse/Ddl.pm line
+ 230
    <> DESTROY(DBI::st=HASH(0x4c04dc8)) ignored for outer handle (inne
+r DBI::st=HASH(0x4c04ca8) has ref cnt 1)
    >> DESTROY     DISPATCH (DBI::st=HASH(0x4c04ca8) rc1/1 @1 g2 ima10
+004 pid#5724) at c:/BI_Domain/bimain/bin/../lib/App/Reverse/Ddl.pm li
+ne 230 via  at c:/BI_Domain/bimain/bin/../lib/App/Reverse/Ddl.pm line
+ 230
    -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x4c04ca8)~INNER) thr#d
+17d58
[download]

Based on that, it seems that:

DBI is treating return value as VARCHAR, not as LOB, therefor rendering any manipulation of LongReadLen moot
Setting LongTruncOk lets the query run its course, however yields only partial results - which is bad

I am on perl 5.28 64bit, DBI version 1.642, DBD::ODBC 1.60

The question: can you suggest a way how to run the querry sucessfully, without truncating the data? Can I "persuade" DBI to use longer buffer for VARCHARs somehow?

Thank you for your wisdom.

Regards,
Jan.

Back to Seekers of Perl Wisdom