Solved

System.Data.Odbc.OdbcCommand("LOAD DATA LOCAL INFILE 'Current_path' INTO TABLE importcsv... I need to pass the filename into this command.. its giving me a errocode 22.. Please help

Posted on 2009-05-08
14
270 Views
Last Modified: 2013-12-17
System.Data.Odbc.OdbcCommand("LOAD DATA LOCAL INFILE 'Current_path' INTO TABLE importcsv... I need to pass the filename into this command.. its giving me a errocode 22.. Please help

'Current_path'  I have assigned the file name to this, The user select the file from a directory and full path gets copied to a textbox. I am trying to pass this fullpath into the LOAD data command but it says file not found.
but if i enter the same path into the LOAD DATA cmd it works!
How can I pass this dynamic value into the command?
0
Comment
Question by:Pavithra_S
  • 9
  • 5
14 Comments
 
LVL 39

Expert Comment

by:abel
Comment Utility
Error 22 is in this case, file not found, as you have noticed already.

If the mysql server is not local, it will not work. If the pathname contains an unc path, you may have problems (see bug report here: http://bugs.mysql.com/bug.php?id=28520, but the report is quite old), and if the final command you create is not containing the single quotes, it will not work either.

Yet more, it might have problems with spaces in paths, I had that in the past myself. Try to use a path that is very short and on the same machine as the mysql server and see if that helps.
0
 
LVL 39

Assisted Solution

by:abel
abel earned 500 total points
Comment Utility
Btw, I forgot a major point: did you double escape the backslashes? I.e., if you would do it from the commandline you would use something like:

load data LOCAL infile 'c:\\test.txt' into tablex...
you should do the same from your command. To be on the safe side, I usually change the backslashes to forward slashes, which works fine on windows (actually, under the hood windows uses forward slashes exclusively) and you don't need to worry about any escaping business:

string currentPath = @"c:\test.txt";currentPath = currentPath.Replace("\\", "/");

0
 

Accepted Solution

by:
Pavithra_S earned 0 total points
Comment Utility
OdbcCom = new System.Data.Odbc.OdbcCommand("LOAD DATA LOCAL INFILE" + " '" + Current_path + " '"+ "INTO TABLE importcsv FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (PatentNo,Title,Inventor,Assignee,DateofPublication,ApplicationDate,FamilyMembers) ", OdbcCon);

this worked for me,

Thanks for your help! LOAD DATA LOCAL INFILE works for me.. my file is local on my machine and MySQL server is on another...
0
 

Author Comment

by:Pavithra_S
Comment Utility
Can u please delete my comment right now?. I will repost this.. Thanks!
0
 
LVL 39

Expert Comment

by:abel
Comment Utility
I see you are quite new here. The experts do not have the power to delete comments or questions. Since this question has a solution (file is not on the right server), you, as the asker, can accept the comment that explained that principle as the solution (which is the first one above). That will close this question. If you post a new q. it is a good idea to use the "related q." button.

0
 
LVL 39

Expert Comment

by:abel
Comment Utility
If you have questions about closing questions and awarding points, I'll be happy to help you. But before you ask, please read this on closing questions and this on awarding points.
0
 

Author Comment

by:Pavithra_S
Comment Utility
Thank you for your suggestions. If I am right you explained that you cannot import a file if it is not on the same machine as the server.. but I am able to do this..
 
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 39

Expert Comment

by:abel
Comment Utility
I'm sorry, I think I misunderstood you then. Your previous comment I understood of it that you could not get the file because it was on a remote server.

If you do it from the commandline, it will work, even if you use the mysql client from a local machine and your server is remote.

If you do it through odbc, you add an extra layer. The command is send to the server untouched and the server has no idea where to get the file with the local name.

What you can do about that is simple: use the absolute UNC path to your file instead, i.e.: \\mylocalcomputername\sharename\directory\file.txt (remember to double-escape).
0
 
LVL 39

Expert Comment

by:abel
Comment Utility
mmm, it maybe something else. According to this link: http://bugs.mysql.com/bug.php?id=14422 you should remove the word LOCAL to get it working (bottom of that post). Even though that link shows a different error it is worth trying.
0
 
LVL 39

Expert Comment

by:abel
Comment Utility
And... on the bottom of this thread is a hint that seems to solve some odbc problems. It doesn't really seem related, but by default, using LOCAL INFILE will be disabled for security reasons. Can you check whether your setting is correct? http://cgfa.telepac.pt/pub/mysql/doc/refman/5.1/en/load-data-local.html

// my.cnf
[odbc]
local-infile=1

or add option=65535 to the odbc connection string.
0
 

Author Comment

by:Pavithra_S
Comment Utility
OdbcCom = new System.Data.Odbc.OdbcCommand("LOAD DATA LOCAL INFILE" + " '" + Current_path + " '"+ "INTO TABLE importcsv FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES ", OdbcCon);

What I was trying to say that the line above works good and I am able to import the file successfully!! Sorry I guess I mislead you by asking to delete the comment..
I said that Coz i had my column names on that... but its ok
Your suggestion of escaping backslash did help me, thank you.. But the solution is the comment I sent... Please let me know how I can assign points to both your comment and My comment as both together gives the right solution..

sorry for having mislead you..

Thank you for your time!
0
 
LVL 39

Expert Comment

by:abel
Comment Utility
You can never assign points to yourself (because that would open the door for any expert assigning themselves points). But what you can do: accept your own comment as a solution and assign points to another comment. Just click "Accept as solution" on your comment and type the total amount of points into the textbox (will appear on the next page) of my comment.

the result will then be: your comment is the answer/solution, my comment is the "assisting solution".
0
 

Author Comment

by:Pavithra_S
Comment Utility
oh ok cool Thanks!
0
 
LVL 39

Expert Comment

by:abel
Comment Utility
excellent. Glad it works now altogether (and I learned something along the way about the limitations and intricacies of INFILE).
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

763 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

8 Experts available now in Live!

Get 1:1 Help Now