CODEWITHSUNDEEP
stored-proceduresweb2py
MichaelF
MichaelF

Reputation: 149

stored procedures in web2py

I'm considering converting an app from php/MySQL to web2py (and MySQL or Postgres). The only SQL code in the php codebase for this app are calls to stored procedures...no SELECTs, no INSERTs, etc., in the php codebase. All SQL source in the php codebase is on the order of "CALL proc_Fubar(args...);"

How do I tell web2py, "Here's my INSERT stored procedure; here's my SELECT..."? I know I can executesql, but how about the returned rowset from a SELECT...I'd like to have that data returned as if it were the results of a web2py query from a table.

Yes, I know. I'm trying to get all the neat stuff that web2py does without keeping up my end of the bargain (by defining my SQL as web2py wants to see it).

Upvotes: 2

Views: 1367

Answers (1)

Anthony
Anthony

Reputation: 25536

You might try the following. First, define a model that matches the fields returned by your stored procedure (set migrate=False so web2py doesn't try to create that table in the db).

db.define_table('myfaketable', ..., migrate=False)

Then do:

raw_rows = db.executesql('[SQL code to execute stored procedure]')
rows = db._adapter.parse(raw_rows,
    fields=[field for field in db.myfaketable],
    colnames=db.myfaketable.fields)

Upvotes: 2

Related Questions