[unixODBC-support] Problem in using cursor to fetch millions of rows

Jerry Lam jlam at sandvine.com
Wed Apr 6 19:37:32 BST 2005


I'm very appreciated the help here. I read the source code in the execute method, the line that is suspicious to me in the void TclSqlStatement::Execute(Tcl_Interp *interp, int objc, TCL_CMDARGS) is this:

while ((rc = SQLExecute(stmt)) == SQL_STILL_EXECUTING) SqlWait(1);

Although I don't understand (or no clue) on how the unixODBC works since this is my first day of trying to get a touch on ODBC, well I'm just purely guessing. The tclodbc driver has stopped the development since 2000, so I'm not surprise that it has bugs and incompleted. 

Thank you,


-----Original Message-----
From: unixodbc-support-bounces at easysoft.com
[mailto:unixodbc-support-bounces at easysoft.com]On Behalf Of
martin.evans at easysoft.com
Sent: Wednesday, April 06, 2005 2:11 PM
To: Support for the unixODBC project
Subject: Re: [unixODBC-support] Problem in using cursor to
fetchmillionsof rows 

I suspect the problem is the execute method in tclodbc retrieves all
the rows in one go i.e. repeatedly calls SQLFetch for each row (then
SQLGetData for each column) until SQLFetch returns SQL_NO_DATA. There
is probably another method in tclodbc which you can use to retrieve
one row at a time.

A quick look at the docs for tclodbc:

execute ?args?

      Method execute executes the given statement, but does not return the
result set. If the statement returns a result set, rows may be read one by one
with method fetch.

fetch ?arrayName? ?columnNames?
Method fetch is used for reading one row from the result set at a time after
executing it by method execute.


Quoting Eric Sharkey <sharkey at netrics.com>:

> > Thanks for the reply. I checked back the release date of the tclodbc, it
> was 
> > dated as 2000-12-10 14:00 in sourceforge. Then I checked the version of
> unixo
> > dbc that I'm using is unixODBC-2.2.7_005. The first version of unixODBC is
> da
> > ted on 1999, do you think that explains why tclodbc doesn't allow to cursor
> t
> > hrough row by row because maybe the early version of unixODBC doesn't have
> th
> > at feature yet?
> Just to clarify the point I tried to make earlier:
>   The unixODBC Driver Manager will not consume gobs of memory or cache
>   data rows.
>   The ODBC driver for the database you are using may consume gobs of
>   memory and cache data rows.
> You've said you're using the PostgreSQL driver, but you haven't mentioned
> the version number of that driver or of the db.
> I don't know much about the internals of the PostgreSQL driver or how
> it manages memory, but you may want to consult the authors of the driver
> or examine the source for yourself.  Maybe someone else on this list can
> comment on this.
> Eric
> _______________________________________________
> unixODBC-support mailing list
> unixODBC-support at easysoft.com
> http://mail.easysoft.com/mailman/listinfo/unixodbc-support

unixODBC-support mailing list
unixODBC-support at easysoft.com

More information about the unixODBC-support mailing list