Calling Stored Procedure in MySQL with output parameter

[Stephen Ray]

My environment is Python 3.2, SQLAlchemy 0.8, MySQL 5.5, and using MySQL-connector 1.0.9.

 

I have a stored procedure that takes two input parameters (both integers) and returns a single integer output parameter indicating the success of failure of the stored procedure. No record sets are returned by the stored procedure, its essentially part of an ETL process that loads from staging tables. All I need to know is the return status contained in the single output parameter to know whether the load was successful or not.

 

I've trawled the web for good examples and seen solutions using func objects, text objects, and calling a constructed string directly. Which would be the best approach to use in this situation? I would like something as DB agnostic as possible so I tried the func approach first but this seemed to be treating the SQL object as a MySQL Function rather than a MySQL Stored Procedure.

 

Thanks in advance.

 

Stephen Ray

[Michael Bayer]

to my knowledge, the existing DBAPIs for MySQL don't support output parameters (news to me that MySQL SPs did).    But I haven't confirmed that.  You'd need to figure out first how to do this with the plain DBAPI cursor, such as that of MySQL-python.   Within SQLAlchemy for now you'd probably need to use the DBAPI connection directly from an Engine or a Connection and then manipulate the cursor directly.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Stephen Ray]

Michael,

 

Thanks for the quick reply.

 

As a workaround I wrapped the stored procedure in another stored procedure that selects the return status. Now I am getting an 'Unread results exception' when I execute.

 

Here is the code fragment (where self._engine is a SQLAlchemy engine):

t = text('CALL myproc(:in1, :in2);', bindparams=[bindparam('in1', type_=Integer, value=1), bindparam('in2', type_=Integer, value=2)])

conn = self._engine.connect() 

result = conn.execute(t) 

 The 'Unread results" exception is thrown by the last line.

 

Is it possible to read a result set from a stored procedure through SQLALchemy or do I need to drop down to DBAPI cursor level (which as you can see I am trying to avoid).

 

Steve R

[Michael Bayer]

yeah I've no idea, would need to get it working with raw cursor first to even know how this should be treated.

[Stephen Ray]

Michael,

 

As you suggested, I grabbed the underlying dbapi connection from the the sqlalchemy connection and was able to get the result back in a somewhat round about way (in the code that follows conn is a SQLAlchemy connection):

 

mysqlconn = conn.connection

cursor = mysqlconn.cursor()

args=(1,2)

cusor.callproc('myproc', args)

 

status = None

for result in cursor.stored_results():

status = result.fetchone()[0]

break 

 According to the MySQL Connector documentation for callproc (http://dev.mysql.com/doc/connector-python/en/myconnpy_MySQLCursor_callproc.html) I should be able to call my original stored procedure passing a tuple of 3 arguments, the last one being a placeholder for the output parameter. After the call to callproc it should be filled in with the output parameter. However, this didn't work which is why I had to go with looking into the returned result set.

 

Thanks for your help!

 

Steve R