If you benefit from web2py hope you feel encouraged to pay it forward by contributing back to society in whatever form you choose!

This is from my blog post.

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

alt text

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

alt text

Related slices

Comments (0)


Hosting graciously provided by:
Python Anywhere