0

I'm trying to run this query from a .Net application

LOAD DATA LOCAL INFILE 'testsFile.txt' 
INTO TABLE Test  
FIELDS TERMINATED BY ','  
OPTIONALLY ENCLOSED BY '"'  
LINES TERMINATED BY '\n'  
IGNORE 1 LINES  
(idTest, SampleID, Analyst, @Analysed, Device, Comments, @TotalRUL, @RULOne, @RULTwo, @RULThree, @RULFour, Uploaded) 
SET  
Analysed = nullif(@Analysed,''), 
TotalRUL = nullif(@TotalRUL,''), 
RULOne = nullif(@RULOne,''), 
RULTwo = nullif(@RULTwo,''), 
RULThree = nullif(@RULThree,''), 
RULFour = nullif(@RULFour,'')

When I run this query from MySQL Workbench everything works fine, but when I use my .net application to run the query I get the following exception:

Parameter '@Analysed' must be defined.

I don't think I can use a declare statement outside of a stored procedure and I cant use a stored procedure due to my use of the LOAD DATA statement

What to do? Is this checkmate?

Federico Razzoli
  • 4,901
  • 1
  • 19
  • 21
sav
  • 2,064
  • 5
  • 25
  • 45

2 Answers2

1

Sure you can't. If your query works with Workbench, this sounds like a .net bug. I suggest you try "stupid" solutions like using backticks (after the @ and after Analyzed... sorry, Stack Overflows autoformatting doesnt allow me to show you what I mean) or changing the variable's name.

Federico Razzoli
  • 4,901
  • 1
  • 19
  • 21
  • 1
    Thanks for the tip, I think my .Net program is treating the variables starting with @ as parameters. And since I have not used any, this causes the error. I've tried surrounding the variables with backticks like `@Analysed` – sav Apr 26 '13 at 00:33
  • I don't know if I was able to express what I mean - and stackoverflow formatting doesn't help. The start backtick should be between @ and A. This way MySQL understands it is a session variable. If not, it considers it as a local variable. Maybe it was already clear, but I wasn't sure... – Federico Razzoli Apr 27 '13 at 12:48
  • I think I know what you are saying – sav Apr 28 '13 at 23:21
0

How to use MySQL user-variables with ADO.NET seems to have the answer to this I needed to add "allow user variables" to the connection string

Community
  • 1
  • 1
sav
  • 2,064
  • 5
  • 25
  • 45