LOAD DATA INFILE with variables
- by Hasitha
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 :)
