5

I want to pass SOME VARIABLES to mysql file from bash shell script.

Here is my shell script. 

#!/bin/bash
echo $0 Started at $(date)
mysql -uroot -p123xyzblabla MyMYSQLDBName<mysqlfile.sql  PARAM_TABLE_NAME

Please note that it is MYSQL and not SQLPLUS

My MYSQL.sql , I want to read and use passed parameter/argument (PARAM_TABLE_NAME)

select count(*) from PARAM_TABLE_NAME

Question 1: What is the correct syntax to pass variable(PARAM_TABLE_NAME) to sql file (mysqlfile.sql)? Question 2: How can I print variable(PARAM_TABLE_NAME) in sql file (mysqlfile.sql)?

Basically, I want to make generic SQL script which can load or select data from tables based on received inputs.

Thanks

user3671115
  • 121
  • 2
  • 7

3 Answers3

3

There is no such thing as passing a parameter to a SQL file. A SQL file is no more than a text file that contains a list of SQL statements. These statements are interpreted by the mysql client program exactly as if you typed them on your keyboard.

The mysql client does not provide the feature you are looking for.

But I can think of a few tricks to achieve a similar effet:

  • create/populate a configuration table prior to reading your SQL file. Then write your SQL file so that it takes this table contents into account:

    bash> mysql -e "INSERT INTO config_table VALUES(1, 2, 3)"
bash> mysql < script.sql

  • prepend your SQL file with some variables declarations. Then use these variables in the rest of your script:

    bash> (echo "SET @var=123;" ; cat script.sql) |mysql

[example script.sql]
SELECT * FROM mytable WHERE id = @var;

  • write your SQL file with some placeholders that your replace on the fly, e.g with sed:

    bash> sed "s/__VAR_A__/mytable/g" script.sql |mysql

[example script.sql]
SELECT * FROM __VAR_A__ WHERE id = 123;

All the above is quite dirty. A much cleaner solution would involve stored procedures or functions. Then you would just pass your parameters as procedure parameters:

bash> PARAM1='foo'; PARAM2='bar'
bash> mysql -e "CALL MyProc($PARAM1);"
bash> mysql -e "SELECT MyFunc($PARAM2);"

note: it is not possible to parametrize a table name in SQL, so you will need to resort to dynamic SQL like this in all cases (except for the sed-based hack, which I do not recommend)

Community
  • 1
  • 1
RandomSeed
  • 29,301
  • 6
  • 52
  • 87
2

This is an old thread but I think may still be useful to some people. Something like this should work:

mysql -uroot -p123xyzblabla MyMYSQLDBName -e "set @testVar='customer_name'; source mysqlfile.sql;"

Now @testVar (customer_name) is available for you to use in mysqlfile.sql file.

HTH

webDeveloper
  • 1,284
  • 3
  • 16
  • 31
1

The way to pass parameters has already been answered in this or other threads. However, specific to the sample in you question, I'd like to add that you can't use the variables declaration method as a placeholder for a table name, as the documentation says:

User variables are intended to provide data values. They cannot be used directly in an SQL statement as an identifier or as part of an identifier, such as in contexts where a table or database name is expected

If you want to use a table name parameter, you can still use the sed or the stored procedures or functions as answered by @RandomSeed

In addition to that, another way is using PREPARE and EXECUTE in your script. The following example allows you to create a database/schema (in case you wanted to use stored procedures you need to have them already stored in a database), like this:

[myscript.sql]
set @s=CONCAT("CREATE DATABASE ", @dbname);
PREPARE stmt FROM @s;
EXECUTE stmt;

Then use any of the proposed syntax in the other questions to set the @dbname variable:

mysql -uroot -p123xyzblabla MyMYSQLDBName -e "set @dbname='mydatabase'; source myscript.sql;"
Fran Reneses
  • 29
  • 4