'Run stored procedure included sql script using robot framework

I want to run sql script which include db and table creations and stored procedure creations. but when I try to run sql script using execute sql script keyword in database library I get an error like below

ProgrammingError: (1064, "You 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 $$\n CREATE OR
 REPLACE PROCEDURE `proc_GetCustomerDetails`(\n   I...' at line 2")

before procedure I have delimiter like this,

DELIMITER $$
CREATE OR REPLACE PROCEDURE `proc_GetCustomerDetails`(
  IN CustomerNbr LONGTEXT,
  IN Lang VARCHAR(5)
)

DETERMINISTIC

BEGIN

IF Lang IS NULL THEN SET  lang = "fin";
END IF;


SELECT * from dbname.customer;

END;$$
DELIMITER ;

If I comment stored procedure part, sql file is running without errors with rest of the table creation statements.

I googled this and couldn't find any related issue. I saw we have call stored procedure keyword. but I want to keep table creations and stored procedures in same sql file and need to run. I use MariaDB for this task.

Libraries used,

  • pymysql
  • robotframework-databaselibrary

If I run sql file using HeidiSQL it is running without any errors with procedures and delimiters. That mean there are no sql errors.

Can Someone tell me how to fix this?

mysqlmariadbrobotframework


Solution 1:[1]

DELIMITER is a statement supported only for the client, it is not supported by the server; thus the error. The solution - drop it.

Here's a question with very good answers what is it and why it's needed.

In short - when you work with a client you need a way to instruct it "this is not a statement you should execute immediately, this is still just a line in the SP you'll be sending to the server" - so you tell (to the client) "the DELIMITER b/n statements is temporarily $$". The server doesn't need/care about that - it knows everything between CREATE PROCEDURE, BEGIN, END are connected statements, a block.

When you connect to the DB through API (pymysql) vs an interactive client (shell, heidisql, etc) - you're sending the SP as a block, there's no way its statements will be ran one by one, thus the DELIMITER is not needed, not a supported command by the server, and generates an error. Drop it.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Todor Minakov

Related Questions