Solved

How to issue a SQL command that has MySQL @variables

Posted on 2008-06-10
5
279 Views
Last Modified: 2010-04-23
Hello,

I want to load a file in MySql and figured out an SQL statment that does the job

LOAD DATA INFILE 'FileList.txt' INTO TABLE `DiskSpace`.`TBL_FileList`
  FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' (@Name,@Path,dSize,@LastAccess,@LastChange,@Creation,@Extension)
  SET dteDate = '2008-03-08',
      szSite= 'Antwerp082',
      szServer='AOT30201',
      szName = REPLACE(SUBSTRING(@Name, 1, 255), '"', ''),
      szPath = REPLACE(SUBSTRING(@Path, 1, 255), '"', ''),
      dteLastAccess=STR_TO_DATE(@LastAccess, '%d/%m/%Y %H:%i'),
      dteLastChange=STR_TO_DATE(@LastChange,'%d/%m/%Y %H:%i'),
      dteCreation=STR_TO_DATE(@Creation, '%d/%m/%Y %H:%i'),
      szExtension = SUBSTRING(@Extension, 1, 25);

Now I want to use this SQL statement into VB.net application but that does not go right

szSQL = szSQL & "LOAD DATA INFILE 'FileList.txt' INTO TABLE `DiskSpace`.`TBL_FileList`"
szSQL = szSQL & "  FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' (@Name,@Path,dSize,@LastAccess,@LastChange,@Creation,@Extension)"
szSQL = szSQL & "  SET dteDate = '2008-03-08',"
szSQL = szSQL & "      szSite= 'Antwerp082',"
szSQL = szSQL & "      szServer='AOT30201',"
szSQL = szSQL & "      szName = REPLACE(SUBSTRING(@Name, 1, 255),'" & Chr(34) & "',''),"
szSQL = szSQL & "      szPath = REPLACE(SUBSTRING(@Path, 1, 255),'" & Chr(34) & "',''),"
szSQL = szSQL & "      dteLastAccess=STR_TO_DATE(@LastAccess, '%d/%m/%Y %H:%i'),"
szSQL = szSQL & "      dteLastChange=STR_TO_DATE(@LastChange,'%d/%m/%Y %H:%i'),"
szSQL = szSQL & "      dteCreation=STR_TO_DATE(@Creation, '%d/%m/%Y %H:%i'),"
szSQL = szSQL & "      szExtension = SUBSTRING(@Extension, 1, 25);"
objMySqlCommand.CommandText = szSQL
objMySqlCommand.ExecuteNonQuery()

Excuting this give me an error @Name not defined.
Adding this

objMySqlCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 255)
objMySqlCommand.Parameters.Add("@Path", SqlDbType.NVarChar, 255)
objMySqlCommand.Parameters.Add("@LastAccess", SqlDbType.NVarChar, 25)
objMySqlCommand.Parameters.Add("@LastChange", SqlDbType.NVarChar, 25)
objMySqlCommand.Parameters.Add("@Creation", SqlDbType.NVarChar, 25)
objMySqlCommand.Parameters.Add("@Extension", SqlDbType.NVarChar, 25)
objMySqlCommand.Prepare()

gives me an SQL error because it replace @Name ... by NULL.

OK. Trying to be a smartass I added
objMySqlCommand.Parameters("@Name").Value = "@Name"
objMySqlCommand.Parameters("@Path").Value = "@Path"
objMySqlCommand.Parameters("@LastAccess").Value = "@LastAccess"
objMySqlCommand.Parameters("@LastChange").Value = "@LastChange"
objMySqlCommand.Parameters("@Creation").Value = "@Creation"
objMySqlCommand.Parameters("@Extension").Value = "@Extension"

give me  an error "String was not recognized as a valid DateTime".

So now I do not know it any longer.

Does anyone know how to pass the @var straight to the MySQL engine?
0
Comment
Question by:Marc_Engrie
  • 2
  • 2
5 Comments
 
LVL 7

Expert Comment

by:AUmidh
ID: 21756525
I did not program it myself but visit the following it solve your problem
http://bugs.mysql.com/bug.php?id=10870
use ?var instread of @var

0
 

Author Comment

