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

2018-04-05 Thread Daniel Kasak 
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

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

2018-04-05 Thread Martin J. Evans 

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

and show me the trace.log file.

Martin

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

2018-11-15 Thread Daniel Kasak 
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
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 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> 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
>
> and show me the trace.log file.
>
> Martin
>

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

2018-11-15 Thread Ron Savage 

Hi Dan

Does it make any difference if you put LongReadLen and LongTruncOK next 
to RaiseError in the attrs?


On 16/11/18 10: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
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 
 line 36.


Thanks for your time ...

Dan

On Thu, Apr 5, 2018 at 9:50 PM Martin J. Evans via dbi-users 
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 

and show me the trace.log file.

Martin



--
Ron Savage - savage.net.au

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

2018-11-15 Thread Daniel Kasak 
On Fri, Nov 16, 2018 at 11:18 AM Ron Savage  wrote:

> Hi Dan
>
> Does it make any difference if you put LongReadLen and LongTruncOK next
> to RaiseError in the attrs?
>

Hi Ron. Thanks for the response :)

No change, unfortunately:

my $dbh = DBI->connect(
"dbi:ODBC:DRIVER=DB2;database=BLUDB;hostname=$db2_hostname;port=5"
  , 'bluadmin'
  , ''
  , {
RaiseError=> 0
  , AutoCommit=> 1
  , LongReadLen   => 65535 * 1024  # 64MB
  , LongTruncOK   => 1
}
) || die( $DBI::errstr );

I've commented out all other lines mentioning LongReadLen or LongTruncOK.
This still gives:

Can't set DBI::db=HASH(0x55e58ba53e30)->{LongTruncOK}: unrecognised
attribute name or invalid value at
/usr/lib64/perl5/vendor_perl/5.26.2/x86_64-linux-thread-multi/DBI.pm line
730.
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 line 38.

Dan


>
> On 16/11/18 10: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
> > 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
> >  line 36.
> >
> > Thanks for your time ...
> >
> > Dan
> >
> > On Thu, Apr 5, 2018 at 9:50 PM Martin J. Evans via dbi-users
> > 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 
> >
> > and show me the trace.log file.
> >
> > Martin
> >
>
> --
> Ron Savage - savage.net.au
>

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

2018-11-17 Thread Martin J. Evans 

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 
 line 36.


Thanks for your time ...

Dan

On Thu, Apr 5, 2018 at 9:50 PM Martin J. Evans via dbi-users 
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 tu