#201 Problems with callproc()

MySQLdb-1.2
closed
MySQLdb (285)
5
2014-08-18
2006-08-16
No

Hi Andy!

I'm having some problems using the callproc() in
MySQLdb 1.2.1_p2. Actually I'm not able to get it
going unless i make a minor change in cursors.py.

I'm using this simple script:

mysqltest.py

import MySQLdb

Create a connection object and create a cursor

Con = MySQLdb.Connect(host="localhost", port=3306,
user="root", passwd="[your_pwd_here", db="test")
Cursor = Con.cursor()

Use callproc to execute a sproc

Cursor.callproc("sp_GetPerson_r")

Fetch all results from the cursor into a sequence

Results = Cursor.fetchall()

Closing the cursor - to avoid errmsg from that extra

resultset returned
Cursor.close()

Close the connection

Con.close()

Print the sequence

print Results

mysqltest.py

As you can see it has some prerequisites regarding
the MySQL, so I've attached a script creating the
database 'Test' complete with the needed table, sproc
and 3 lines of data.

If I run the script on a local MySQL (beware: It will
overwrite any database called 'Test') and run the .py-
script I get: ProgrammingError "execute() first".

I think it might be a problem with the setting of
self._executed in cursors.py - but I'm far from sure.
My guess is that the _executed should be set to the
query just executed and this just isn't in the
current cursors.py as far as I can see.

Two ways of fixing it (and maybe causing mayhem
elsewhere?):
1) change the only occurence of '_last_executed'
to '_executed' in cursors.py(_do_query)
2) insert 'self._executed = q' before
the 'self._warning_check()' in cursors.py(callproc)

As far as I can tell we have to possibilites here -
either I'm missing something important and the
MySQLdb 1.2.1_p2 has a fully functional callproc()
method, OR it's a bug. Which one is it? :)

Thx for your effort so far (creating MySQLdb - and
reading this ;))

Ulrik

Discussion

  • Ulrik Thoerner

    Ulrik Thoerner - 2006-08-16

    Backup of MySQL-db Test. Caution if you already have a db named Test ;)

     
  • CADM

    CADM - 2006-08-22

    Logged In: YES
    user_id=1581345

    It's a bug which I verified too, and I suggest to follow
    the second fix, which is similar to the work done in
    cursor.execute().

     
  • CADM

    CADM - 2006-08-22

    Logged In: YES
    user_id=1581345

    def callproc(self, procname, args=()):
    
        """Execute stored procedure procname with args
    
        procname -- string, name of procedure to execute
    

    on server

        args -- Sequence of parameters to use with
    

    procedure

        Returns the original args.
    
        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.

        Compatibility warning: The act of calling a stored
    

    procedure
    itself creates an empty result set. This appears
    after any
    result sets generated by the procedure. This is
    non-standard
    behavior with respect to the DB-API. Be sure to
    use nextset()
    to advance through all result sets; otherwise you
    may get
    disconnected.
    """

        from types import UnicodeType
        db = self._get_db()
        charset = db.character_set_name()
        q = ''
        for index, arg in enumerate(args):
            q += "SET @_%s_%d=%s;" % (procname,
    

    index,
    db.literal(arg))
    if type(q) is UnicodeType:
    q = q.encode(charset)
    self._query(q)
    while self.nextset(): pass

        q = "CALL %s(%s)" % (procname,
                             ','.join(['@_%s_%d' % (procname, i)
                                       for i in range(len(args))]))
        if type(q) is UnicodeType:
            q = q.encode(charset)
        self._query(q)
        self._executed = q
        self._warning_check()
        return args
    

    Its's mine fixed callproc().

    I changed the mysql variable construction, too, which
    is a little optimization, because the server will be
    called only once for all parameters, and all "result
    sets" will be trashed out.

    The code is tested and works fine.

     
  • Andy Dustman

    Andy Dustman - 2006-08-26

    Logged In: YES
    user_id=71372

    Fixed in development version; please test 1.2.2b1 (not
    released yet; watch mysql-python-test releases).

     

Log in to post a comment.