[DB-SIG] SQL statement parse for Oracle

Matthew T. Kromer matt@zope.com
Fri, 14 Dec 2001 08:51:44 -0500


For what it's worth, DCOracle2 has two ways to do that -- first, if a 
Cursor sees an identical (string eqivalent) command go through execute() 
again, it doesn't call prepare on it, since it was the last command 
prepared.  Secondly, DCOracle 1 had a cursor.prepare(statement) which 
returned a cursor with a prepared statement on it.

DCOracle2 sort-of supports this, in that it takes a prepare method on a 
db and sniffs it a bit to see if it looks like a statement, or an 
instruction to do a two-phase commit prepare.  If it looks like a 
statement, it returns a cursor with that statement prepared on it, which 
can then be passed a None statement to use the previously prepared one 
(ie None acts as a "use last statement" statement).  DCOracle 1 code 
used this to do things like

stmt = db.prepare(statement)
stmt.execute(arg1=1,arg2=2) etc

which will still function properly with DCOracle2.

Since DCOracle2 is based on OCI8 though, if you're still using an Oracle 
7 system you'd have to upgrade your client libs.

Jekabs Andrushaitis wrote:

>In the Oracle world SQL statement parsing can take considerable CPU/Block IO
>for complex statements. Well, I am using pre-historic Oracle DB module
>(looks like
>some OLD DCOracle to me:), however I did not see anything related to this in
>DBAPI too.
>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. 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...
>
>>From DB-API's point of view it could be Python methods for cursor object:
>	dbc.parse('some statement here')
>	dbc.executeparsed([some bind variables here])
>
>This can be a very useful feature for DBAPI to have, since in case of Oracle
>and
>complex statements executed many times it can make the difference visible.
>
>I have made made these patches for my OLD OLD Oracle module (sadly I have
>to stick with it for now cause of other patches weve made in it - Python DB
>connection
>object creation from embedded SQL connections, various memory leak fixes
>which
>were critical in the environment I am using this module etc:)...
>
>Maybe I am totally missing something about DBAPI spec, and if above
>described
>features do exist in the spec, then ignore this silly mail :)
>
>Also, I am not aware of RDBMS supporting parsing without executing, but
>Oracle
>certainly is one of those :)
>
>Jekabs
>
>
>_______________________________________________
>DB-SIG maillist  -  DB-SIG@python.org
>http://mail.python.org/mailman/listinfo/db-sig
>