MySQLdb Stored Procedure Out Parameter not working

Question

I have a database hosted on Google Cloud SQL, and a python script to query it.

I am trying to call a Stored Procedure that has an Out Parameter. The SP is called successfully, but the value of the Out Parameter doesn't seem to be returned to my python code.

For example, here is the example taken from here:

Definition of the multiply stored procedure:

CREATE PROCEDURE multiply(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)
BEGIN
  SET pProd := pFac1 * pFac2;
END

If I call the SP from the command line like this:

CALL multiply(5, 5, @Result)
SELECT @Result

I correctly get the result:

+---------+
| @Result |
+---------+
|      25 |
+---------+

But if I call it with python code using the MySQLdb package, like this:

args = (5, 5, 0) # 0 is to hold value of the OUT parameter pProd
result = cursor.callproc('multiply', args)
print result

then I do not get the out parameter in my result tuple:

(5, 5, 0)

So, what am I doing wrong here?

UPDATE: Just found this warning in the callproc code:

    Compatibility warning: PEP-249 specifies that any modified
    parameters must be returned. This is currently impossible
    as they are only available by storing them in a server
    variable and then retrieved by a query. Since stored
    procedures return zero or more result sets, there is no
    reliable way to get at OUT or INOUT parameters via callproc.
    The server variables are named @_procname_n, where procname
    is the parameter above and n is the position of the parameter
    (from zero). Once all result sets generated by the procedure
    have been fetched, you can issue a SELECT @_procname_0, ...
    query using .execute() to get any OUT or INOUT values.

And also note that the callproc function merely returns the same input arg tuple. So bottom line is this is not possible. Back to the drawing board then ...

Jempty

Is (5, 5, 0) the result from print result? – Gabble

yes. i was expecting the 3rd item to be the result of my out param? – Jempty

I don't know about python, but in other languages you cannot substitute a value for a by-reference variable. Also it seems that you might be better of with a function multiply(a,b) than with a procedure. – Antibiotic

well, yes but this is just a simple illustrative example. – Jempty

Since you've answered your own question (that it can't be done from Python), you should mark this as answered. – Grivation

Answer 1

All you need is an additional SELECT to access the output values:

>>> curs.callproc('multiply', (5, 5, 0))
(5, 5, 0)
>>> curs.execute('SELECT @_multiply_0, @_multiply_1, @_multiply_2')
1L
>>> curs.fetchall()
((5L, 5L, 25L),)

Ambroid

yeah, thats what i've ended up rolling with. it's not ideal but it works i guess – Jempty

Answer 2

Check This, Just Remember to setup database connection just to MYSQL database initialization and try something like:

Just to for know what about talks, the database table definition:

CREATE TABLE table_tmp
(
    data1 INT(11),
    data2 VARCHAR(10),
    data3 TINYINT(1)  -- This will be the output value
);

Definition of the database Procedure:

DROP PROCEDURE IF EXISTS sp_test_tmp;
CREATE DEFINER=`<user_in_the_db>`@`%` PROCEDURE `sp_test_tmp`(

      IN in_data1   INT
    , IN in_data2   VARCHAR(10)
    , IN in_data3   BOOL
    , OUT result    BOOL
)
BEGIN
    INSERT INTO table_tmp
    (
         data1
        ,data2
        ,data3
    )
    VALUES 
    (
        in_data1
        ,in_data2
        ,in_data3
    );
  SET result = FALSE;  -- Setting the output to our desired value
  COMMIT;  -- This will help to update the changes in the database, with variable
           -- the row never will get updated (the select/get a little 
           -- complex less)
END;

Python Code Using a list of parameters, I am thinking in generic function ;)

TRUE = 1   -- My own definition, for make compatible Mysql and Python Boolean data representation
FALSE = 0

def execute_procedure(pname='sp_test_tmp',pargs=(1,'[email protected]',TRUE,FALSE)):
    try:
        cursor = mysql.connect().cursor()
        status = cursor.callproc(pname, pargs)
        cursor.execute('SELECT @_sp_test_tmp_3') # This is the magic
        result = cursor.fetchone()               # Get the Values from server

        if result[0] == TRUE:
           print ("The result is TRUE")
           resp = True
        elif result[0] == FALSE:
           resp = False
           print("The result is FALSE")
       else:
          resp = False
          print("This is crazy!!!")

       return str(resp)
       except Exception as inst:
         exception = type(inst)
         print(exception)
         return "DON'T"
       finally:
         cursor.close()

Minelayer

not a magic. it's fxx sxx! but thanks. – Exosphere