in reply to Re: DBD::ODBC won't read past a certain record
in thread DBD::ODBC won't read past a certain record

I agree with LanceDeeply.

A too-large text column was the first thing I thought of when I saw this node, since I've had the same experience. Couple that with the fact that it used to work but no longer does, and that points to the data as well...

Here's the code I use to take care of this:

   
#
# Increase the max size of the text fields retrieved, also prevent us
#    from quitting when we over-step our bounds.
#
$dbh->{LongReadLen} = 1000;
$dbh->{LongTruncOk} = 1;

[download]

Good luck.

Pat

Replies are listed 'Best First'.
Re: Re: Re: DBD::ODBC won't read past a certain record
by Groll (Acolyte) on Aug 06, 2003 at 04:50 UTC
    I've come across this too, as had the original author of the script in question. He had set the LongReadLen to 80,000, which is a bit excessive! I've just retried it with both the LongReadLen set to 16,000 and the LongTruncOk set true, for just the 968077 & 968078 records, only selecting the transactionID, which I know exist via Access, with the DBI trace on, as suggested by one of the other replies, still with no joy.
    The trace shows the execute statement for the second record returning with '0E0' (zero but true) instead of -1. No errors are reported.
    We need to get this working really soon for accounting purposes, so I'm currently looking at a rewrite in Java ... Sacrilege!
[reply]
      The 0E0 return value means that no rows were found. Are you positive that the corresponding TransactionID values exist in the table?

      I'd like to know the schema for the table - the LongReadLen/LongTruncOK is an issue for TEXT or IMAGE columns, but are there any such columns in the table?

      If you select only the TransactionID do you get different results?

      Michael

[reply]
      Groll,

      Do you need to select * from that table to do your accounting process? Have you tried just selecting the columns that you need? If that text/image column is not a necessary part of processing, don't select it. Limit the select columns to the ones you will be using.

      If you really do need it though, here are some last few things to try before you re-write in Java.

      Set the LongReadLen to the size specified by: 

      select maxsize = max ( Datalength( YourBigColumnName ) ) from Transact
+ion

      [download]
      If you're loosing hope, before you do a clomplete re-write in Java, try Win32::ODBC. It's part of the ActiveState distribution. (It sounds like you've working in a Windows environment.) I've had to do this once before when I had trouble accessing large Text/Image data through DBI + IIS.

      You can still use most of your existing code. You'll need to call SetMaxBufSize on the Win32::ODBC handle.

      Good luck!

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom