Re: DBD::ODBC and issues getting LongTruncOk / LongReadLen to take effect

On 15/11/2018 23:54, Daniel Kasak wrote:
> Hi Martin. Sorry for the very long delay. We had abandoned the ODBC 
> driver in favour of the native DBD::DB2, and I've been working on this 
> project outside my normal work hours anyway, so got bogged down in 
> other issues ...
>
> I've uploaded the trace to: https://tesla.duckdns.org/downloads/trace.log
This might be the problem:

     DescribeCol column = 11, name = D, namelen = 7, type = unknown(-99), precision/column size = 65536, scale = 0, nullable = 1
      SQL_COLUMN_DISPLAY_SIZE = 65536
      SQL_COLUMN_LENGTH = 65536
      now using col 11: type = unknown (-99), len = 65537, display size = 65537, prec = 65536, scale = 0

and later

     fetch col#11 D datalen=4294967295 displ=65537
                            ^^^^^^^^^^ that is very large and DBD::ODBC does not know what column type -99 is
     !!dbd_error2(err_rc=-999, what=st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small), handles=(55ddd5896920,55ddd5896f20,55ddd5926680)
     !SQLError(55ddd5896920,55ddd5896f20,55ddd5926680) = (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 DBI attribute LongTruncOk not set and/or LongReadLen too small) (SQL-HY000)', state='HY000', undef
     !! ERROR: 1 'st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small) (SQL-HY000)' (err#1)
1   <- fetch= ( undef ) [1 items] row1 at db2_syscat_columns.pl line 36
     !! ERROR: 1 'st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small) (SQL-HY000)' (err#1)
     <- fetchrow_hashref= ( undef ) [1 items] row1 at db2_syscat_columns.pl line 36


seems to confirm it.

What is column type -99? Can you dump the schema of that table so we can 
see what column 11 is?

I'm guessing it is an XML type which by default is being returned as a 
BLOB. See 
https://www.ibm.com/support/knowledgecenter/fi/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0023298.html

A temporary workaround (although rather ugly) would be instead of doing 
a select *, you selected all the columns except column 11.

A hack would be to map -99 to a blob in dbdimp.c in dbd_describe() 
around these lines:

         /*
          * change fetched size, decimal digits etc for some types,
          * The tests for ColDef = 0 are for when the driver does not give
          * us a length for the column e.g., "max" column types in SQL 
Server
          * like varbinary(max).
          */
         fbh->ftype = SQL_C_CHAR;
         switch(fbh->ColSqlType)
         {
           case SQL_VARBINARY:
           case SQL_BINARY:
           case -99: <---------------------- try adding this line and 
rebuilding DBD::ODBC
             fbh->ftype = SQL_C_BINARY;
             if (fbh->ColDef == 0) {             /* cope with 
varbinary(max) */
                 fbh->ColDisplaySize = DBIc_LongReadLen(imp_sth);
             }
         break;

but of course if you change MapXMLDescribe (which I hadn't heard about 
before today) to map it to a different type the code would need changing 
again.

Probably a better solution in the short term is to set MapXMLDescribe to 
-152 as that is SQL_SS_XML which I think may be the same as 
MS_SQLS_XML_TYPE and of so, that is already handled.

See https://fossies.org/linux/DBD-ODBC/dbdimp.h which suggests 
MS_SQLS_XML_TYPE is -152.

Martin

-- 
Martin J. Evans
Wetherby, UK



> The script I'm using to generate this is: 
> https://tesla.duckdns.org/downloads/db2_syscat_columns.pl
>
> It dies on the 1st call of $sth->fetchrow_hashref():
>  DBD::ODBC::st fetchrow_hashref failed: st_fetch/SQLFetch (long 
> truncated DBI attribute LongTruncOk not set and/or LongReadLen too 
> small) (SQL-HY000) at db2_syscat_columns.pl 
> <http://db2_syscat_columns.pl> line 36.
>
> Thanks for your time ...
>
> Dan
>
> On Thu, Apr 5, 2018 at 9:50 PM Martin J. Evans via dbi-users 
> <[email protected] <mailto:[email protected]>> wrote:
>
>     On 05/04/18 12:24, Daniel Kasak wrote:
>     > Hi all.
>     >
>     > I'm writing a database utility that has to access IBM's "DashDB"
>     and other DB2-variants. I have their latest ODBC driver, and I
>     have simple queries working. However queries against their system
>     catalog are not working - queries appear to be returning *empty*
>     recordsets. After some laborious debugging, I can see that when I
>     call $sth->fetchrow_array, DBI::errstr is set to:
>     >
>     > st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not
>     set and/or LongReadLen too small) (SQL-HY000)
>     >
>     > I've dealt with this before. So when constructing my $dbh, I do:
>     >
>     > $self->{dbh}->{LongReadLen} = 65535 * 1024 # 64MB - yes I know
>     it's big
>     > $self->{dbh}->{LongTruncOK} = 1;
>     >
>     > This usually fixes things - or at least has for SQL Server and
>     Netezza. For DB2 connections however, it appears to have no effect.
>     >
>     > I've turned on ODBC tracing immediately prior to calling
>     $sth->prepare ... $sth->execute ... $sth->fetchrow_array:
>     > https://paste.pound-python.org/show/hS6ur7dwGRsQubr29HFT/
>     >
>     > This one was from the query:
>     > select * from SYSCAT.COLUMNS
>     >
>     > Does anyone know why LongTruncOk / LongReadLen are not being
>     honored here?
>     >
>     > I'm using unixODBC-2.3.6, DBD::ODBC from git, and the latest
>     available DB2 ODBC driver.
>     >
>     > Please help! Thanks :)
>     >
>     > Dan
>
>     There are some very long columns in that result-set but I can't
>     tell from the unixodbc log alone what has happened.
>     Can you reduce your perl to the simplest test you can and rerun with
>
>     DBI_TRACE=15=trace.log perl mycode.pl <http://mycode.pl>
>
>     and show me the trace.log file.
>
>     Martin
>