使用变量加载数据文件 [英] LOAD DATA INFILE with variables

问题描述

我试图将LOAD DATA INFILE用作糟糕的过程，但似乎无法完成.然后，我尝试了将代码嵌入应用程序本身的常用方法，

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.

我不知道该如何解决，但是当我直接在mysql本身上使用相同的查询时，它可以正常工作.请帮助我..非常感谢你:)

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 found the solution myself, it is to set this condition,

      Allow User Variables=true;

在连接字符串中将允许在查询中使用@parameter值:)

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

感谢您的回复:)

这篇关于使用变量加载数据文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！