Create a SQL FUNCTION with CodeIgniter

Question

For a legacy project, I would like to execute this query using CodeIgniter :

DELIMITER $$
CREATE FUNCTION `getCustomerFullName`(intCustomerID INT) 
  RETURNS varchar(100) CHARSET latin1
  return CONCAT(
    (SELECT FirstName FROM Customer WHERE CustomerID = intCustomerID),
    ' ',
    (SELECT LastName FROM Customer WHERE CustomerID = intCustomerID))$$
DELIMITER ;

When I try to use $this->db->query(), I get this error :

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 
'DELIMITER $$ CREATE FUNCTION `getCustomerFullName`(intCustomerID INT) RETURNS '
at line 1

How can I execute such "multi-line" query using CodeIgniter ?

wogsland · Accepted Answer · 2016-05-10 21:09:02Z

2

The issue is in the query, not CodeIgniter. Use the regular delimiter inside your function:

DELIMITER $$
CREATE FUNCTION `getCustomerFullName`(intCustomerID INT) 
  RETURNS varchar(100) CHARSET latin1
  return CONCAT(
    (SELECT FirstName FROM Customer WHERE CustomerID = intCustomerID),
    ' ',
    (SELECT LastName FROM Customer WHERE CustomerID = intCustomerID));
$$
DELIMITER ;

answered May 10, 2016 at 21:09

wogsland

9,53121 gold badges63 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hari Prasath Banyan Infotech Over a year ago

it is possible to call a function in codeigniter sql query?

elddenmedio · Accepted Answer · 2016-05-10 21:53:46Z

1

if you want to execute a Function or SP, you need to add this to mysql (in the mtop menu of your phpmyadmin or Workbench) you have many otions

in More (routine / new / add new)
create a function or sp

Then in CI you only call like

$this->db->query('call Function()');

edited May 10, 2016 at 21:53

answered May 10, 2016 at 21:47

elddenmedio

1,0301 gold badge8 silver badges15 bronze badges

1 Comment

xurei Over a year ago

No. I need to create the function IN CodeIgniter. I'm using its migration mechanism, and a lot of different instances. I cannot use PhpMyAdmin in that case, it would require too much maintenance.

grupowebex · Accepted Answer · 2017-01-24 17:26:32Z

0

Best solution i get was looking inside system libs and use it.

$this->db->simple_query("YOUR FULL SQL STRING");

I was looking on google and not solution provided.

answered Jan 24, 2017 at 17:26

grupowebex

1855 bronze badges

Collectives™ on Stack Overflow

Create a SQL FUNCTION with CodeIgniter

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related