pass paramter from batch to mysql file

Question

I try hard to get my batch to pass parameter to mysql file

my batch :

SET MYSQL_EXE="C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe"
SET DB_USER=root
SET DB_PWD=viper007
SET DB=db01

FOR %%c in (D:\Dev_Algo\Grt_pce\datas\*.*) do (
    set "str=%%c"
    SETLOCAL ENABLEDELAYEDEXPANSION
    call set "str=%%str:\=/%%"
    echo SET @fpath ='!str!' > params.sql
    ENDLOCAL
    type params.sql D:\Dev_Algo\Grt_pce\query0.sql | mysql --user=%DB_USER% --password=%DB_PWD% --database=%DB%    
    )

my params.sql file

SET @fpath ='D:/Dev_Algo/Grt_pce/datas/LBFLO007.csv'

My sql file (extract)

SET @file=@fpath;
LOAD DATA INFILE @file IGNORE INTO TABLE db01.tblpce
    fields terminated by ';'
    ignore 1 lines

Result - I can connect to mysql NO PROBLEM - I can load my file with SQL command from MYSQL Workbench - Params file is OK withe single quotes needed for LOAD DATA INFILE - I can't set variable @fpath. I got the error message from batch

ERROR 1064(4200) at line 1 : You have an error in your SQL synthax; check the manual......'SET @file=@fpath' at line 2

I read this http://bugs.mysql.com/bug.php?id=39115 , apparently this functionaly is not implemented. Is this limitation is linked with my error message.

How can I bypass this error, in order to import bulks csv into mysql.

If your params.sql doesn't have a newline at the end, type command won't add it, so mysql will see SET @fpath ='.......'SET @file=@fpath; — woxxom
– woxxom, Commented Oct 3, 2015 at 11:24
I just add echo. >> params.sql . I got the same error .'SET @file=@fpath' at line 3 — user3884958
– user3884958, Commented Oct 3, 2015 at 11:29
Actually even without the new echo. >>params.sql . This file has 2 line (1 is blank) — user3884958
– user3884958, Commented Oct 3, 2015 at 11:32
it seems tha t > or >> add return cariage and it seems that in order to work I must not have a new line !! — user3884958
– user3884958, Commented Oct 3, 2015 at 11:55

woxxom · Accepted Answer · 2015-10-03 11:47:47Z

2

Manually replace @file with the actual path (using JREPL.BAT, for example), save the result in another .sql file, use it with mysql.

set "str=%%c"
SETLOCAL ENABLEDELAYEDEXPANSION
call set "str=%%str:\=/%%"
call jrepl "@file" "'!str!'" <D:\Dev_Algo\Grt_pce\query0.sql >query.sql
ENDLOCAL
type query.sql | mysql --user=%DB_USER% --password=%DB_PWD% --database=%DB%

And remove SET @file=@fpath; line from your sql file.

answered Oct 3, 2015 at 11:47

woxxom

75.2k15 gold badges161 silver badges164 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

pass paramter from batch to mysql file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related