CODEWITHSUNDEEP
c#mysqlsqlvisual-studiofile
Lucas Heise
Lucas Heise

Reputation: 149

C# - Load data infile to mysql - fatal error

I'm trying to load data to a mysql table, when I run the code in the MySql Workbench, it works fine, but when I try to run it in my c# application, I get this error:

Fatal error encountered during command execution.

Here's my code:

mConn.Open(); //Opens the connection
string query = "delete from time; alter table time auto_increment=1; LOAD DATA INFILE 'C://time_u.txt' INTO TABLE `time` CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (@col2, @col3) set date = @col2, hour = @col3;";
WriteDataToDataBase(query); //This guy just executes the command.ExecuteNonQuery();
mConn.Close();

Ps: I have 3 columns, the first one is a primary key (id) which I can't import, it needs to go up by one automatically, the second is the date (varchar) and third is the hours (varchar as well).

UPDATE
I tried to change the LOAD DATA code and I found out that the part that gives the error is this part: (@col2, @col3) set date = @col2, hour = @col3;. When I remove this, the code works (it doesn't work in my case because without this it tries to put data in the Primary key) but it doesn't show that first error.

Thanks...

Upvotes: 1

Views: 860

Answers (1)

Bradley Grainger
Bradley Grainger

Reputation: 28172

MySql.Data is treating @col2, @col3 as (undefined) query parameters, which is causing this error. One workaround would be to add AllowUserVariables=true to your connection string.

The other workaround would be to avoid the variables and just load directly into the columns you want:

LOAD DATA INFILE 'C://time_u.txt'
INTO TABLE time
CHARACTER SET 'utf8'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(date, hour);

Upvotes: 1

Related Questions