View Full Version : Passing parameters to sql script (on Windows)

11-30-2011, 10:17 PM

I need to send a parameter to SQL script from a file. bat (in MySQL) I tried all the examples here and on google but its not working maybe because the examples used are for Linux, but not Windows XP can you help me by giving an example:

For example I want to pass the name of the table 'Table1' from the .bat file to the sql script . DB and the name of my database.

mysql-uroot-Proot h127.0.0.1-DB <C: \ mysql.sql


use DB;
select * from Table1;

Notes: Examples of Oracle do not work, and scripts for mysql on linux do not work: s i guess ( i tried some but ddn't work )

Thank you very much;):thumbsup:

Old Pedant
12-01-2011, 01:04 AM

What is this?


I just use mysql as the command to invoke the command line MySQL client and it works fine. Yes, in Windows.

But then you need to do something like

mysql -uUSERNAME -pPASSWORD < mysql.sql

And yes, that works. I do it all the time.

12-01-2011, 08:31 AM
yes i know that command works if you want just to call the sql file, however i couldn't find an example to send a parameter from the .bat file to the sql script. and i guess nobody seems to know how :(

Old Pedant
12-01-2011, 10:34 AM
Ahhh...I see.

If this was Linux, there are various regular-expression based "replaces" you could do. You'd send the "mysql.sql" through one of those filters and thence to the redirected stdin.

You could do that on Windows, if you could find a similar little program.

For that matter, you could write one easily enough in JScript, since it has all the regular expression capability you'd need.

In other words, you'd do something like this in your .bat file:

cscript filter.js mysql.bat argument1 argument2 | mysql -uXXX -pXXX

I just tried a really simple example and it worked fine.

12-01-2011, 10:40 AM
Thank you :thumbsup: can you please show me the example in details if it's possible ? i'm kind of stuck in this problem.

Old Pedant
12-01-2011, 10:44 AM
Have you ever used the Windows Script Host?

You create a file named ".vbs" or ".js" and then write code in VBScript or JScript (JavaSciprt). In place of the browser objects (the browser DOM), you have a bunch of WScript objects.

I'll try to work up a reasonable little example tomorrow, but it's 01:44 here and my wife is yelling at me to get to bed.

12-01-2011, 10:50 AM

Check this


12-01-2011, 10:51 AM
ooooooooh i'm sorry to keep u buzy it's 9:50 am here so...:D Thank you very much good night

12-01-2011, 11:05 AM
Thank u jassi.singh ! but tht works only for Oracle and not MySql on Windows :( i've tried to send parameters from .bat file and recieve in the sql file but until now i guess no body could find a solution

Old Pedant
12-01-2011, 10:37 PM
Okay, here you go.

First of all, here is my JavaScript file that does the simple filter/replace:

*** FILE: filter.js ***

// get the command line arguments
var args = WScript.Arguments;
// this code will only work if exactly two arguments are passed to the program:
if ( args.length == 2 )
// the arguments are assumed to be db name and table name
var db = args(0);
var table = args(1);

// now we read in all the text in "StdIn"...that is, all the text from standard input
var sql = WScript.StdIn.ReadAll();

// and do a simple replacement of $DB$ with the db name
sql = sql.replace(/\$DB\$/g, db );
// and replace $TABLE$ with the table name
sql = sql.replace(/\$TABLE\$/g, table );

// and then spit the altered text back out to StdOut (standard output)
WScript.echo( sql );

Then here is my very simple SQL file.

*** FILE: demo.sql ****

use $DB$;
select * from $TABLE$;

As you can see, I want to replace $DB$ with the db name passed in and $TABLE$ with the table name passed in.

So, finally, here is my batch file:

*** FILE: demo.bat ***

cscript /NoLogo filter.js testDB tblTest < demo.sql | mysql -uUSERNAME -pPASSWORD

(of course replace USERNAME and PASSWORD with your own values).

That code reads thus:

cscript === invokes the command line script engint
/NoLogo === suppresses the header messages that would mess up the stuff sent to mysql
filter.js === the name of the JavaScript file to execute
testDB === the database name to use to replace $DB$
tblTest === the table name to use to replace $TABLE$
< demo.sql === asks to use the file "demo.sql" as the input (that is, redirected StdIn)
| === the "pipe" character sends the output of everything up to now to what follows
mysql -uUSERNAME -pPASSWORD === the normal invocation of mysql

If you've never used the "pipe" character it might take some getting used to.

Essentially, it captures all the output from the command line code on the left of the "pipe" and sends it, AS INPUT, to the command on the right side of the "pipe".

And that's it.

It's really the same as you would use on Linux except that instead of using a Shell script we use JavaScript as our coding language. The < and | characters have the same meanings that they do in Linux command lines.

How's that?


You can have as many SQL commands as you want in your ".sql" file. And you could make as many substitutions as you wanted if you just change the number of arguments expected and processed.

Old Pedant
12-01-2011, 10:50 PM
It occurred to me that I *could* have created a general purpose JavaScript filter/replacement program that would take *ANY* number of arguments and that use them to replace, in order, parameters of the form $1$, $2$, ...., $13$, ...

Let's write that, just for fun:

*** FILE: filter.js ****

// read in all the sql
var sql = WScript.StdIn.ReadAll();

// process all arguments:
var args = WScript.Arguments;
for ( var a = 0; a < args.length; ++a )
var field = args(a);
var fieldnum = a+1;
// construct the needed regular expression (e.g., $1$ for argument 0)
var re = new RegExp("\\$" + fieldnum + "\\$", "g");
// and replace $n$ in the sql with the argument value
sql = sql.replace( re, field );
WScript.echo( sql );

*** FILE: demo.sql ***

use $1$;
select * from $2$ where id > $3$;

*** FILE: demo.bat ***

cscript /NoLogo filter.js testDB tblTest 2000 < demo.sql | mysql -uUSER -pPASS

And there you have it! A nice generic way of replacing any number of arguments in a SQL file.

Old Pedant
12-01-2011, 11:01 PM
Should have noted: The use of $xx$ is completely arbitrary. We could use any delimiters you preferred so long as they wouldn't occur in your MySQL code. @xx@ or #xx# would be reasonable choices, too.