Calling stored procedure from codeigniter

Question

I am using codeigniter that has mysqli as db driver, am trying to call a simple stored procedure from my model but get an error. What am i doing wrong

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pc()' at line 1

pc()

Filename: C:\hosted\saner.gy\ipa\system\database\DB_driver.php

Line Number: 330

When i run the query call Stored Procedure it runs well but from codeigniter it throws the above error

Stored Procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `pc`()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    SELECT * FROM tbl_flo
  WHERE name = 'sam';
END

Controller

public function sp()
{
$this->User_model->pc();
}

Model

public function pc()
        {
            $query = $this->db->query("pc()");

            return $query->result();
        }

Grigg

Answer 1

Stored procedures are invoked using the CALL procedure_name(optional_params) query.

You need to edit the query used in your model like this:

public function pc()
    {
        $query = $this->db->query("CALL pc()");
        return $query->result();
    }

Lieberman

Codeigniter not allows to call stored procedures in this way. – Fawkes

@semira, I don't see why it shouldn't, this is a regular query like any other. Anyways, this is a very old response, maybe things have changed? – Lieberman

Codeigniter not allows to pass parameters to stores procedures. It is a common problem in Codeigniter. Still it is not changed. Even if is a query like any other, Codeigniter always returns an error when it is a stored procedure with parameters. – Fawkes

Answer 2

This block of code work for me at model

function get_sunmeter_for_initiator($data){
        try {
            $this->db->reconnect();
            $sql = "CALL `get_sunmeter_for_initiator`(?, ?, ?)";
            $result = $this->db->query($sql,$data); // $data included 3 param and binding & query to db
            $this->db->close();

            
        } catch (Exception $e) {
            echo $e->getMessage();
        }
        return $result;

    }

Stolon

Answer 3

You are using the following way to call procedure.

$this->db->call_function('pc');

Or you can also use this

$this->db->query("call pc()");

Apiculate

Codeigniter not allows to call stored procedures in this way. – Fawkes

Answer 4

Update 2024

Was curious: just tested this on CodeIgniter v4.5.1, MySQL database.

Stored procedures with parameters do work, on this version at the very least.

  $this->db->query('CALL my_stored_proc(?,?)',[$param1, $param2]);

Herr

Answer 5

    $param1 = this->request->getPost('param1');
    $param2 = this->request->getPost('param2');

    $sql = "CALL my_procedure(?, ?)";
    $params = [$param1, $param2];
    $query = $this->db->query($sql, $params);

Carboloy

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community? – Yearbook

Answer 6

$this->db->query("CALL getWalletStatement($currency, '$date', '$date1', $type, 10000)")->getResult();

Carboloy

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Likelihood

Please read "How to Answer" and "Explaining entirely code-based answers". It helps more if you supply an explanation why this is the preferred solution and explain how it works. We want to educate, not just provide code. – Bordereau

This adds nothing new compared to the answers posted in 2015, and on top of that it introduces SQL injection. – Ulrica