+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Calling a MySql function (/showthread.php?tid=73483)
Calling a MySql function - Shawn - 04-28-2019
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
RE: Calling a MySql function - Shawn - 04-28-2019
(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:
RE: Calling a MySql function - Shawn - 04-28-2019
(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;
RE: Calling a MySql function - InsiteFX - 04-29-2019
Not sure but maybe this will help.
Emulating nextval() function to get sequence in MySQL
RE: Calling a MySql function - Shawn - 04-29-2019
(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.
RE: Calling a MySql function - InsiteFX - 04-30-2019
Try changing your $sql single quotes to double quotes.
In the User Guide the query bindings all use double quotes.