Mailing List Archive: Zope: DB

[SOLVED]: ZSQL meyhod fr an Oracle Stored Procedure that Returns a ref_cur

Index | Next | Previous | View Threaded

banaouas.medialog at wanadoo

Nov 18, 2007, 11:33 PM

Post #1 of 4 (484 views)
Permalink

[SOLVED]: ZSQL meyhod fr an Oracle Stored Procedure that Returns a ref_cur

solution from zope-db archives was pointed to me by Maciej Wisniowski :
http://www.nabble.com/Re:-RE-:-How-lunch-SQL-request-from-python-script---t3039608.html

and here is the final implementation:

1-add a Z cxOracle Database Connection (myconnection) via pmi

2-add a python script for external method in Plone\Data\Extensions :
# myscript.py
def mymethod(self , somename):
conn = self.myconnection()
curext = conn.db.cursor()
curint = conn.db.cursor()
sql = "begin MY_PACKAGE.MY_PROC(:p_Cursor, :p_NAME); end;"
curext.execute(sql, (curint, somename))
data = TransformCursorIntoReadableText(curint)
return data

3-add an external method :
Id=mymethod
Module name=myscript
Function Name=mymethod

This external method can be called from a "Script (python)".

banaouas a écrit :
> Hi,
> I'm starting to query an oracle 10g XE database from zope using ZSQL.
> I installed cx_Oracle-4.3.3-10g.win32-py2.4.exe and
ZcxOracleDA-0.5.tar.gz.
> It works fine for simple queries.
> But how can I query a stored procedure returning a cursor ?
> In python script, this sample works fine:
> con = cx_Oracle.connect("my_user", "my_pass", "my_server")
> cur = con.cursor()
> sql = "begin MY_PACKAGE.MY_PROC(:p_Cursor, :p_NAME); end;"
> cursor.execute(sql, (cur, 'DUPONT%'))
> ...
> con.commit()
>
> thanks
>

_______________________________________________
Zope-DB mailing list
Zope-DB[at]zope.org
http://mail.zope.org/mailman/listinfo/zope-db

wlang at wu-wien

Nov 19, 2007, 12:34 AM

Post #2 of 4 (464 views)
Permalink

Re: [SOLVED]: ZSQL meyhod fr an Oracle Stored Procedure that Returns a ref_cur [In reply to]

According to m.banaouas:
> 2-add a python script for external method in Plone\Data\Extensions :
> # myscript.py
> def mymethod(self , somename):
> conn = self.myconnection()
> curext = conn.db.cursor()
> curint = conn.db.cursor()
> sql = "begin MY_PACKAGE.MY_PROC(:p_Cursor, :p_NAME); end;"
> curext.execute(sql, (curint, somename))
> data = TransformCursorIntoReadableText(curint)
> return data

If you are writing to a transactional database, you should add

conn._register()

to register that connection for the zope transaction
machinery. That way the Zope Publisher calls "commit" at the end of a
successful request (or "abort" on error).

\wlang{}

--
Willi.Langenberger[at]wu-wien.ac.at Fax: +43/1/31336/9207
Zentrum fuer Informatikdienste, Wirtschaftsuniversitaet Wien, Austria
_______________________________________________
Zope-DB mailing list
Zope-DB[at]zope.org
http://mail.zope.org/mailman/listinfo/zope-db

banaouas.medialog at wanadoo

Nov 19, 2007, 12:56 AM

Post #3 of 4 (461 views)
Permalink

Re: [SOLVED]: ZSQL meyhod fr an Oracle Stored Procedure that Returns a ref_cur [In reply to]

Initially, I needed to access to this oracle database only for read
operations.
But even for read only access, it might be a good practice to add this
commit, cause sometime database engins hold some kind of lock on the read
data, depending on how transaction was started.

I imagine that zsql class do implicitely this commit ?

In other part, it would be usefull to "separate" two operations , data
quering and result container building in
the ZcxOracleDA.query method. So, we can "format" any cursor with the same
structure if the (simple) statement was processed directly by .query method.

Willi Langenberger wrote:
>
> According to m.banaouas:
>> 2-add a python script for external method in Plone\Data\Extensions :
>> # myscript.py
>> def mymethod(self , somename):
>> conn = self.myconnection()
>> curext = conn.db.cursor()
>> curint = conn.db.cursor()
>> sql = "begin MY_PACKAGE.MY_PROC(:p_Cursor, :p_NAME); end;"
>> curext.execute(sql, (curint, somename))
>> data = TransformCursorIntoReadableText(curint)
>> return data
>
> If you are writing to a transactional database, you should add
>
> conn._register()
>
> to register that connection for the zope transaction
> machinery. That way the Zope Publisher calls "commit" at the end of a
> successful request (or "abort" on error).
>
>
> \wlang{}
>
> --
> Willi.Langenberger[at]wu-wien.ac.at Fax: +43/1/31336/9207
> Zentrum fuer Informatikdienste, Wirtschaftsuniversitaet Wien, Austria
> _______________________________________________
> Zope-DB mailing list
> Zope-DB[at]zope.org
> http://mail.zope.org/mailman/listinfo/zope-db
>
>

--
View this message in context: http://www.nabble.com/ZSQL-meyhod-fr-an-Oracle-Stored-Procedure-that-Returns-a-ref_cur-tf4818086.html#a13829942
Sent from the Zope - DB mailing list archive at Nabble.com.

_______________________________________________
Zope-DB mailing list
Zope-DB[at]zope.org
http://mail.zope.org/mailman/listinfo/zope-db

wlang at wu-wien

Nov 19, 2007, 4:22 AM

Post #4 of 4 (464 views)
Permalink

Re: [SOLVED]: ZSQL meyhod fr an Oracle Stored Procedure that Returns a ref_cur [In reply to]

According to banaouas:
> > If you are writing to a transactional database, you should add
> >
> > conn._register()
> >
> > to register that connection for the zope transaction
> > machinery. That way the Zope Publisher calls "commit" at the end of a
> > successful request (or "abort" on error).
>
> Initially, I needed to access to this oracle database only for read
> operations.
> But even for read only access, it might be a good practice to add this
> commit, cause sometime database engins hold some kind of lock on the read
> data, depending on how transaction was started.

It might be a good practice, because you never know, if your
application will eventually also be writing to the DB. I don't know if
it has an effect on read only operations.

> I imagine that zsql class do implicitely this commit ?

Strictly speaking its no "commit", its a "registration for
commit/abort". And yes, for ZSQL methods this is done implicitly (via
the "query" method of the DB class).

\wlang{}

--
Willi.Langenberger[at]wu-wien.ac.at Fax: +43/1/31336/9207
Zentrum fuer Informatikdienste, Wirtschaftsuniversitaet Wien, Austria
_______________________________________________
Zope-DB mailing list
Zope-DB[at]zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact lists@gossamer-threads.com