Welcome Guest, Not a member yet? Register   Sign In
Calling a MySql function
#1

(This post was last modified: 04-28-2019, 09:31 PM by Shawn.)

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
Reply
#2

(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:
Reply
#3

(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;
Reply
#4

Not sure but maybe this will help.

Emulating nextval() function to get sequence in MySQL
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#5

(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.
Reply
#6

(This post was last modified: 04-30-2019, 03:43 AM by InsiteFX.)

Try changing your $sql single quotes to double quotes.

In the User Guide the query bindings all use double quotes.
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply




Theme © iAndrew 2016 - Forum software by © MyBB