[PyDB2-discuss] Calling stored procedures

[Dan S. <de...@gm...>]

Hi:

I noticed there is no documentation in the README about stored
procedure support, so I went ahead to try it out according to the
interface defined by PEP249.

Env: DB2 V8.2, Windows XP, Python 2.3, pyDB2 0.998a installed from
Man-Yong Lee's binary.

I started by creating a simple SQL stored procedure to test the INOUT
parameter support:

    CREATE PROCEDURE test_date(INOUT date DATE)
    BEGIN
      SET date = '2007-01-05';
    END

... then called it from the following method:

def call_procedure_inout(conn):
    """
    Call a procedure that accepts one INOUT DATE parameter.
    """
    
    curs=conn.cursor()
    stmt = 'TEST_DATE'
    parm = ('2004-12-02')
#    parm = ('10/27/1991')
    res = curs.callproc(stmt, parm)
    return res

The expected return value would be '2007-01-05', but I get an error instead:

*****
  File "C:\local\python2.3\Lib\site-packages\DB2.py", line 438, in callproc
    r = self._cs.callproc(procname.upper(), t_args)
_db2.error: 

SQLSTATE : 22007, NATIVE ERROR CODE : -99999

[IBM][CLI Driver] CLI0113E  SQLSTATE 22007: An invalid datetime format
was detected; that is, an invalid string representation or value was
specified. SQLSTATE=22007
*****

As you can see, I've tried a couple of alternative date-time formats. 
No luck. I then changed the CREATE PROCEDURE statement to specify a
VARCHAR(16) INOUT parameter to simplify the testcase by avoiding any
datetime weirdness, but ended up getting the same value I had passed
into the stored procedure, instead of having it set by the stored
procedure to some other value. Finally, I changed the stored procedure
signature to a single OUT parameter, but still didn't find that
.callproc ended up returning any changed input sequence values. When I
call these stored proecedures from the command line, they perform as I
expected.

So, does pyDB2 support INOUT or OUT parameters?

Also, I created the rsultset.db2 sample stored procedure shipped in
the SQLLIB/samples/sqlproc directory, then tried calling it from
Python. I was able to retrieve the first result set from the stored
procedure, but when I called curs.nextset() I received an error:
  AttributeError: Cursor instance has no attribute 'nextset'

So it looks like pyDB2 doesn't support stored procedures that return
multiple result sets through the Cursor.nextset() method -- is this
correct?

Thanks,
Dan