[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How to issue a SQL command that has MySQL @variables

Posted on 2008-06-10
5
Medium Priority
?
307 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 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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

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

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 …
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
How to fix display issue, screen flickering issue when I plug in power cord to the machine. Before I start explaining the solution lets check out once the issue how it looks like after I connect the power cord. most of you also have faced this…
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…
Suggested Courses

590 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