All geek questions in one place

LOAD DATA INFILE with variables

I am trying to use LOAD DATA INFILE as a common procedure, but it seems to be impossible. Then I tried the usual way of embedding code in the application as such,

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();

A generated query that is stored in a string variable query,

  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 message,

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

I don't know how to get around this, but when I use the same query directly in mysql, it works fine. PLEASE help me .. very much :)

+4
source share
2 answers

I found a solution on my own, it must establish this condition,

  Allow User Variables=true;

in the connection string, which will allow using @parameter values ​​in the request :)

Thanks for answers:)

+8
source

I also ran into this problem, then created a massive script insert and ran it using C # .net code

0
source

Source: https://habr.com/ru/post/1439095/

More articles:


All Articles