Cristian Gafton wrote: > On Wed, 6 Sep 2006, M.-A. Lemburg wrote: > >> If the database drivers don't provide a mechanism to pass in >> statements and parameters separately, that's a possible way to >> implement bound parameters. > > But the database drivers in most cases *do* provide such a mechanism. I > tend to blame the DB API's lack of clear specification on how to handle > bind parameters that has made some take the "easy" way out.
I suppose that the authors who did had good reasons in doing so. mxODBC certainly doesn't use that approach, but some ODBC drivers do because the wire protocols don't provide a way to separate the statement from the parameters. >>> Because it is only the programmer that knows "I am expecting 1 million >>> rows out of this query, you'd better now load it all up in RAM at once" >> Right, but in that case, the programmer would just do a .fetchall(), >> so the interface can infer this from the type of .fetchxxx() method. > > You're missing the point. Usually a programmer does something like: > cursor.execute(...) > cursor.fetchXXX() > > The problem is that in some cases the entire result set is downloaded in > the client RAM before returning from the *execute* call. the fetchXXX > calls come too late to infer anything. You have to know before the execute > if you want to open up a server side cursor or you want the execute call > to return and malloc a whole bunch of memory on your local stack. Sounds rather specific to a certain database backend. Most databases we work with (MS SQL Server, Oracle, SAP/Max DB, Sybase, DB2 to name a few) tend to postpone execution and sending of the results until the very last moment. In database applications you rarely want huge result sets in one go. You typically try to read in the data in chunks where the actual fetching of the chunks is done using multiple SQL statements, keeping the complete result set on the server side and only transferring the data you need. >>> Not all database drivers are rich enough, or smart enough, or sufficiently >>> envolved (MySQL and PostgreSQL are such examples); you either retrieve all >>> results at once at a cost of client memory or you retrieve in chunks using >>> FETCH at the cost of speed. Again, it is the application programmer that >>> knows which is appropiate for which case. >> Maybe I'm missing something, but doesn't the programmer let the >> database module know by using either .fetchmany() or >> .fetchall() ?! > > It doesn't. The C level APIs of the databases are written in such a way > that at the end of the low level on-the-wire execute() call you are making > you get returned the entire result set. There is nothing fetchXXX can do > to help you there. If that's the case for PostgreSQL, perhaps you need to add a non-standard method to choose the fetch strategy before doing the .executexxx() ?! >> Database drivers normally do not fetch any rows from a result set >> until you actually make a call to do so. In some cases, they don't >> even execute the SQL statement until you do. > > You've probably been spoiled by Oracle... Not really :-) I've worked with many ODBC drivers during the last few years and some have exhibited really funny behavior, e.g. I remember MS SQL Server once complaining about a syntax error when querying the number of rows in the result set - not during the prepare step of the statement where you would normally expect this to happen. -- Marc-Andre Lemburg eGenix.com Professional Python Services directly from the Source (#1, Sep 06 2006) >>> Python/Zope Consulting and Support ... http://www.egenix.com/ >>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/ >>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/ ________________________________________________________________________ ::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! :::: _______________________________________________ DB-SIG maillist - DB-SIG@python.org http://mail.python.org/mailman/listinfo/db-sig
