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
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
273 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
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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
INDEX does not make a difference, why? 10 60
Help with error in Query 2 31
Output in PHP throwing alignment of data off issue 12 43
Inserting data into database 10 35
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…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

860 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