Monday, February 13, 2012

Calling remote program on DB2 from Python/Web2py

I never realized the power of stored procedure on DB2 and now I can execute the CL/RPG/QRY program from python/web2py !


1. Create Stored Procedure

Let's say, I have a CL program called MYCLPGM in MYLIB. In CL program, I added libraries, clear files and call RPG program..etc.

Then, I can create stored procedures called MYPRCD as follows.
STRSQL 
CREATE PROCEDURE MYLIB/MYPRCD LANGUAGE CL NOT DETERMINISTIC
CONTAINS SQL EXTERNAL NAME MYLIB/MYCLPGM PARAMETER STYLE  
GENERAL



You can also set up parameters or select different language if you need. I recommend using CL and call whatever you need from there because most of case, your objects are in different libraries and you can add them in CL.


After it's created, you can check the stored procedures information in here
STRSQL 
VIEW ROUTINE
SELECT * FROM SYSROUTINES WHERE SPECIFIC_NAME ='MYPRCD'

VIEW PARAMETERS
SELECT * FROM SYSPARMS


From DB2, you can call the stored procedure like
STRSQL 
CALL MYLIB/MYPRCD

2. Calling from Python
* I assume you already finished my previous post.


>>> import pyodbc
>>> conn = pyodbc.connect('DSN=MYDSN;UID=xxxxx;PWD=xxxxx')
>>> cursor = conn.cursor()
>>> cursor.execute('CALL MYLIB.MYPRCD')
<pyodbc.Cursor object at 0x01E7DA30>
>>> conn.close
>>> conn.close() 
3. Calling from web2py (Tested with Ver 1.99.4)
* I assume you already finished my previous post.

Yes, you can do it using executesql.

Model
-------------------------------------------------------------------
db = DAL('db2://DSN=MYDSN;UID=xxxxx;PWD=xxxxx', migrate_enabled=False)
-------------------------------------------------------------------

Controller
-------------------------------------------------------------------
def index():
    form=SQLFORM.factory()
    if form.accepts(request):
        db.executesql('CALL MYLIB.MYPRCD')
        response.flash = 'Stored Executed !!'
    return dict(form=form)
-------------------------------------------------------------------

View
-------------------------------------------------------------------
{{extend 'layout.html'}}

<h3>Run stored procedure</h3>
<hr>
{{=form}}
-------------------------------------------------------------------





2 comments:

  1. hi..thanks for the post...have you tried calling a stored proc that returns a value when using pyodbc?

    ReplyDelete
  2. I tried but no success for returning parameters for it : (

    ReplyDelete