采购 MySQL ExecuteNonQuery 导致的“致命错误"? [英] Sourcing 'Fatal Error' resulting from MySQL ExecuteNonQuery?

问题描述

使用 Windows Server 2003、MySQL 5.1.23、MySQL .NET 连接器 6.2.2

Using Windows Server 2003, MySQL 5.1.23, MySQL .NET Connector 6.2.2

我正在尝试使用 MySQLBulkLoader 类来读取固定宽度的文本文件.该类 Load 方法的摘录是:

I am trying to use the MySQLBulkLoader class to read in a fixed width text file. An excerpt from that classes Load method is:

string sql = BuildSqlCommand();
MySqlCommand cmd = new MySqlCommand(sql, Connection);
cmd.CommandTimeout = 10 * 60;// Timeout;
cmd.ExecuteNonQuery();

'BuildSqlCommand' 创建的字符串是:

The string created by 'BuildSqlCommand' is:

LOAD DATA LOCAL INFILE 'c:/M/D/ALLDATA_0.TXT' INTO TABLE dk.tk 
FIELDS  TERMINATED BY '' LINES TERMINATED BY '\n' 

当程序启动ExecuteNonQuery"时，发生以下错误:

When the program launchs the 'ExecuteNonQuery' the following error occurs:

(消息)在命令执行期间遇到致命错误.(来源)MySql.Data(内部异常消息)尝试读取结果集时遇到致命错误.

ExecuteNonQuery 是否有任何已知限制?文本文件大小为 290MB.

Is there any known limitations to ExecuteNonQuery? The text file size is 290MB.

推荐答案

经过多次不同的尝试后，看起来该错误与 LOAD DATA INFILE 语句的混合语法以及 MySQL Bulk Loader 的工作方式有关.我最终完全忽略了 MySQL Bulk Loader 类，因为 BuildSQLCommand 方法返回的结果不起作用.BulkLoader 的问题在于它似乎需要逗号分隔的文件，其中我的文件是固定宽度的，没有字段分隔符.

After many different attempts it looks like the error was related to a mix the syntax of the LOAD DATA INFILE statement and how the MySQL Bulk Loader works. I ended up completely ignoring the MySQL Bulk Loader class as the BuildSQLCommand method returned results that did not work. The problem with the BulkLoader is that it appears to want comma delimited files where my file was fixed width with no field delimiters.

以下代码示例适用于导入固定宽度的文件 - 设置表格字段大小以将文件分割成适当的字段.

The following code sample works for importing a fixed width file - the table field sizes are setup to slice up the file into the appropriate fields.

string sql = @"load data infile 'c:/myfolder/Data/" + aFile.Name 
+ "' ignore into table t_data fields terminated by '' enclosed by '' lines terminated by '\n'";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.CommandTimeout = Timeout;
cmd.ExecuteNonQuery();

我的超时值设置为 0.

My Timeout value is set to 0.

这篇关于采购 MySQL ExecuteNonQuery 导致的“致命错误"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！