[Zope-dev] ZSybaseDA 1.0.1 and MS-SQL (was Re: ANN: ZSybaseDA 1.0.1)

[Anthony Baxter]

Update 2: The following patch makes the ZSybaseDA stop using the 
cursor() of the ctsybase function. This has one adverse effect that
I'm aware of:

With a cursor, a large query will only fetch max_rows (as defined by the
SQL Method) of the result. Without it, it will get all results, then simply
truncate the results to max_rows.

And one good effect:

It works again against MS SQL. 

Ideally, this should be an option you can choose on a per-DA instance basis,
but I gave up trying to untangle the Shared.DC.Connection code enough that I
could have an option for only one type of DA. 

The other approach would be to dive into the Sybase docs to try and find
out what magic is needed to make cursors work against MS SQL. I'm not even
going to pretend I'm ever going to get the time to do this...

Anyway, this patch works for me. (patch also gone to Collector)

--- db.py.orig	Tue Aug 31 12:29:26 1999
+++ db.py	Tue Aug 31 12:28:40 1999
@@ -91,6 +91,12 @@
 from time import gmtime, strftime
 from Shared.DC.ZRDB.THUNK import THUNKED_TM, Surrogate
 
+# set this to 0 if you get errors like
+# ct_cursor(): user api layer: external error: The connection's
+# capabilities do not support this type of request.
+# when connecting to MS SQL or ancient Sybase servers.
+UseCursor = 0 
+
 failures=calls = 0
 
 nonselect_desc=[
@@ -162,7 +168,8 @@
 	calls=calls+1
 	db=self.db
 	try:
-	    c=self.cursor
+	    if UseCursor:
+		c=self.cursor
 	    self._register()
 	    queries=filter(None, map(strip,split(query_string, '\0')))
 	    if not queries: raise 'Query Error', 'empty query'
@@ -181,15 +188,21 @@
 	    else:
 		query_string=queries[0]
 		if select_m(query_string) >= 0:
-		    r=c.execute(query_string)
-		    result=c.fetchmany(max_rows)
-		    desc=c.description[0]
+		    if UseCursor:
+			r=c.execute(query_string)
+			result=c.fetchmany(max_rows)
+			desc=c.description[0]
+		    else:
+			result=db.execute(query_string)[0]
+			result=result[:max_rows] # gross
+			desc=db.description[0]
 		else:
 		    r=db.execute(query_string)
 		    result=[[query_string, str(`r`), calls]]
 		    desc=nonselect_desc
 	    failures=0
-	    c.close()
+	    if UseCursor:
+		c.close()
 	except self.Database_Error, v:
 	    messages=[]
 	    ertype=v[0]