Re: ADO Max Records and Visual Basic

-----Original Message-----
From: William235(at)aol(dot)com [mailto:William235(at)aol(dot)com]
Sent: 21 February 2002 13:48
To: dpage(at)postgresql(dot)org
Subject: ADO Max Records and Visual Basic

Dave:

Bill,

You would benefit more from posting your questions to the
pgsql-odbc(at)postgresql(dot)org <mailto:pgsql-odbc(at)postgresql(dot)org> mailling list
- there are lots of people there that may be able to help you more than me.

I am having difficulty with VB caching too many records from a PostgresQL
database when I open a recordset. I am using serverside cursors. When I
establish the connection and open the recordset using

datRS.Open "In_Data", cnPressData, adOpenStatic, adCmdTable, adUseServer

If I remember correctly, server side cursors are not yet implemented in the
ODBC driver, so you will actually be running client side.

the command immediately retrieves over 400MB of data from the database.
This uses up all available physical and virtual memory and ultimately times
out. I have tried MaxRecords, CacheSize, at low settings but neither
command affects how many records are initially brought over. Ultimately my
program will fail as the size of the database grows.

Please note that I am using this software only to write to the database. It
does not re-read any of the data, it merely gathers it and forwards it to
the database. I am using ODBC driver ver. no. psqlODBC-07-01-0008

If you are only inserting data, then why not try something like:

datRS.Open "SELECT * FROM " & Chr(34) & "In_Data" & Chr(34) & " WHERE 1 =
2", cnPressData, adOpenStatic, adCmdText, adUseServer

That will open a recordset that will always be empty so you can just insert
data (the Chr(34)'s are required to quote your mixed case tablename).
Alternatively, if you don't actually need the recordset object for binding
or whatever, you could just throw INSERTs at the database:

cnPressData.Execute "INSERT INTO " & Chr(34) & "In_Data" & Chr(34) & "
(col1, col2, col3) VALUES (val1, val2, val3)"

Regards, Dave.