[Zope-DB] Mixing ZSQLMethods with ZOracleProcedures: Problem and Patch

[Willi Langenberger]

Hi!

We encountered a problem when we use a ZOracleProcedure and a
ZSQLMethod in the same script. Sometime it happens, that one of them
doesn't see the changes from the other one. Example:

add_result_py:
  store_result_sp       #oracle stored procedure, stores values in DB
  calculate_mean_sql    #z sql method, doesn't see these values

(both objects have the same z oracle db connection object).

The reason for this seems to be the differnt way these objects get the
low level database connection (db):

- ZSQLMethod: gets the db object on every call from the zope "database
  connection object"

- ZOracleProcedure: gets the db object from the zope "database
  connection object" on the first call, but caches it then (in _v_db)

Is this possible? The following patch seems to solve the problem for us
(however, its difficult to debug...). Comments?


\wlang{}

-- 
Willi.Langenberger at wu-wien.ac.at                Fax: +43/1/31336/9207
Zentrum fuer Informatikdienste, Wirtschaftsuniversitaet Wien, Austria

-----included file follows-----

--- SP.py.ori	2003-12-06 23:02:40.000000000 +0100
+++ SP.py	2003-12-06 23:02:26.000000000 +0100
@@ -130,25 +130,22 @@
         self.connection = connection
         self.procname = procname
         self.acquire = acquire
-        self._v_proc = None
-        self._v_db = None
-
     
     def _connect(self):
-        db = self._v_db = getattr(self,self.connection)() # Get connection
+        db = getattr(self,self.connection)() # Get connection
         c = db.cursor                          # Get cursor
         self._v_errors = 0
         if self.procname:
             try:
-                p = c.findproc(self.procname)        # Get procedure
-                self._v_proc = p
+                proc = c.findproc(self.procname)        # Get procedure
             except DCOracle2.DatabaseError:
                 self._v_errors = self._v_errors + 1
                 if self._v_errors > 3: raise
-                
+        return db, proc
+
     def description(self):
-        if getattr(self,"_v_proc",None) is None: self._connect()
-        return self._v_proc.__doc__
+        db, proc = self._connect()
+        return proc.__doc__
 
     def ZSPAcquireType(self):
         aq = getattr(self,"acquire","never")
@@ -158,7 +155,8 @@
 
     def ZSPListPackage(self, package):
 
-        db = self._v_db.db
+        db, proc = self._connect()
+        db = db.db
 
         cd = db.collapsedesc(db.describe(package))
 
@@ -177,9 +175,9 @@
 
         results = []
 
-        if getattr(self,"_v_db",None) is None: self._connect()
+        db, proc = self._connect()
 
-        objs = self._v_db.db.objects()
+        objs = db.db.objects()
 
         for (name, type) in objs:
             if type in ('PROCEDURE', 'FUNCTION'):
@@ -198,7 +196,7 @@
             # Ugh raising a string error
             raise 'Unauthorized', "You are not authorized to access this resource"
 
-        if getattr(self,"_v_proc",None) is None: self._connect()
+        db, proc = self._connect()
 
         aq = self.ZSPAcquireType()
 
@@ -212,7 +210,7 @@
             argc = 0
             argl = len(args)
             _notfound = []
-            arglist = self._v_proc.argList()
+            arglist = proc.argList()
             for (name, type, mode) in arglist:
                 if 'IN' in mode:
                     argc = argc + 1
@@ -228,16 +226,13 @@
                             kw[name] = v   # Auto-import as kw arg
 
         # register DB object for commit at the end of the zope transaction
-        self._v_db._register()
-        try:
-            # Note, this does not do result promotion like the DA query will
-            # which is probably bad.  OracleDates in particular look like
-            # DateTimes but arent at all the same!
-            results = apply(self._v_proc,args,kw)
-            return self._lobConvert(results)
-        except:
-            self._v_proc = None             
-            raise                          # Reraise error
+        db._register()
+
+        # Note, this does not do result promotion like the DA query will
+        # which is probably bad.  OracleDates in particular look like
+        # DateTimes but arent at all the same!
+        results = apply(proc,args,kw)
+        return self._lobConvert(results)
 
     def invars(self):
         d = self.description()
@@ -256,7 +251,6 @@
         self.connection = connection
         self.procname = procname or procname2
         self.acquire = acquire
-        self._v_proc = None
         RESPONSE.redirect(URL1 + "/manage_procview?manage_tabs_message=Stored%20Procedure%20Changed")
 
     def manage_testZOracleStoredProcedure(self, REQUEST):