xurei xurei - 6 months ago 22
SQL Question

Create a SQL FUNCTION with CodeIgniter

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 ?

Answer

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 ;
Comments