Hello everyone. I have a strange result using ODBC driver (Uselsx “_lsxodbc”) with Notes v6.5. I have an agent that retrieves all records from a table (select * from table1). strangely it doesn’t give me any kind of error, but i know that not all the records are retreived. the table as almost 20.000 records and i have tried with a SQL server tabel and an Unix Informix v7 table with the same result. Any ideas or sugestions welcome.
Paulo campino
Subject: ODBC recordset not returning all records
The short answer is Lotuscript Data Objects (LSDO) is a heap of junk and unreliable. You should use the Lotus Connectors LSX (Uselsx “*lsxlc”) instead. It will mean a complete rewrite of your data access code, but it is much more stable. Now if only they had decent documentation for it.
HTH,
Charles
P.S. There is an Enterprise Integration forum where more people will be able to help with these kind of questions.
Subject: I dont agree. LS:DO is working OK
I’ been using it for more than 4 years with many customers and it is OK for getting data out of relationnal databases.
Subject: ODBC recordset not returning all records
i think you have to loop through a couple of times to get all the data (until you get and END OF DATA) it hardly ever returns all the data in one go. there are code examples around somewhere but as i haven’t done odbc for about 6 months now i’ve forgotten where everything is.
just has a look in the designer help and there are examples in there that show you how to traverse an entire resultset. search for NumRows and the first document is very helpful.
Subject: You need to limit the cache size. That’s it.
All records you fetch are cached until memory is full.
To avoid this, you have to limit the cache size and also the fetch-batch size.
Lotus documentation is incorrect: you have to set the fetch-batch size to a number less than the cache size (and never equal to).
Example:
Set result = New ODBCResultSet
result.cacheLimit = 50
result.FetchBatchSize = 40
Nota: the cache is used only in case you need to access the same record a second time. In your case you don’t need any cache.