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.
Omer-PitouAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jimyXCommented:
> This is my code, Unfortunately it gives an syntax error.

What is the error message? It should point to the issue and line number. Could you show that please.
Your presented query is fine. So that's limiting the issue to either the path is not valid or the SQL in that file is not correct.

You may try simple query to be safe (the file is readable):
"Select load_file('C:/fingertest/fingerhotel.sql');"

Can you see the content as a result?

Please also, as per MySQL manual, make sure that:
The file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege.
The file must be readable by all and its size less than max_allowed_packet bytes.
If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Omer-PitouAuthor Commented:
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.
Omer-PitouAuthor Commented:
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 ....
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

jimyXCommented:
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.
Omer-PitouAuthor Commented:
Hi,
Thanks for your input.
I am fine with you creating that application. What will be the language?
jimyXCommented:
Please let's move the discussion through the email.
Omer-PitouAuthor Commented:
pitou_ntumba@yahoo.fr
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.