How to call Stored Procedure?

ERPNext
#1

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

#2

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

#3

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

#4

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

#5

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
#6

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