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
277 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
[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
  • 9
  • 5
14 Comments
 
LVL 39

Expert Comment

by:abel
ID: 24339573
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
ID: 24339796
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
ID: 24340322
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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

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

Expert Comment

by:abel
ID: 24340411
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
ID: 24340449
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
ID: 24340531
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
 
LVL 39

Expert Comment

by:abel
ID: 24340828
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
ID: 24340858
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
ID: 24340911
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
ID: 24341219
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
ID: 24341276
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
ID: 24341279
oh ok cool Thanks!
0
 
LVL 39

Expert Comment

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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

726 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