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

-------------------------------------------------------------------