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

[jahero]

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

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
}

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

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.

[poj]

Have you tried odbc_lob_read ?

poj

[jahero]

Thanks for the tip.

I have tried it now.

$sth = $dbh->prepare($sql);
$sth->execute();
my $lob;
my $chrs_or_bytes_read = $sth->odbc_lob_read(0, \$lob, 65535, { TreatA
+sLOB=>1 });

Results in .. Column 0 was not bound with TreatAsLOB

I guess this particular DBI method can only be used on columns that are bound to a prepare statement. If that's true, it can not be used in this instance. There is nothing to bind.

$sth = $dbh->prepare($sql);
$sth->bind_col(1, \$lob);
$sth->execute();

fails with

cannot bind to non-existent field 1

I am sending a DDL statement (show table) into the database, and expecting to get a DDL script of the particular table as output. Everything works fine up until to a certain size of the DDL script, then I am getting the "truncation" error mentioned in the original post.

Regards, Jan

[poj]

I can't test this on Terradata but seems to work on MSSQL

my $sth = $dbh->prepare('select lob from table_lob');
$sth->execute();
$sth->bind_col(1, undef, {TreatAsLOB=>1});
$sth->fetch;
$sth->odbc_lob_read(1, \my $data, 65335);
print $data;

poj

[jfroebe]

You may need to set the StringColumnLength connection parameter to a higher value. see ERROR: "Informatica ODBC 10170 String data right truncation on data from data source: String data is too big for the driver's data buffer" while running the session with PowerExchange for Cassandra for Cassandra Target in PowerCenter. It appears to be an ODBC driver issue not a DBI one imo

[jahero]

Hi Jason!

Thanks for the pointer (and the time it took to find that link).
At the first glance, it does not seem to be the solution - ODBC driver in question (Teradata) does not seem to support such attribute, nor did I find anything with approximately the same meaning. I will, however, investigate, and let you know what I found.

Regards, Jan

[jahero]

I have now faced the same problem with different context - "show table" on Teradata via ODBC call was failing with the error described here. This is the code for sub that returns the table definition correctly, when given the database handle ($dbh) and "show table statement" ($sql).

I am writing this here for any unfortunate soul, which could face the same issue as me in the future - as well as for my future self.

Thanks to all who showed me kindness in the past, and helped me solve the problem.

sub _long_show ( $dbh, $sql )  {    
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    $sth->bind_col(1, undef, {TreatAsLOB=>1});
    
    my $data;
    my $retval = "";
    
    while($sth->fetch) {
        while (my $retrieved = $sth->odbc_lob_read(1, \my $data, 65535
+)) {
            $retval .= $data;
        }
    }
    return $retval . "\n";
}