Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??

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.


In reply to Strange DBI/DBD::ODBC behaviour (right truncation of data on ODBC call) by jahero

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others romping around the Monastery: (6)
As of 2024-03-29 01:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found