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
<dbi-users@perl.org <mailto:dbi-users@perl.org>> 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
