Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New to the CF scene
    Join Date
    Nov 2011
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Passing parameters to sql script (on Windows)

    Hello,

    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.


    file.bat:

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


    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

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    ???

    What is this?

    mysql-uroot-Proot

    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
    Code:
    mysql -uUSERNAME -pPASSWORD < mysql.sql
    And yes, that works. I do it all the time.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Nov 2011
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy

    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

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New to the CF scene
    Join Date
    Nov 2011
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you can you please show me the example in details if it's possible ? i'm kind of stuck in this problem.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Regular Coder
    Join Date
    Sep 2011
    Posts
    103
    Thanks
    0
    Thanked 14 Times in 14 Posts

  • #8
    New to the CF scene
    Join Date
    Nov 2011
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ooooooooh i'm sorry to keep u buzy it's 9:50 am here so... Thank you very much good night

  • #9
    New to the CF scene
    Join Date
    Nov 2011
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Okay, here you go.

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

    *** FILE: filter.js ***
    Code:
    // 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 ****
    Code:
    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 ***
    Code:
    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:
    Code:
    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.
    Last edited by Old Pedant; 12-01-2011 at 09:51 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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 ****
    Code:
    // 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 ***
    Code:
    use $1$;
    select * from $2$ where id > $3$;
    *** FILE: demo.bat ***
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •