0

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?

Prabuddha Kulatunga
  • 137
  • 16
  • DELIMITER is client command, not SQL statement, hence it produces an error. Read the use manual for your SQL server accessing tool/library about how to redefine the delimiter or how to execute compound statement. – Akina Mar 23 '22 at 05:47
  • Thanks for the info. I couldn't fine any way to redefine delimiter or that kind of info yet. – Prabuddha Kulatunga Mar 23 '22 at 05:54
  • Explain the logic of your procedure. It is very strange. You receive 2 parameters but use them nowhere, including the parameter which is processed in IF statement. So your SP simply selects everything from the table `dbname.customer` finally.. What is your SP goal? – Akina Mar 23 '22 at 06:02
  • oh it is a sample select query. I didn't post original logic. I just wanted to mention I have to use delimiter since there are multiple statements inside my procedure. but in robot I can't run scripts with delimiter. basically I just don't want to change procedure. I just want a way to avoid this issue in robot. If I run sql using heidisql or any client it is running fine without errors. So that means there are no sql side errors and I also don't need to change procedure internals statements or parameters. – Prabuddha Kulatunga Mar 23 '22 at 06:37
  • 1
    *If I run sql using heidisql or any client it is running fine without errors.* Of course. They're specific client programs and knows about delimiter reassigning command (heidi implements this command due to it is std. for the using in CLI - compatibility providing). Whereas your robot seems to be simple tool for custom queries execution. The error looks like the robot tries to execute the whole provided text via prepared statement which obviously results in error. This seems to be non-fixable. Search another tool for to solve your task. – Akina Mar 23 '22 at 07:05
  • Thanks @Akina for the valuable info. let's wait if someone have ideas about different better plugins. I am new to this robot framework. I just wanted to ask from experienced people about this. your ideas also explains the issue. Thank you very much again. – Prabuddha Kulatunga Mar 23 '22 at 07:33
  • 1
    I think that it is safe to rename your topic - the subject must attract the attention of the appropriate specialists. I think that it should look similar to "RobotFramework and Stored procedure creation - what I can use instead when I need to create SP?". Of course, think and adjust this sample subject. – Akina Mar 23 '22 at 07:38
  • Thanks. seems like no solution – Prabuddha Kulatunga Apr 21 '22 at 18:35

1 Answers1

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.

Todor Minakov
  • 19,097
  • 3
  • 55
  • 60
  • Thanks for the info. I tried to run by removing delimiter since you asked. it is not running yet. but if I remove whole SP from sql file it is running without errors. when remove delimiter it shows an less detailed error like this, 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 '' at line 12" – Prabuddha Kulatunga Mar 24 '22 at 18:05
  • Well, all I can say now is that you need to fix that error :) – Todor Minakov Mar 25 '22 at 04:39
  • No solution yet.. – Prabuddha Kulatunga Apr 11 '22 at 06:41