23/10/2008

SnTT: when LS will not retrieve all SQL data via ODBC...

This SnTT post may not be new for some people, but believe me, it will be useful to many programmers you will soon deal with this issue ;-)

Every now and then I develop a LotusScript agent to retrieve data from any data source via ODBC and SQL statement: usually I use LS:DO classes to do the job, easy and straightforward way.

Problem is when reading a large recordset, via a standard code like this:
' result as ODBCResultSet

Do

result.NextRow
'Process each row


Loop Until result.IsEndOfData


For some reason, the Do Loop code will exit even though the recordset is not fully read...
That makes me crazy: data is still available but the res.IsEndOfData equals True?!

Anyhow, there's a workaround.
Just AFTER result.Execute (will run your SQL statement...), add the code line:
result.cacheLimit= DB_NONE
and you will get ALL the data from the remote system.

From the Designer Help:
DB_NONE= minimum set. DB_NONE means that only a very small window, including the current row, is kept in memory.

See also an IBM Technote about it


No comments: