Link to home
Start Free TrialLog in
Avatar of Omer-Pitou
Omer-Pitou

asked on

MySQL - EXECUTE Statements in a text file

Hi,
I am trying to find an easy way to deploy updated stored procedures to my clients, without accessing the MySQL Workbench and using the Import module.
I have my stored procedures exported to a .sql file with the export module of MySQL Export module.
I am trying to simulate something  like reading the content of .sql file and execute it. This is my code, Unfortunately it gives an syntax error.
SET @stmt=load_file('C:/fingertest/fingerhotel.sql');
PREPARE sqlStmt FROM @stmt;
EXECUTE sqlStmt;
DEALLOCATE PREPARE sqlStmt;

Any input is welcome.
ASKER CERTIFIED SOLUTION
Avatar of jimyX
jimyX

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Omer-Pitou
Omer-Pitou

ASKER

I solved it,
Actually, the sql file generated with the workbench export module, comes with a USE command inside.
Loading the content of the sql file into a string, with the USE command included, was causing problems when it comes to execution with prepare commands. So, I took out the USE command, and it works.
Oh sorry, I just noticed that with 2 or more commands in the sql, it gives error.
Find attached a test file, and the error point to the second create table command. It is something like 'syntax error near create ....
Please note, as per MySQL manual, you can not execute multiple queries.

The text must represent a single statement, not multiple statements.

BTW, no attachment!!!

PS: If you are interested, I can help you by creating a simple application to execute that file for you, instead of relying on query. So you change nothing, you just connect to MySQL and provide the path of that multiple queries file, and the application executes the queries for you.
Hi,
Thanks for your input.
I am fine with you creating that application. What will be the language?
Please let's move the discussion through the email.
pitou_ntumba@yahoo.fr