Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Does "%MBIN%\mysql.exe" -u"%MUSER%" -p"%MPASS%" -h "%MHOST%" "%DB%" < %scriptpath%\DBQuery1.sql work? –  dezso Oct 26 '12 at 11:22
add comment

closed as too localized by dezso, Mark Storey-Smith, Paul White, Max Vernon, StanleyJohns Jun 24 '13 at 18:06

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

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.

share|improve this answer
add comment

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