REM **********
REM Modify the values below to match your configuration
REM **********
set v_mysqlHost=192.168.*.130
set v_mysqlPort=3306
set v_mysqlUser=admin
set v_mysqlPassword=111111
set v_mysqlDatabase= testBase
REM if the mysql sever deployed on linux system
set v_storePathLinux=/tmp
set v_storePathWindows=D:\excel files\
set v_mysqlCommand=mysql --host=%v_mysqlHost% --port=%v_mysqlPort% --user=%v_mysqlUser% --password=%v_mysqlPassword% --database=%v_mysqlDatabase%
REM **********
REM The action starts here
REM **********
REM **********
REM Export the List
REM **********
set v_fileName=SOL.txt
set v_Query= select distinct(p.id),p.`reference` Ref,c.`name` Customer,p.`description` collection,p.`status` Status,(if(p.`createDate` is null,0,p.`createDate`)+25569*24*60*60*1000+8*60*60*1000)/(24*60*60*1000) createdDate,(if(p.`approvedDate` is null,0,p.`approvedDate`)+25569*24*60*60*1000+8*60*60*1000)/(24*60*60*1000) approvedDate,(if(p.`creatUserName` is null,'CS',p.`creatUserName`)) UserName,tp.`NoPatternComponents`,tp.`NoCaseComponents`,tp.`NoCoverComponents`,tp.`NoMaterialComponents`,p.`noPatts` NoPatterns,tp.`NoPatternComponents` + tp.`NoCaseComponents` + tp.`NoCoverComponents`+ tp.`NoMaterialComponents` TotalNoComponents INTO OUTFILE '%v_storePathLinux%/%v_fileName%' FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '\"' FROM `t_project` p,`t_customer` c,`t_temp_project` tp where p.`customerid` = c.`id` and p.`id` = tp.`projectId` and p.`enabled` = 1;
call %v_mysqlCommand% <bat.sql
call %v_mysqlCommand% -e "%v_Query%"
the bat.sql shoule be in the same root of this batch file,and the content of bat.sql is:
call new_proc();
Then we can first call a procedure then export the sql result to the txt file ,by the way,donot forget to give the FILE privliage to the user of admin through root