Get ZMySQLDA to retrieve MySQL Stored Procedures


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.