Create function query in MySQL

Discussion of open issues, suggestions and bugs regarding usage of dbExpress drivers for MySQL in Delphi and C++Builder
Post Reply
AngelusB
Posts: 12
Joined: Tue 20 Jan 2009 11:17

Create function query in MySQL

Post by AngelusB » Fri 20 Dec 2019 18:51

I try to execute the query below with DBexpress (TSQLQuery connected to TSQLConnection). The query runs fine in Navicat or other clients, but not in DBExpress (driver DevartMySqlDirect).

DELIMITER $$
CREATE FUNCTION totaalStabu(stabuId int,molenId int,periodeId int) RETURNS float
BEGIN
DECLARE Status FLOAT;
SELECT sum(oh_aantal) INTO Status
FROM sim
WHERE oh_molen= molenId and oh_periode=periodeId and oh_stabu=stabuId
group by oh_stabu;
RETURN Status;
END$$
DELIMITER ;

Error message
#42000You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELIMITER $$
CREATE FUNCTION totaalStabu(stabuId int,molenId int,periodeId int)' at line 1

Please help

ViktorV
Devart Team
Posts: 2907
Joined: Wed 30 Jul 2014 07:16

Re: Create function query in MySQL

Post by ViktorV » Tue 24 Dec 2019 14:33

This error is returned by the MySQL server. To solve it, you must correctly modify the request. For example:

Code: Select all

CREATE FUNCTION totaalStabu(stabuId int,molenId int,periodeId int) RETURNS float
BEGIN
DECLARE Status FLOAT;
SELECT sum(oh_aantal) INTO Status
FROM sim
WHERE oh_molen= molenId and oh_periode=periodeId and oh_stabu=stabuId
group by oh_stabu;
RETURN Status;
END

Post Reply