Solved

How to issue a SQL command that has MySQL @variables

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 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