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