Solved

How to issue a SQL command that has MySQL @variables

Posted on 2008-06-10
5
290 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

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 143

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

630 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