Re: [PyDB2-discuss] Calling stored procedures

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

Hi Man-Yong:

Well, pyDB2 has filled a heck of a gap in the meantime, and I thank you for it.

In theory, nextset() should be relatively easy to implement at least;
in CLI/ODBC you could call SQLMoreResults() (
http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/r0000628.htm)
and either set the Python Cursor object to None if the result is
SQL_NO_DATA_FOUND or prepare the Cursor object to fetch the next
result set if the result is SQL_SUCCESS.

Dan


On Mon, 6 Dec 2004 14:35:38 +0900, ??? <yo...@li...> wrote:
> Hi~
> 
> Currently, stored procedure support in PyDB2 is incomplete and not tested thoroughly.
> As you can see, nextset() method is missing now.
> 
> PyDB2 needs severe reworking right now, 'cause it was started just for my project's need
> and most of all, I'm not a DB2 specialist.  Personally, I haven't used any stored procedure in my real projects :(
> 
> You know we can get lots of hints from other Python database modules like MySQLdb and DCOracle*.
> After reviewing them, I can start coding afresh, I think.
> 
> Thanks,
> Man-Yong
> 
> 
> 
> ----- Original Message -----
> From: "Dan Scott" <de...@gm...>
> To: <pyd...@li...>
> Sent: Sunday, December 05, 2004 1:58 PM
> Subject: [PyDB2-discuss] Calling stored procedures
> 
> > 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
> >
> > 
> > -------------------------------------------------------
> > SF email is sponsored by - The IT Product Guide
> > Read honest & candid reviews on hundreds of IT Products from real users.
> > Discover which products truly live up to the hype. Start reading now.
> > http://productguide.itmanagersjournal.com/
> > _______________________________________________
> > PyDB2-discuss mailing list
> > PyD...@li...
> > https://lists.sourceforge.net/lists/listinfo/pydb2-discuss
> >