by:Marc_Engrie
ID: 21758517
:-(

Replacing @var with ?var does not solve the problem.

                szSQL = szSQL & "LOAD DATA INFILE '" & szFile.Replace("\"c, "/"c) & "' INTO TABLE `DiskSpace`.`TBL_FileList`"
                szSQL = szSQL & "  FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' (?Name,?Path,dSize,?LastAccess,?LastChange,?Creation,?Extension)"
                szSQL = szSQL & "  SET dteDate = '" & szDate & "',"
                szSQL = szSQL & "      szSite= '" & szSite & "',"

I get "Parameter '?Name' must be defined."
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21783050
szSQL = szSQL & "  FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' (@Name,@Path,dSize,@LastAccess,@LastChange,@Creation,@Extension)"

is wrong, should simply be:
szSQL = szSQL & "  FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' (Name,Path,dSize,LastAccess,LastChange,Creation, Extension)"
you list there the field names you want to fill with "constant" data (with the SET part later.

then:
szSQL = szSQL & "  SET dteDate = '2008-03-08',"
szSQL = szSQL & "      szSite= 'Antwerp082',"
szSQL = szSQL & "      szServer='AOT30201',"
szSQL = szSQL & "      szName = REPLACE(SUBSTRING(@Name, 1, 255),'" & Chr(34) & "',''),"
szSQL = szSQL & "      szPath = REPLACE(SUBSTRING(@Path, 1, 255),'" & Chr(34) & "',''),"
szSQL = szSQL & "      dteLastAccess=STR_TO_DATE(@LastAccess, '%d/%m/%Y %H:%i'),"
szSQL = szSQL & "      dteLastChange=STR_TO_DATE(@LastChange,'%d/%m/%Y %H:%i'),"
szSQL = szSQL & "      dteCreation=STR_TO_DATE(@Creation, '%d/%m/%Y %H:%i'),"
szSQL = szSQL & "      szExtension = SUBSTRING(@Extension, 1, 25);"

must indeed use ? instead of @ for mysql:

szSQL = szSQL & "  SET dteDate = '2008-03-08',"
szSQL = szSQL & "      szSite= 'Antwerp082',"
szSQL = szSQL & "      szServer='AOT30201',"
szSQL = szSQL & "      szName = REPLACE(SUBSTRING(?Name, 1, 255),'" & Chr(34) & "',''),"
szSQL = szSQL & "      szPath = REPLACE(SUBSTRING(?Path, 1, 255),'" & Chr(34) & "',''),"
szSQL = szSQL & "      dteLastAccess=STR_TO_DATE(?LastAccess, '%d/%m/%Y %H:%i'),"
szSQL = szSQL & "      dteLastChange=STR_TO_DATE(?LastChange,'%d/%m/%Y %H:%i'),"
szSQL = szSQL & "      dteCreation=STR_TO_DATE(?Creation, '%d/%m/%Y %H:%i'),"
szSQL = szSQL & "      szExtension = SUBSTRING(?Extension, 1, 25);"

as must the parameter code:
objMySqlCommand.Parameters.Add("?Name", SqlDbType.NVarChar, 255)
objMySqlCommand.Parameters.Add("?Path", SqlDbType.NVarChar, 255)
objMySqlCommand.Parameters.Add("?LastAccess", SqlDbType.NVarChar, 25)
objMySqlCommand.Parameters.Add("?LastChange", SqlDbType.NVarChar, 25)
objMySqlCommand.Parameters.Add("?Creation", SqlDbType.NVarChar, 25)
objMySqlCommand.Parameters.Add("?Extension", SqlDbType.NVarChar, 25)


>gives me an SQL error because it replace @Name ... by NULL.
now, this makes me "review" the entire code: you want to change the value loaded by the file, so you actually don't defined this as parameter at all, but;


0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21783068
sorry, submitted before posting the rest...

your initial code works when you run it in mysql directly, right? but fails with vb.net, due to the @...

I think, my first attempt would be to put the code into a stored proc, and run just the proc from the vb.net code...
avoids all these problems :)
0
 

Accepted Solution

by:
Marc_Engrie earned 0 total points
ID: 22013615
All suggestions workarounds did not work. So I went down to the basics again.

Create a user that does not require a password :-(   and create a SQL statement in a text file and then issue a shell CMD to call MySQL with the text file as command line option. This works great. So the only drawback is the user with no password.  Well live is based on compromises.
I can live with this :-)

0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question