How to call Stored Procedure?

Hello

To call stored procedure i have written following method in database.py file
def CallProc(self, query):
if not self._conn:
self.connect()
try:
self._cursor.callproc(‘test_procedure’)
except Exception:
print Exception

But it showing me an error

Traceback (most recent call last):
File “/home/rohit/Documents/office_work/new_smartailor/bench-repo/frappe-bench/apps/frappe/frappe/middlewares.py”, line 15, in call
return super(StaticDataMiddleware, self).call(environ, start_response)
File “/home/rohit/Documents/office_work/new_smartailor/bench-repo/frappe-bench/env/lib/python2.7/site-packages/werkzeug/wsgi.py”, line 588, in call
return self.app(environ, start_response)
File “/home/rohit/Documents/office_work/new_smartailor/bench-repo/frappe-bench/env/lib/python2.7/site-packages/werkzeug/wsgi.py”, line 588, in call
return self.app(environ, start_response)
File “/home/rohit/Documents/office_work/new_smartailor/bench-repo/frappe-bench/env/lib/python2.7/site-packages/werkzeug/local.py”, line 224, in application
return ClosingIterator(app(environ, start_response), self.cleanup)
File “/home/rohit/Documents/office_work/new_smartailor/bench-repo/frappe-bench/env/lib/python2.7/site-packages/werkzeug/wrappers.py”, line 286, in application
return f(*args[:-2] + (request,))(*args[-2:])
File “/home/rohit/Documents/office_work/new_smartailor/bench-repo/frappe-bench/apps/frappe/frappe/app.py”, line 100, in application
frappe.db.rollback()
File “/home/rohit/Documents/office_work/new_smartailor/bench-repo/frappe-bench/apps/frappe/frappe/database.py”, line 495, in rollback
self.sql(“rollback”)
File “/home/rohit/Documents/office_work/new_smartailor/bench-repo/frappe-bench/apps/frappe/frappe/database.py”, line 121, in sql
self._cursor.execute(query)
File “/home/rohit/Documents/office_work/new_smartailor/bench-repo/frappe-bench/env/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 205, in execute
self.errorhandler(self, exc, value)
File “/home/rohit/Documents/office_work/new_smartailor/bench-repo/frappe-bench/env/lib/python2.7/site-packages/MySQLdb/connections.py”, line 36, in defaulterrorhandler
raise errorclass, errorvalue
ProgrammingError: (2014, “Commands out of sync; you can’t run this command now”)

Please suggest the right approach to do the same

Well, nothing can be said about this without looking at the stored procedure. Why don’t you just write that logic in Python?

Hi can any one help on this “How to call Stored Procedure” from my app.

Is there a way to call stored procedure on submit event of POS?

Found way to call stored procedure!!!

Quick Summary

  1. Created new method in sales_invoice.py (your_module_name.py)
def copy_sales_invoice_frm_GL(doc, method):
    frappe.db.sql("call stored_procedure_name")
  1. Search for “doc_events” in hooks.py

  2. Create new entry under “doc_events” as below:

"Sales Invoice": {
	"on_submit": "erpnext.accounts.doctype.sales_invoice.sales_invoice.copy_sales_invoice_frm_GL"
},
  1. restart bench using

bench restart

  1. Done. :slight_smile:
2 Likes

To help beginners:
Use below code to send parameters to stored procedure, and after return read columns.

results = frappe.db.sql("call procedureName(%s,%s)", (parameter1, parameter2), as_dict=1)
for result in results :
      frappe.errprint(result.columnName)
2 Likes