CodeIgniter Forums

Full Version: Calling a MySql function
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
I have created a function in mysql called NextVal that requires a string parameter and returns an integer.
I tried the following code:
Code:
public function next_value() {
    $ci =& get_instance();
    $sql = 'SELECT nextval(?) as receipt_id';
    $query = $ci->db->query( $sql, array( $this->sequence_name ) );
    return $query->row()->receipt_id;
}

Codeigniter logs the following error:
ERROR - 2019-04-28 19:16:55 --> Query error:  - Invalid query: SELECT nextval('receipt_id_seq') as receipt_id

However, if I paste and run the select statement shown in the error in MySql Workbench it returns the expected integer value under the column name receipt_id
(04-28-2019, 07:37 PM)Shawn Wrote: [ -> ]I have created a function in mysql called NextVal that requires a string parameter and returns an integer.
I tried the following code:
Code:
public function next_value() {
    $ci =& get_instance();
    $sql = 'SELECT nextval(?) as receipt_id';
    $query = $ci->db->query( $sql, array( $this->sequence_name ) );
    return $query->row()->receipt_id;
}

Codeigniter logs the following error:
ERROR - 2019-04-28 19:16:55 --> Query error:  - Invalid query: SELECT nextval('receipt_id_seq') as receipt_id

However, if I paste and run the select statement shown in the error in MySql Workbench it returns the expected integer value under the column name receipt_id

The full error is:
Query error: execute command denied to user 'my_app_username'@'localhost' for routine 'my_schema.nextval' - Invalid query:
(04-28-2019, 09:30 PM)Shawn Wrote: [ -> ]
(04-28-2019, 07:37 PM)Shawn Wrote: [ -> ]I have created a function in mysql called NextVal that requires a string parameter and returns an integer.
I tried the following code:
Code:
public function next_value() {
    $ci =& get_instance();
    $sql = 'SELECT nextval(?) as receipt_id';
    $query = $ci->db->query( $sql, array( $this->sequence_name ) );
    return $query->row()->receipt_id;
}

Codeigniter logs the following error:
ERROR - 2019-04-28 19:16:55 --> Query error:  - Invalid query: SELECT nextval('receipt_id_seq') as receipt_id

However, if I paste and run the select statement shown in the error in MySql Workbench it returns the expected integer value under the column name receipt_id

The full error is:
Query error: execute command denied to user 'my_app_username'@'localhost' for routine 'my_schema.nextval' - Invalid query:

I have tried the following but no joy:
grant execute on db.* to 'my_app_username'@'localhost';
FLUSH PRIVILEGES;
(04-29-2019, 03:50 AM)InsiteFX Wrote: [ -> ]Not sure but maybe this will help.

Emulating nextval() function to get sequence in MySQL

Thanks, but I have a nextval emulator function for MySql. The problem is that I am getting an access denied error when I try calling it from my class in codeigniter.
Try changing your $sql single quotes to double quotes.

In the User Guide the query bindings all use double quotes.