[DB-SIG] SQL statement parse for Oracle

[M.-A. Lemburg]

Jekabs Andrushaitis wrote:
> 
> In the Oracle world SQL statement parsing can take considerable CPU/Block IO
> for complex statements. 
> ...
> What I am talking about is API to allow to parse statement for a cursor (for
> Oracle
> OCI it is done by 'oparse' calls) without executing it. It could be used to
> speed
> up things in situation where same statement has to be executed several times
> with different bind variables for example.

This is already possible using the .execute() command cache which
is described in the DB API.

> Or when you want application uses
> persistand DB connection and you can create several cursors for each
> operation
> you need to perform thus eliminating cost of parsing each statement over and
> over
> again...

See above.

The only problem is that you have to execute the statement at least 
once to get it prepared on the cursor. This isn't much of a problem
though if you wrap the DB API cursor objects in more elaborate
Python objects (for abstraction purposes or to extend them with
new features you need).
 
The reason for not putting this into the DB API is that some
DBs may not be able to separate the prepare and the execute
steps, e.g. some ODBC drivers only pretend to prepare the
statement -- the actual parsing etc. still takes place at
.execute() time.

Perhaps I should add a .prepare() method to the set of standard
DB API extensions though ?!

Here's what I have in mxODBC:

   .prepare(operation)

        Prepare a database operation (query or command) statement for
        later execution and set cursor.command.
        To execute a prepared statement, pass
        cursor.statement to one of the .executeXXX() methods.
        Return values are not defined.

and

   .command
   
        Provides access to the last prepared or executed SQL command
        available through the cursor. If no such command is available,
        None is returned.

-- 
Marc-Andre Lemburg
CEO eGenix.com Software GmbH
______________________________________________________________________
Consulting & Company:                           http://www.egenix.com/
Python Software:                        http://www.lemburg.com/python/