LOAD DATA INFILE with variables

Question

I was tring to use the LOAD DATA INFILE as a sotred procedure but it seems it cannot be done. Then i tried the usual way of embedding the code to the application itself like so,

conn = new MySqlConnection(connStr);
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd = conn.CreateCommand();
string tableName = getTableName(serverName);
string query = "LOAD DATA INFILE '" + fileName + "'INTO TABLE "+ tableName +" FIELDS TERMINATED BY '"+colSep+"' ENCLOSED BY '"+colEncap+"' ESCAPED BY '"+colEncap+"'LINES TERMINATED BY '"+colNewLine+"' ("+colUpFormat+");";
cmd.CommandText = query;
cmd.ExecuteNonQuery();
conn.Close();

The generated query that gets saved in the string variable query is,

        LOAD DATA INFILE 'data_file.csv'INTO TABLE tbl_shadowserver_bot_geo FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"'LINES TERMINATED BY '\n' (timestamp,ip,port,asn,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,url,agent,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy);

But now when i run the program it gives an error saying,

        MySQLException(0x80004005) Parameter @dummy must be defined first.

I dont know how to get around this, but when i use the same query on mysql itself directly it works fine. PLEASE help me..thank you very much :)

I notice there are some spaces missing in your query (.csv'INTO) and (ESCAPED BY '"'LINES). Check if that is the case in your original query or if its just wrongly posted here. — Nin, Oct 11 '12 at 08:58
hi, thank you for the reply, but it wasn't the issue :) .. i found the solution — Hasitha Shan, Oct 12 '12 at 04:02

score 8 · Accepted Answer · answered Oct 11 '12 at 09:09

8

I found the solution myself, it is to set this condition,

      Allow User Variables=true;

in the connection string that will allow to use @parameter values in a query :)

Thank you for the responses :)

answered Oct 11 '12 at 09:09

Hasitha Shan

2,900
6
42
83

Neeraj Kumar Gupta · Answer 2 · 2012-10-11T09:34:44.680

0

I too encountered with this problem, then I created the bulk insert script and run it using C#.net code

edited Oct 11 '12 at 09:34

answered Oct 11 '12 at 07:54

Neeraj Kumar Gupta

2,157
7
30
58

sure..i will once i complete testing the suggestion you gave. thanks again :) – Hasitha Shan Oct 11 '12 at 07:58
1

This is simply not true, LOAD DATA INFILE can be used from code. – nos Oct 11 '12 at 08:00
I tried it by using code but it does not work .. can you please give any link or code sample which doing same .. – Neeraj Kumar Gupta Oct 11 '12 at 08:05
well yours must be the long way around it seems that there is another easy solution.. i just found it and ill post it as the answer :) – Hasitha Shan Oct 11 '12 at 09:07
Ok .. Good this will be also use full for me – Neeraj Kumar Gupta Oct 11 '12 at 09:34

LOAD DATA INFILE with variables

2 Answers2

Linked

Related