Solved

How to issue a SQL command that has MySQL @variables

Posted on 2008-06-10
5
271 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now