Execute stored procedures containing SQL scripts using Robot Framework

Question

I want to run a sql script that contains database and table creation and stored procedure creation. But when I try to run the sql script in the database library using the execute sql script keyword, I get the following error:

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 the stored procedure, I have this delimiter:

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 out the stored procedure part, the sql file will run without errors along with the rest of the table creation statements.

I searched on Google and found no related questions. I see we have keywords that call stored procedures. But I want to put table creation and stored procedure in the same sql file and run it. I am using MariaDB for this task.

Libraries used:

• pymysql
• Robot Framework Database Library

If I run the sql file using HeidiSQL, it runs within the stored procedure and delimiter without any errors. This means there are no sql errors.

Can anyone tell me how to fix this?

Answer 1

DELIMITER is a client-only statement, the server does not support it, hence the error. Solution - delete it.

Here is a question with a very good answer that explains what DELIMITER is and why it is needed.

In short - when you are working with a client you need a way to indicate to it "this is not an immediately executed statement, this is still just a line in the stored procedure that you will send to the server" - so You tell (the client) "DELIMITER between statements is $$ temporarily". The server doesn't need/care about this - it knows that everything between CREATE PROCEDURE, BEGIN, END is a connected statement, a block.

When you connect to the database via API (pymysql), compared to interactive clients (shell, heidisql, etc.) - you send the SP as a chunk and there is no way to run its statements one by one, so DELIMITER is not needed , the server does not support the command and an error will be generated. Delete it.