Path in instructions "CONCATE... PREPARE... EXECUTE... "

Question

I'm having a problem with MySQL. To put it in context, I'm in charge of migrating my company's codes from Oracle to MySQL and I'm having quite a few problems with macro variables: the only solution I've found is to use concat, then prepare and execute, but that makes us lose a lot of readability... By the way, if you have a better alternative on this point, don't hesitate!

Having said that, in general, the "concate.... PREPARE... EXECUTE" instructions allow me to do whatever I want, except when translating path macros...

When I want to use these instructions with source or tee, I get error messages and despite several weeks of searching I can't find a solution. Does anyone have a solution?

I've included some code examples below

Example 1:

I want to automate source //data_file/data_test/test_source_fic_a_import.sql; when I run it like this no worries but when I do :

set @rep_ts := '//data_file/data_test/';
SET @req_test_source := CONCAT("source ", @rep_ts, "test_source_fic_a_import.sql ;");
PREPARE prepa_test_source FROM @req_test_source; EXECUTE prepa_test_source; DEALLOCATE PREPARE prepa_test_source;

I get the following error message :

ERROR 1064 (42000): 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 [...]

Example 2 :

It's the same when I run :

tee //data_file/data_test/test_tee.txt;
use basecommune;
select count(*)
from test_squelbyan;
notee

it works but when I try

set @rep_ts := '//data_file/data_test/';
SET @req_test_tee := CONCAT("tee ", @rep_ts, "test_tee.txt;");
PREPARE prepa_test_tee FROM @req_test_tee; EXECUTE prepa_test_tee; DEALLOCATE PREPARE prepa_test_tee;

I get :

ERROR 1064 (42000): 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 'tee [...]

Answer 1

source and tee are mysql client builtin commands. See https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html

Those builtin commands are not recognized by MySQL's SQL parser, and they cannot be run by a prepared statement.

You can only run such commands in the mysql client, for example at the mysql> prompt, or in an SQL script that is run by the mysql client.