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" Does ZOPE support this kind of procedureal access to the Oracle DB?. Pls advice. Martin van Nijnatten
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". Hopefully this gives some insight. Chris -- | Christopher Petrilli Python Powered Digital Creations, Inc. | petrilli@digicool.com http://www.digicool.com
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 )
participants (3)
-
Christopher Petrilli -
Martin van Nijnatten -
Nitin Borwankar