Quick, before I forget!!
Tired of “can’t return a result set in the given context” errors when calling stored procedures in Mysql 5?
The ultra fast how-to on returning records from a multi-column multi-record stored procedure in Mysql 5 using Zope/Python!
In the ZMySQLDA, edit the db.py file and add the lines below in the _parse_connection_string function:
#added by Greg Fischer retheoff at gmaildot com on 07/05/2005
# Wanted an option to use 'client_flag' in connection string
# to utilize MULTI_RESULTS and thereby retrieving full
# recordsets from Mysql 5.x
# There is most likely a better way to implement this,
# but this solved my isssue for now. (what if you have
# more than 1 client_flag?)
if not items: return kwargs
kwargs['client_flag'], items = int(items[0]), items[1:]
# End addition
Like so…
def _parse_connection_string(self, connection): kwargs = {'conv': self.conv} items = split(connection) self._use_TM = None if not items: return kwargs lockreq, items = items[0], items[1:] if lockreq[0] == "*": self._mysql_lock = lockreq[1:] db_host, items = items[0], items[1:] self._use_TM = 1 else: self._mysql_lock = None db_host = lockreq if '@' in db_host: db, host = split(db_host,'@',1) kwargs['db'] = db if ':' in host: host, port = split(host,':',1) kwargs['port'] = int(port) kwargs['host'] = host else: kwargs['db'] = db_host if kwargs['db'] and kwargs['db'][0] in ('+', '-'): self._try_transactions = kwargs['db'][0] kwargs['db'] = kwargs['db'][1:] else: self._try_transactions = None if not kwargs['db']: del kwargs['db'] if not items: return kwargs kwargs['user'], items = items[0], items[1:] if not items: return kwargs kwargs['passwd'], items = items[0], items[1:] #added by Greg Fischer retheoff at gmaildot com on 07/05/2005 # Wanted an option to use 'client_flag' in connection string # to utilize MULTI_RESULTS and thereby retrieving full # recordsets from Mysql 5.x # There is most likely a better way to implement this, # but this solved my isssue for now. (what if you have # more than 1 client_flag?) if not items: return kwargs kwargs['client_flag'], items = int(items[0]), items[1:] # End addition if not items: return kwargs kwargs['unix_socket'], items = items[0], items[1:] return kwargs
Now, when you create your database connection object in Zope to Mysql 5, you use a slightly different connection string:
db user passwd client_flag
The client_flag is 131072 as specified in the CLIENT.py of MySQLdb for Python. (it didnt work for me to just use the constant MULTI_RESULTS.)
It will look like this:
adatabase auser apassword 131072
On Linux, this worked great. I cant get it to work on Windows. Probably need a newer version of _mysql.c from the mysql-python developers. (just a guess)
In Python, if you just want to connect, use the client_flag option in your connection string: (watch the wrapped lines)
cn = MySQLdb.Connection(db=adb, host=ahost,
user=auser, passwd=apw, client_flag=131072)
Worked great, again on Linux! But on Windows, I still get the “can’t return a result set in the given context” error unfortunately. If I find out how to get around that, I’ll post more.
This is a QUICK FIX! It is in know way the proper solution and will most likely cause some other problems, but it works and I needed the sprocs on Mysql! So, use at your own risk. And good luck!
Edit 7/06/05 2PM:
This *was* working. I ran in to a problem after creating a more complicated procedure using multiple statements. I then received an error in Zope stating something about releasing a lock. ?? I duno. I’ll test it in Python directly and see if it’s just my corny DA fix.
Edit 7/06/05 5PM:
Ok, it works, but I need to do more testing. You NEED TO USE A DASH in the connection string. Like so:
–adatabase auser apassword 65536
Also notice I am using 65536 as the client_flag. Using this specifies MULTI_STATEMENTS, and implies MULTI_RESULTS.