1

My batch file will contain some thing like this :

    set MUSER=root 
    set MPASS=xprd123 
    set MHOST=172.33.49.55 
    set DB=DBName1
    set MBIN=C:\Program Files\MySQL\MySQL Server 5.5\bin
    set scriptpath=D:\STATS_COLLECTOR 
    "%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "%MHOST%" "%DB%" -e "set @2:='%scriptpath%'; source DBQuery1.sql;"

Content of DBQuery1.sql will be :

SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS' 
UNION 
SELECT
MAX(IF(variable_name = 'INNODB_OS_LOG_WRITTEN', VARIABLE_VALUE, NULL)) AS INNODB_OS_LOG_WRITTEN, 
MAX(IF(variable_name = 'QUESTIONS', VARIABLE_VALUE, NULL)) AS QUESTIONS 
FROM information_schema.GLOBAL_STATUS 
INTO OUTFILE '@2/stats.csv' 
    FIELDS TERMINATED BY ',' 
    ENCLOSED BY '"' 
    LINES TERMINATED BY '\n'
;

I am not sure this is the way to pass path to the SELECT query inside DBQuery1.sql.

This code is not working.

I looked at other posts on passing path to batch file, but whatever I tried, I am not able to solve this issue.

When I execute the batch file, I am not sure if the path is getting passed into the sql file and if yes, why @2 is not getting the path passed.

Could anybody kindly help on this.

1
  • Does "%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "%MHOST%" "%DB%" < %scriptpath%\DBQuery1.sql work? Oct 26, 2012 at 11:22

1 Answer 1

0

MySQL's source command executes the file as a separate process. By design, it executes in a different session than the one where you assigned the variable. You should find that executing set @2:='%scriptpath%'; source DBQuery1.sql; from within a mysql.exe session also does not work, either, although I haven't tested this myself recently.

As a workaround, you can write your script to automatically generate the SQL script file each time the file is run:

set MUSER=root 
set MPASS=xprd123 
set MHOST=172.33.49.55 
set DB=DBName1
set MBIN=C:\Program Files\MySQL\MySQL Server 5.5\bin
set scriptpath=D:\STATS_COLLECTOR\
set scriptfile=D:\STATS_COLLECTOR\DBQuery1.sql

echo SELECT 'INNODB_OS_LOG_WRITTEN','QUESTIONS' > %scriptfile%
echo UNION >> %scriptfile%
echo SELECT >> %scriptfile%
echo MAX(IF(variable_name = 'INNODB_OS_LOG_WRITTEN', VARIABLE_VALUE, NULL)) AS INNODB_OS_LOG_WRITTEN, >> %scriptfile%
echo MAX(IF(variable_name = 'QUESTIONS', VARIABLE_VALUE, NULL)) AS QUESTIONS >> %scriptfile%
echo FROM information_schema.GLOBAL_STATUS >> %scriptfile%
echo INTO OUTFILE '%scriptpath%/stats.csv' >> %scriptfile%
echo     FIELDS TERMINATED BY ',' >> %scriptfile%
echo     ENCLOSED BY '"' >> %scriptfile%
echo     LINES TERMINATED BY '\n' >> %scriptfile%
echo ; >> %scriptfile%

"%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "%MHOST%" "%DB%" < %scriptfile%

Another alternative is to write the query in a more versatile scripting language such as PowerShell, which would allow you to use the MySQL Connector/Net API to execute any arbitrary set of queries you wanted.

Not the answer you're looking for? Browse other questions tagged or ask your own question.