Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

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

by jahero (Pilgrim)
on Oct 21, 2019 at 13:01 UTC ( [id://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

Re: Strange DBI/DBD::ODBC behaviour (right truncation of data on ODBC call)
by jahero (Pilgrim) on Feb 28, 2022 at 15:04 UTC

    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"; }

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
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?Last hourOther CB clients
Other Users?
Others scrutinizing the Monastery: (4)
As of 2024-03-28 20:23 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found