LOAD DATA INFILE problem....

[asenthil]

Hi guys,

I have got a question regarding LOAD DATA INFILE. Can some one save my day.

1. I want to import a txt file into mysql database.
when I use the following query in the mysql> prompt its fine.
which is

when I do it java I am getting error

Error
-----------
java.sql.sqlExc eption: General error message from server:
"File 'D:/Sen.txt' not found <ErrCode: 2>"


But The file already exists

Code:

try {
           con = DriverManager.getConnection(url, "root", "");
            stmt = con.createStatement();
            stmt.executeUpdate(createString);
            String filename = "D:/Sen.txt";
            String tablename = "IMPORT";
stmt.executeUpdate("LOAD DATA INFILE \"" + filename + "\" INTO TABLE " + tablename + " FIELDS TERMINATED BY ','");
            stmt.close();
            con.close();
}

My text file(Sen.txt) is like this...

abc,1
bcd,2
adf,3

I dont know why its say like that
Can anyone help me out in this...

thanks...
senthil.

[123456prakash]

Hi guys,

I have got a question regarding LOAD DATA INFILE. Can some one save my day.

1. I want to import a txt file into mysql database.
when I use the following query in the mysql> prompt its fine.
which is

when I do it java I am getting error

Error
-----------
java.sql.sqlExc eption: General error message from server:
"File 'D:/Sen.txt' not found <ErrCode: 2>"


But The file already exists

Code:

try {
           con = DriverManager.getConnection(url, "root", "");
            stmt = con.createStatement();
            stmt.executeUpdate(createString);
            String filename = "D:/Sen.txt";
            String tablename = "IMPORT";
stmt.executeUpdate("LOAD DATA INFILE \"" + filename + "\" INTO TABLE " + tablename + " FIELDS TERMINATED BY ','");
            stmt.close();
            con.close();
}

My text file(Sen.txt) is like this...

abc,1
bcd,2
adf,3

I dont know why its say like that
Can anyone help me out in this...

thanks...
senthil.

hi dude try this code
try {
Connection con = // getConnection here
// Create the statement
Statement stmt = con.createState ment();
String tablename = "IMPORT";
String filename = "D:\\\\Sen.txt" ;
String query = "LOAD DATA INFILE \"" + filename + "\" INTO TABLE " + tablename;
stmt.executeUpd ate(query);
} catch (Exception e) {
}

[r035198x]

Hi guys,

I have got a question regarding LOAD DATA INFILE. Can some one save my day.

1. I want to import a txt file into mysql database.
when I use the following query in the mysql> prompt its fine.
which is

when I do it java I am getting error

Error
-----------
java.sql.sqlExc eption: General error message from server:
"File 'D:/Sen.txt' not found <ErrCode: 2>"


But The file already exists

Code:

try {
con = DriverManager.getConnection(url, "root", "");
stmt = con.createStatement();
stmt.executeUpdate(createString);
String filename = "D:/Sen.txt";
String tablename = "IMPORT";
stmt.executeUpdate("LOAD DATA INFILE \"" + filename + "\" INTO TABLE " + tablename + " FIELDS TERMINATED BY ','");
stmt.close();
con.close();
}

My text file(Sen.txt) is like this...

abc,1
bcd,2
adf,3

I dont know why its say like that
Can anyone help me out in this...

thanks...
senthil.

Must be some directory thing. I've never used it in Java myself. A quick check here shows that you have it right for running from mysql prompt. Java looks for the file starting in the current directory where your code is but mysql looks for the file from the database directory... Still trying to fihure out what would work there if you get it please post and tell us as well.

[asenthil]

Must be some directory thing. I've never used it in Java myself. A quick check here shows that you have it right for running from mysql prompt. Java looks for the file starting in the current directory where your code is but mysql looks for the file from the database directory... Still trying to fihure out what would work there if you get it please post and tell us as well.

Hey another one things...

But when i tried the filename = "C:/test.txt"

itz import the data from the database and load it to the text file perfectly..

but whatz special in this is.... there is no test.txt file in the C:/

Actually test.txt file is a created file by using Query

SELECT * INTO OUTFILE in the java program for export data to a file(C:/test.txt)..

When i run the above program...

there is no test.txt file in the C:/....

But when i tried to run the program again... its throwing exception

that C:/test.txt file is already exist... But there is no test.txt file..

i dont know what is happening here...

What to do for it...

plzz explain me....

[r035198x]

Hey another one things...

