[unixODBC-support] Problem in using cursor to fetch millions of rows
sharkey at netrics.com
Wed Apr 6 18:10:39 BST 2005
> I would like to know how can I use the cursor such that it returns
> me 1 row at a time? My problem is that I'm using tclodbc which is an
> odbc layer on top of tcl and it makes call to unixodbc for SQL execution.
> My table has some millions of rows, when I try the following, (sorry
> it is in tcl but it is very easy to follow) it will consume all the
> memory in my machine and force it to return error. How can I effectively
> use cursor to just ask to return 1 row at a time?
In general, you can't control this.
You can request that the driver only return a certain number of rows
from a select (if the driver supports this, and not all do) or you can
request a certain cursor size from SQLFetch, but how memory management
is done by the driver and where/when records are copied from the
datasource to the driver is completely under the control of the driver.
Some drivers will copy records when SQLFetch is called, while others
will copy the entire result set as soon as it is available. Usually
you can't change this.
If you are using PostgreSQL, then you may want to look into using the
LIMIT and OFFSET clauses in the select statement. This is not
interoperable with other databases, but it will work with PostgreSQL
to limit the result set size.
More information about the unixODBC-support