Calling Store Procedures in Frappe

ERPNext Customize ERPNext
1

We want to call a stored procedure of Maria DB and want to execute it through the frappe framework code. I have tried frappe.db.sql(“CALL SPNAME;”) but it’s not working, has anyone tried it practically, pls share some sample code. it will be a great help.

import frappe

@frappe.whitelist()
def call_update_gross_pay_stored_proc():
    try:
        frappe.db.sql("CALL `update_gross_pay`", ())
        
    except Exception as e:
        
        frappe.throw("An error occurred while calling the stored procedure.")

frappe.ui.form.on('Employee Test', {
	refresh: function(frm){
	    frm.add_custom_button(__('UPDATE GROSS PAY'), function(){
	        frappe.call({
                method: "frappe.employee.doctype.employee_test.store_procedure.call_update_gross_pay_stored_proc",
                callback: function(response) {
                    console.log(result)
                }
            });
            
			
	        
	    });
	}
});

DELIMITER //

CREATE PROCEDURE _dc1eeb63b0ad9a8a.update_gross_pay()
BEGIN
    UPDATE tabEmployee Test
    SET gross_pay = 5000;
END //

DELIMITER ;

@revant_one @rmehta

2

hi,

try this:
server script v3

client script v3

console log v3

3

import frappe
from frappe import db

@frappe.whitelist()
def get_update_recs():
db.sql(“CALL you_store_procedure();”).
db.commit() // To make changes in your Database