Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

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

by jahero (Pilgrim)
on Oct 21, 2019 at 13:01 UTC ( #11107760=perlquestion: print w/replies, xml ) Need Help??

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)

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.

Replies are listed 'Best First'.
Re: Strange DBI/DBD::ODBC behaviour (right truncation of data on ODBC call)
by poj (Abbot) on Oct 22, 2019 at 10:01 UTC

      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

        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
Re: Strange DBI/DBD::ODBC behaviour (right truncation of data on ODBC call)
by jfroebe (Parson) on Oct 21, 2019 at 18:59 UTC

      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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://11107760]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (5)
As of 2019-11-14 03:40 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Strict and warnings: which comes first?



    Results (76 votes). Check out past polls.

    Notices?