[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
>