I have following stored function defined in my local database:
CREATE DEFINER=`root`@`localhost` FUNCTION `GenerateSectionFee`(`in_month` INT, `in_year` INT, `in_section_detail_id` INT, `in_issue_date` DATE, `in_due_date` DATE) RETURNS int(11)
MODIFIES SQL DATA
BEGIN
/** Variables declartion **/
declare v_new_mfm_id int;
/** insert heads of given grade & section **/
insert into month_fees_masters(section_details_id, fee_month, fee_year, issue_date, due_date, h1, h2, h3, h4, h5, h6, h7, h8, h9, h10,
th1, th2, th3, th4, th5, th6, th7, th8, th9, th10)
select
class_detail_id, in_month, in_year, in_issue_date, in_due_date, h1, h2, h3, h4, h5, h6, h7, h8, h9, h10,
th1, th2, th3, th4, th5, th6, th7, th8, th9, th10
from fee_masters
where class_detail_id=in_section_detail_id and fee_type='R';
/** Get id of inserted record **/
SELECT LAST_INSERT_ID() into v_new_mfm_id;
/** insert monthly fee of all students of given grade & section **/
insert into month_fees(month_fees_masters_id, student_id, arears, fine_amount, fee_con, deposit_amount, status)
select
v_new_mfm_id, s.id, (f.arears+f.fine_amount-f.fee_con-f.deposit_amount), 0 fine_amount, s.fee_con, 0 deposit_amount, 'L' status
from
students s inner join month_fees f on s.id=f.student_id and f.status='L'
where
s.cur_class_detail_id = in_section_detail_id and s.status='ACTIVE'
;
return (0); /** Zero means No Error. Records created successfully **/
END
When I call it from phpmyadmin it works fine. but when I call it from Laravel it gives following error:
[2014-11-03 05:53:54] production.ERROR: exception 'Illuminate\Database\QueryException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1305 PROCEDURE iiui_db.GenerateSectionFee does not exist (SQL: call GenerateSectionFee(10, 2014, 27, 2014-10-01, 2014-10-08))' in C:\wamp\www\SchoolWeb\vendor\laravel\framework\src\Illuminate\Database\Connection.php:555
I am calling this function from Laravel as follows:
$flag_ = DB::statement('call GenerateSectionFee(?, ?, ?, ?, ?)', $data);
While $data is an array.
CALL
is used with stored procedures - doc, while for a function you do:
select function(args)
so in your case:
$flag_ = DB::statement('select GenerateSectionFee(?, ?, ?, ?, ?)', $data);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments