Executing SQL ... SQL Error 1064 ... How to specify current working directory?

Abdull posted 5 years ago in General
I have a "master" .sql file which bootstraps my database. It looks like this:

~~~~~File: init_database_tables.sql~~~~~~~~~
source create_database_mydatabase.sql;
source create_table_users.sql;
source create_table_authorities.sql;
source create_table_UserConnection.sql;
source create_table_persistent_logins.sql;

~~~~~End file: init_database_tables.sql~~~~~

As you can see,
delegates to other SQL script files located in the same directory as
. These other scripts actually do the work of bootstrapping my database.

When I open
in HeidiSQL ("Tools" -> "Load SQL File...") and then try to execute it (blue arrow button "Execute SQL..."), I get the following error message
"SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'source create_database_geeshenk.sql' at line 1"

When instead of running
I run the scripts one by one from within HeidiSQL, everything works fine.

My gut feeling is that HeidiSQL/Mysql is trying to source the scripts from the wrong working directory ("cwd"). Is there a way to specify the working directory for MySQL scripts to run inside of HeidiSQL?
ansgar posted 5 years ago
"source" is not some valid SQL keyword, and so you cannot run
source [filename];

These "source .." lines are likely interpreted by some other compiler or script interpreter on your side. No clue which interpreters are involved in your environment.
Abdull posted 5 years ago
"source" is standard MySQL syntax. Do you have suggestions for a valid SQL keyword that does the same?
jfalch posted 5 years ago
source is NOT SQL, but a special command valid only within the mysql command line client; AFAIK there is no SQL equivalent. mysql.exe -h... -D... <init_database_tables.sql should probably do what you want.
Abdull posted 5 years ago
jfalch, thanks for giving background. As HeidiSQL is focused on MySQL, I'd have guessed "source" is a keyword understood by HeidiSQL.

So for my use case, when using HeidiSQL, I have to load every SQL file separately and execute them manually, one by one.

Maybe it's just me, but having some sort of batch SQL script file execution functionality inside HeidiSQL would be of great help. Something like: " Execute all script files inside folder x" dialog or a "execute all script files selected" dialog. Of course HeidiSQL support for the "source" command would also be great. :)
jfalch posted 5 years ago
IMHO if you want batch script execution, there is no need to duplicate existing functionality into HeidiSQL. Its perspective is interactive; if you want batch, you could well use the command line client together with shell scripting.

Please login to leave a reply, or register at first.