[Zope] Zope stored proc access was Re: storyserver database access

[Nitin Borwankar]

Christopher Petrilli wrote:
> 
> On 12/9/99 3:57 AM, Martin van Nijnatten at euroibc@solair1.inter.NL.net
> wrote:
> 
> > I have been told:
> > "As you know Vignette Story Server uses TCL/Ticle as its Proprietary
> > Scripting Language & which allows
> > PL/SQL Procedure Calls to the Oracle DB, Since the latest version of
> > PL/SQL supports the Virtual
> > Table/Row manipulation, which reduces the I/O time consumtion & deadlock
> > Situations, also PL/SQL
> > procedure allows higher volume of data manipulation with in the Cursors
> > itself"
> 
> I honestly know *nothing* about using the virtual table/row manipulations in
> Oracle 8 (nor much of the other wizz-bang-new-features), however I can
> address our plans for dealing with stored procedures.
> 
> First, a bit of background.
> 
> Stored procedures (in all modern databases) are implemented in a superset of
> SQL that provides for more functionality.  SQL99 calls these "SQL-Invoked
> Routines".  One of he benefits of most implementations (but not necessarily
> all) is that they have gone through the parse and query optimizer machinery,
> which is what consumes as much as 90% of the resources in a query.
> 
> So the primary benefit of using stored procedures is that you can reduce
> your query time by a huge amount.  Also, it lets you focus controls across
> applications.  Some environments *only* allow access via stored procedures
> so as to provide even more complex security restrictions.
> 
> So why doesn't Zope handle it today?
> 
> Well, mostly it has to do with the fact that stored procedures (until SQL99)
> were not standardized.  They're still not in reality.  This creates a
> complex problem, and one that many people have simply avoided.  This is
> changing however.
> 
> What are we planning?
> 
> Well, since SQL-related things are my domain, and we've done a lot of
> thinking about this, here's basically where we intend to go when we have the
> resources/customer requirement.  We'd obviously entertain other people doing
> this for us :-)
> 
> What we have in mind is something like a "SQL Stored Procedure Method" (yes
> I know the replication of terms is difficult, we'll find some better name
> I'm sure).  What it provides is a SQL Method-esque interface to stored
> procedures, with the same kind of control and results.  You would define the
> name of the stored procedure, and it would introspect into the database to
> provide in/out parameters (there's a difference).  It might then provide an
> interface to bind parameters from one namespace to another.  This is not as
> easy as it looks, but is worth the effort to automate.
> 
> At this point, you would be able to call them as with any other method.
> 
> What's it going to take?
> 
> Well, it means coming up with an abstract (SQL99 derived) interface that
> works with all major databases that provide SPs.  Once this is done, we need
> an interface to introspect into the database.  This becomes part of the DA
> API.  Once that's done, it's a "simple matter of programming".

Will it help to see how JDBC does this (especially the vendor specific
calls
for metadata) and essentially duplicate in Python ?
If so the JDBC sources would be helpful.

Nitin Borwankar



> 
> Hopefully this gives some insight.
> Chris
> --
> | Christopher Petrilli        Python Powered        Digital Creations, Inc.
> | petrilli@digicool.com                             http://www.digicool.com
> 
> _______________________________________________
> Zope maillist  -  Zope@zope.org
> http://lists.zope.org/mailman/listinfo/zope
>           No cross posts or HTML encoding!
> (Related lists -
>  http://lists.zope.org/mailman/listinfo/zope-announce
>  http://lists.zope.org/mailman/listinfo/zope-dev )