But when i tried the filename = "C:/test.txt"

itz import the data from the database and load it to the text file perfectly..

but whatz special in this is.... there is no test.txt file in the C:/

Actually test.txt file is a created file by using Query

SELECT * INTO OUTFILE in the java program for export data to a file(C:/test.txt)..

When i run the above program...

there is no test.txt file in the C:/....

But when i tried to run the program again... its throwing exception

that C:/test.txt file is already exist... But there is no test.txt file..

i dont know what is happening here...

What to do for it...

plzz explain me....

When you run it first time it creates the file right?
When you run it second time, do you delete that file first?

[asenthil]

When you run it first time it creates the file right?
When you run it second time, do you delete that file first?

Ya it is creating a file..... But it is not storing in c:/

i dont know where it is storing......

the file is storing in some other location...

the file is in invisible mode....

[r035198x]

Ya it is creating a file..... But it is not storing in c:/



i dont know where it is storing......



the file is storing in some other location...



the file is in invisible mode....

Let us eliminate all the other possibilities first.

 } catch (Exception e) {
}

is not handling any exception. Change it to

 } catch (Exception e) { 

e.printStackTace();


}

and run it to make sure the program is not throwing any exceptions

[asenthil]

Now also the same result....

i will explain u clearly..

When i want a export data from a database into a text file..

i used the query, SELECT * INTO OUTFILE 'C:/test.txt' FROM TABLE....

there is no test.txt file in C:/..i cant see it...

But when i run the program again, it throwing an exception that
C:/test.txt already exists....

thats what, i came to the conclusion that, the above query creating a file

and exporting the data into that file... but i think that created file is in

invisible mode...

Now i'm trying to importing data from database from a file...

in this i had used the following query...

stmt.executeUpd ate("LOAD DATA INFILE \"" + 'C:/test.txt' + "\" INTO TABLE " + tablename + " FIELDS TERMINATED BY ','");

its working perfectly running.... and importing the data from the test.txt file into

the database...

But whats special in this is.... there is no test.txt file in C:/


Can u understand what i'm saying?

Plzz tell me a solution for this....

thanks for replying...
senthil.

[r035198x]

Now also the same result....



i will explain u clearly..



When i want a export data from a database into a text file..



i used the query, SELECT * INTO OUTFILE \'C:/test.txt\' FROM TABLE....



there is no test.txt file in C:/..i cant see it...



But when i run the program again, it throwing an exception that

C:/test.txt already exists....



thats what, i came to the conclusion that, the above query creating a file



and exporting the data into that file... but i think that created file is in



invisible mode...



Now i\'m trying to importing data from database from a file...



in this i had used the following query...



stmt.executeUpd ate(\"LOAD DATA INFILE \\\"\" + \'C:/test.txt\' + \"\\\" INTO TABLE \" + tablename + \" FIELDS TERMINATED BY \',\'\");



its working perfectly running.... and importing the data from the test.txt file into



the database...



But whats special in this is.... there is no test.txt file in C:/





Can u understand what i\'m saying?



Plzz tell me a solution for this....



thanks for replying...

senthil.

Maybe some blocking as explained here..Wait a minute. I think this is taking the shape of a mysql problem so let me copy your post there as well so you can be able to view replies posted from there and here as well.

[ronverdonk]

As for the file definition, have a look at the MySQL decoumentation regarding the use of file names with and without specifying the LOCAL attribute in your LOADFILE statement:

The LOCAL keyword, if specified, is interpreted with respect to the client end of the connection:

If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full pathname to specify its exact location. If given as a relative pathname, the name is interpreted relative to the directory in which the client program was started.

If LOCAL is not specified, the file must be located on the server host and is read directly by the server. The server uses the following rules to locate the file:

If the filename is an absolute pathname, the server uses it as given.

If the filename is a relative pathname with one or more leading components, the server searches for the file relative to the server's data directory.

If a filename with no leading components is given, the server looks for the file in the database directory of the default database.

Note that, in the non-LOCAL case, these rules mean that a file named as ./myfile.txt is read from the server's data directory, whereas the file named as myfile.txt is read from the database directory of the default database. For example, if db1 is the default database, the following LOAD DATA statement reads the file data.txt from the database directory for db1, even though the statement explicitly loads the file into a table in the db2 database:

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;
Windows pathnames are specified using forward slashes rather than backslashes. If you do use backslashes, you must double them.

For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege.

Ronald :cool: