MySQL script with parameters

Question

I want to create a deployment script, somehow emulate Oracle deployment scripts, where with &param you can use previously declared parameters. I need to call this script for different users on different databases automatically.

For example my script should be:

USE &param;
DROP TABLE IF EXISTS `TEST` ;
CREATE TABLE IF NOT EXISTS `TEST` (X   INT(16))
etc....

Of course &param is what I would have used in Oracle environment.

Thanks

Updates:

Forgot to mention that I am using a windows environment for now. I have created a batch script to call the mysql script. The easiest way I thought would be to pass to mysql 2 command: 1) use the schema I have as parameter and then call the script which will create the table regardless of the schema. Unfortunately mysql seems to understand that I want to connect to the schema X, but doesn't want to call the script.

REM param is the schema and mainsql is the script
SET param="%1"
SET mainsql="script.sql"

echo %param%
echo %mainsql%

mysql -u <user> --password=<psw>  %param% "source %mainsql%;"

KernelM · Accepted Answer · 2012-03-28 14:51:28Z

5

As far as I know you can't directly pass variables in to a MySQL script. The best you can do is set user variables in a wrapper shell script. Something like:

passed_var1=$1
passed_var2=$2
mainsql=script.sql

mysql $(usual_parameters) -e "set @user_var1=$passed_var1; set @user_var2=$passed_var2; source $mainsql"

Adjust for actual use, of course.

edited Mar 28, 2012 at 14:51

answered Mar 28, 2012 at 13:32

KernelM

8,9362 gold badges26 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

bioShark Over a year ago

Thank you for your answer, but I am still struggling with it. First of all, I had forgot to mention I will run the scrip in windows (at least for now). But anyway, I have adapted the script to a batch script, and it seems to connect to mysql, using the schema I want,but can't call a command.... I put updates in the question.

KernelM Over a year ago

Oh, I forgot to add the -e parameter that tells mysql to actually execute the following string.

bioShark Over a year ago

Brilliant man, thanks. That did the trick. Meaning that if I specify the schema as a parameter it works to call source. But if I make this call: "set @innerparam=%param%; source %mainsql%;", I get the error: ERROR 1054 (42S22) at line 1: Unknown column '<params value>' in 'field list'

bioShark Over a year ago

apparently I can call 2 source commands, so it's fine. All in all I think that solves my problem. Thanks a lot.

KernelM Over a year ago

Oh, yeah, you'd need to quote "%param%" because otherwise MYSQL will interpret it as a column name. You'd also have to make sure the quotes are properly escaped. Glad to see it worked out though.

Collectives™ on Stack Overflow

MySQL script with parameters

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related