I can't call a stored procedure in CodeIgniter. However, when I call the procedure directly in MySQL, it works. Why isn't it working when I call it in CodeIgniter?
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_proc`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
declare name1 TEXT;
declare id1 TEXT;
select name,id into name1,id1 from my_tbl WHERE name='sam';
select * from my_tbl;
select name1,id1;
END
I think you are using the following way to call procedure.
$this->db->call_function('test_proc');
Its wrong. Only default procedures can be called using this method. To call procedures defined by you, you have to go with
$this->db->query("call test_proc()");
For Oracle procedured here is a simple way to call
$rsponse = '';
$s = oci_parse($this->db->conn_id, "begin packageName.procedureName(:bind1,:bind2,:bind3,:bind4,:bind5); end;");
oci_bind_by_name($s, ":bind1", $data['fieldOne'],300);
oci_bind_by_name($s, ":bind2", $data['fieldTwo'],300);
oci_bind_by_name($s, ":bind3", $data['fieldThre'],300);
oci_bind_by_name($s, ":bind4", $data['fieldFour'],300);
oci_bind_by_name($s, ":bind4", $response,300);
oci_execute($s, OCI_DEFAULT);
echo $message;
In the above example procedure accept four arguments as input and one parameter as output. in case of direct calling procedure remove 'packageName.' That's it...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With