pydb2-discuss

[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

Re: [PyDB2-discuss] Calling stored procedures

[??? <yo...@li...>]

SGl+DQoNCkN1cnJlbnRseSwgc3RvcmVkIHByb2NlZHVyZSBzdXBwb3J0IGluIFB5REIyIGlzIGlu
Y29tcGxldGUgYW5kIG5vdCB0ZXN0ZWQgdGhvcm91Z2hseS4NCkFzIHlvdSBjYW4gc2VlLCBuZXh0
c2V0KCkgbWV0aG9kIGlzIG1pc3Npbmcgbm93Lg0KDQpQeURCMiBuZWVkcyBzZXZlcmUgcmV3b3Jr
aW5nIHJpZ2h0IG5vdywgJ2NhdXNlIGl0IHdhcyBzdGFydGVkIGp1c3QgZm9yIG15IHByb2plY3Qn
cyBuZWVkDQphbmQgbW9zdCBvZiBhbGwsIEknbSBub3QgYSBEQjIgc3BlY2lhbGlzdC4gIFBlcnNv
bmFsbHksIEkgaGF2ZW4ndCB1c2VkIGFueSBzdG9yZWQgcHJvY2VkdXJlIGluIG15IHJlYWwgcHJv
amVjdHMgOigNCg0KWW91IGtub3cgd2UgY2FuIGdldCBsb3RzIG9mIGhpbnRzIGZyb20gb3RoZXIg
UHl0aG9uIGRhdGFiYXNlIG1vZHVsZXMgbGlrZSBNeVNRTGRiIGFuZCBEQ09yYWNsZSouDQpBZnRl
ciByZXZpZXdpbmcgdGhlbSwgSSBjYW4gc3RhcnQgY29kaW5nIGFmcmVzaCwgSSB0aGluay4NCg0K
VGhhbmtzLA0KTWFuLVlvbmcNCg0KLS0tLS0gT3JpZ2luYWwgTWVzc2FnZSAtLS0tLSANCkZyb206
ICJEYW4gU2NvdHQiIDxkZW5pYWxzQGdtYWlsLmNvbT4NClRvOiA8cHlkYjItZGlzY3Vzc0BsaXN0
cy5zb3VyY2Vmb3JnZS5uZXQ+DQpTZW50OiBTdW5kYXksIERlY2VtYmVyIDA1LCAyMDA0IDE6NTgg
UE0NClN1YmplY3Q6IFtQeURCMi1kaXNjdXNzXSBDYWxsaW5nIHN0b3JlZCBwcm9jZWR1cmVzDQoN
Cg0KPiBIaToNCj4gDQo+IEkgbm90aWNlZCB0aGVyZSBpcyBubyBkb2N1bWVudGF0aW9uIGluIHRo
ZSBSRUFETUUgYWJvdXQgc3RvcmVkDQo+IHByb2NlZHVyZSBzdXBwb3J0LCBzbyBJIHdlbnQgYWhl
YWQgdG8gdHJ5IGl0IG91dCBhY2NvcmRpbmcgdG8gdGhlDQo+IGludGVyZmFjZSBkZWZpbmVkIGJ5
IFBFUDI0OS4NCj4gDQo+IEVudjogREIyIFY4LjIsIFdpbmRvd3MgWFAsIFB5dGhvbiAyLjMsIHB5
REIyIDAuOTk4YSBpbnN0YWxsZWQgZnJvbQ0KPiBNYW4tWW9uZyBMZWUncyBiaW5hcnkuDQo+IA0K
PiBJIHN0YXJ0ZWQgYnkgY3JlYXRpbmcgYSBzaW1wbGUgU1FMIHN0b3JlZCBwcm9jZWR1cmUgdG8g
dGVzdCB0aGUgSU5PVVQNCj4gcGFyYW1ldGVyIHN1cHBvcnQ6DQo+IA0KPiAgICBDUkVBVEUgUFJP
Q0VEVVJFIHRlc3RfZGF0ZShJTk9VVCBkYXRlIERBVEUpDQo+ICAgIEJFR0lODQo+ICAgICAgU0VU
IGRhdGUgPSAnMjAwNy0wMS0wNSc7DQo+ICAgIEVORA0KPiANCj4gLi4uIHRoZW4gY2FsbGVkIGl0
IGZyb20gdGhlIGZvbGxvd2luZyBtZXRob2Q6DQo+IA0KPiBkZWYgY2FsbF9wcm9jZWR1cmVfaW5v
dXQoY29ubik6DQo+ICAgICIiIg0KPiAgICBDYWxsIGEgcHJvY2VkdXJlIHRoYXQgYWNjZXB0cyBv
bmUgSU5PVVQgREFURSBwYXJhbWV0ZXIuDQo+ICAgICIiIg0KPiAgICANCj4gICAgY3Vycz1jb25u
LmN1cnNvcigpDQo+ICAgIHN0bXQgPSAnVEVTVF9EQVRFJw0KPiAgICBwYXJtID0gKCcyMDA0LTEy
LTAyJykNCj4gIyAgICBwYXJtID0gKCcxMC8yNy8xOTkxJykNCj4gICAgcmVzID0gY3Vycy5jYWxs
cHJvYyhzdG10LCBwYXJtKQ0KPiAgICByZXR1cm4gcmVzDQo+IA0KPiBUaGUgZXhwZWN0ZWQgcmV0
dXJuIHZhbHVlIHdvdWxkIGJlICcyMDA3LTAxLTA1JywgYnV0IEkgZ2V0IGFuIGVycm9yIGluc3Rl
YWQ6DQo+IA0KPiAqKioqKg0KPiAgRmlsZSAiQzpcbG9jYWxccHl0aG9uMi4zXExpYlxzaXRlLXBh
Y2thZ2VzXERCMi5weSIsIGxpbmUgNDM4LCBpbiBjYWxscHJvYw0KPiAgICByID0gc2VsZi5fY3Mu
Y2FsbHByb2MocHJvY25hbWUudXBwZXIoKSwgdF9hcmdzKQ0KPiBfZGIyLmVycm9yOiANCj4gDQo+
IFNRTFNUQVRFIDogMjIwMDcsIE5BVElWRSBFUlJPUiBDT0RFIDogLTk5OTk5DQo+IA0KPiBbSUJN
XVtDTEkgRHJpdmVyXSBDTEkwMTEzRSAgU1FMU1RBVEUgMjIwMDc6IEFuIGludmFsaWQgZGF0ZXRp
bWUgZm9ybWF0DQo+IHdhcyBkZXRlY3RlZDsgdGhhdCBpcywgYW4gaW52YWxpZCBzdHJpbmcgcmVw
cmVzZW50YXRpb24gb3IgdmFsdWUgd2FzDQo+IHNwZWNpZmllZC4gU1FMU1RBVEU9MjIwMDcNCj4g
KioqKioNCj4gDQo+IEFzIHlvdSBjYW4gc2VlLCBJJ3ZlIHRyaWVkIGEgY291cGxlIG9mIGFsdGVy
bmF0aXZlIGRhdGUtdGltZSBmb3JtYXRzLiANCj4gTm8gbHVjay4gSSB0aGVuIGNoYW5nZWQgdGhl
IENSRUFURSBQUk9DRURVUkUgc3RhdGVtZW50IHRvIHNwZWNpZnkgYQ0KPiBWQVJDSEFSKDE2KSBJ
Tk9VVCBwYXJhbWV0ZXIgdG8gc2ltcGxpZnkgdGhlIHRlc3RjYXNlIGJ5IGF2b2lkaW5nIGFueQ0K
PiBkYXRldGltZSB3ZWlyZG5lc3MsIGJ1dCBlbmRlZCB1cCBnZXR0aW5nIHRoZSBzYW1lIHZhbHVl
IEkgaGFkIHBhc3NlZA0KPiBpbnRvIHRoZSBzdG9yZWQgcHJvY2VkdXJlLCBpbnN0ZWFkIG9mIGhh
dmluZyBpdCBzZXQgYnkgdGhlIHN0b3JlZA0KPiBwcm9jZWR1cmUgdG8gc29tZSBvdGhlciB2YWx1
ZS4gRmluYWxseSwgSSBjaGFuZ2VkIHRoZSBzdG9yZWQgcHJvY2VkdXJlDQo+IHNpZ25hdHVyZSB0
byBhIHNpbmdsZSBPVVQgcGFyYW1ldGVyLCBidXQgc3RpbGwgZGlkbid0IGZpbmQgdGhhdA0KPiAu
Y2FsbHByb2MgZW5kZWQgdXAgcmV0dXJuaW5nIGFueSBjaGFuZ2VkIGlucHV0IHNlcXVlbmNlIHZh
bHVlcy4gV2hlbiBJDQo+IGNhbGwgdGhlc2Ugc3RvcmVkIHByb2VjZWR1cmVzIGZyb20gdGhlIGNv
bW1hbmQgbGluZSwgdGhleSBwZXJmb3JtIGFzIEkNCj4gZXhwZWN0ZWQuDQo+IA0KPiBTbywgZG9l
cyBweURCMiBzdXBwb3J0IElOT1VUIG9yIE9VVCBwYXJhbWV0ZXJzPw0KPiANCj4gQWxzbywgSSBj
cmVhdGVkIHRoZSByc3VsdHNldC5kYjIgc2FtcGxlIHN0b3JlZCBwcm9jZWR1cmUgc2hpcHBlZCBp
bg0KPiB0aGUgU1FMTElCL3NhbXBsZXMvc3FscHJvYyBkaXJlY3RvcnksIHRoZW4gdHJpZWQgY2Fs
bGluZyBpdCBmcm9tDQo+IFB5dGhvbi4gSSB3YXMgYWJsZSB0byByZXRyaWV2ZSB0aGUgZmlyc3Qg
cmVzdWx0IHNldCBmcm9tIHRoZSBzdG9yZWQNCj4gcHJvY2VkdXJlLCBidXQgd2hlbiBJIGNhbGxl
ZCBjdXJzLm5leHRzZXQoKSBJIHJlY2VpdmVkIGFuIGVycm9yOg0KPiAgQXR0cmlidXRlRXJyb3I6
IEN1cnNvciBpbnN0YW5jZSBoYXMgbm8gYXR0cmlidXRlICduZXh0c2V0Jw0KPiANCj4gU28gaXQg
bG9va3MgbGlrZSBweURCMiBkb2Vzbid0IHN1cHBvcnQgc3RvcmVkIHByb2NlZHVyZXMgdGhhdCBy
ZXR1cm4NCj4gbXVsdGlwbGUgcmVzdWx0IHNldHMgdGhyb3VnaCB0aGUgQ3Vyc29yLm5leHRzZXQo
KSBtZXRob2QgLS0gaXMgdGhpcw0KPiBjb3JyZWN0Pw0KPiANCj4gVGhhbmtzLA0KPiBEYW4NCj4g
DQo+IA0KPiAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tDQo+IFNGIGVtYWlsIGlzIHNwb25zb3JlZCBieSAtIFRoZSBJVCBQcm9kdWN0IEd1aWRl
DQo+IFJlYWQgaG9uZXN0ICYgY2FuZGlkIHJldmlld3Mgb24gaHVuZHJlZHMgb2YgSVQgUHJvZHVj
dHMgZnJvbSByZWFsIHVzZXJzLg0KPiBEaXNjb3ZlciB3aGljaCBwcm9kdWN0cyB0cnVseSBsaXZl
IHVwIHRvIHRoZSBoeXBlLiBTdGFydCByZWFkaW5nIG5vdy4gDQo+IGh0dHA6Ly9wcm9kdWN0Z3Vp
ZGUuaXRtYW5hZ2Vyc2pvdXJuYWwuY29tLw0KPiBfX19fX19fX19fX19fX19fX19fX19fX19fX19f
X19fX19fX19fX19fX19fX19fXw0KPiBQeURCMi1kaXNjdXNzIG1haWxpbmcgbGlzdA0KPiBQeURC
Mi1kaXNjdXNzQGxpc3RzLnNvdXJjZWZvcmdlLm5ldA0KPiBodHRwczovL2xpc3RzLnNvdXJjZWZv
cmdlLm5ldC9saXN0cy9saXN0aW5mby9weWRiMi1kaXNjdXNzDQo+

Re: [PyDB2-discuss] Calling stored procedures

[Yuval T. <yu...@ad...>]

On Monday 06 December 2004 07:35, ??? wrote:
> 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.

I've been meaning to say this for a long time now... :-)
Well, I actually started to implement a MySQLdb-like module - which basically 
means, implement only the basics in C and the rest in python (I think it 
comes out to be implementing the connection object in C and the cursors in 
python - similar to MySQLdb).  I stopped working on it a while ago - I've 
been meaning to post the idea before I get serious with it.
I'm pretty familiar with DB2 (not a lot, but enough) and with the current DB2 
module.  So maybe now's a good time to get back on it - I'm in.  Anybody 
else ? :-)

-- 
Yuval Turgeman
Content Technology, Aduva LTD.

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
> >