Best approach to load textfile to MySQL 1

[JoJ]

My first instinct is to send a "LOAD DATA INFILE ..." string to the executeUpdate() method. So far none of my tests seem to work. It may have something to do with Java's handling of the file path.

 

[sedj]

My first instinct is to think you have not posted enough information for anyone to help you.

Errors ? Examples ? Data types ?

My second instinct is to think why don't you use the "mysqlimport" exe that comes with MySQL ?

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[JoJ]

My mistake ... m trying to construct a db application. Much of its source data (flat files, no delimeters) is to be loaded in a MySQL table (3 columns: trans#, batch#, line) on the network. [java 1.5 & MySQL 4.0 on XP's]

I keep getting an SQLException.

I pass something similar to the ff (i removed the TRY..CATCH block for visibility):

String fn = "V:\\\\Devt\\\\ProjA\\\\test3.dat";
String tb = "tblFlat";

sqlText = "LOAD DATA INFILE \"" + fn + "\" INTO TABLE " + tb + " (line)";

UpdateTableBuilder utb = new UpdateTableBuilder(drvConn, urlConn, uidConn, pwdConn);
			
utb.executeUpdateTable(sqlText);

to the following builder class:

import java.sql.*;
import javax.swing.table.*;

public class UpdateTableBuilder {
    
    Connection connection;


    public UpdateTableBuilder(String drvConn, String dbnConn,
			      String uidConn, String pwdConn)
        throws ClassNotFoundException, SQLException
    {

	Class driver = Class.forName(drvConn);

	connection = 
	DriverManager.getConnection(dbnConn, uidConn, pwdConn);
    }

    public int executeUpdateTable(String query)
        throws SQLException
    {

	if (connection == null)
	    throw new 
	    IllegalStateException("Connection already closed.");

	
	Statement statement =
	    connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
            ResultSet.CONCUR_UPDATABLE);
	
	int n = statement.executeUpdate(query);
	
	return n;
    }

    protected void finalize() { close(); }
}

 

[sedj]

Why not just use the mysqlimport exe ? It would be quicker !

If you must use Java :

SQLException ? A Stack Trace would help ... SQLException's come in many flavours.

Have you tried changing :

String fn = "V:\\\\Devt\\\\ProjA\\\\test3.dat";

to :

String fn = "V:/Devt/ProjA/test3.dat";



--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[JoJ]

Ill keep your mysqlimport.exe suggestion in mind. But at this point m curious as to how java, jdbc & mysql are handling the path.

I cant cut&paste texts from the console so im typing the error stack portion that you might be looking for:

java.sql.SQLException: General error message from server:
"File 'V:\Devt\ProjA\test3.dat' not found (Errcode: 2)"

Now that im typing this, i just realized that the server's file system directory could be mapped differently.

Correct me if im wrong, but if I continue this approach, the flat file should always be accessible from the server side, not from the client side.

Should the application copy the file from client to server first before LOADing, or is there a more efficient approach? mysqlimort.exe?

 

[sedj]

Errmmm, yes the file will need to be on the server !

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[JoJ]

I added the LOCAL option in the string, and that worked:

sqlText = "LOAD DATA [b]LOCAL[/b] INFILE \"" + fn + "\" INTO TABLE " + tb + " (line)";

I may not have realized it sooner if you had not gotten me to type the stack trace which gave me the chance to really stare at the drive letter V: -- Thank you.

 

[JoJ]

In this forum, I read a couple more of your tips in different threads. One was replaceAll("\\\\", "/"); and another about the forward slash being usable in both unix and windows.

I applied those tips to my tests and am happy with results. For other members who might find it useful, Here's my code at the end of the day:

	public void setSQL(String rdbms, String file, String table) {
		
		StringBuffer sb = new StringBuffer();
		
		file = file.replaceAll("\\\\", "/");
		
		if (rdbms.equals("mysql")) {
		
	    	sb.append("LOAD DATA LOCAL INFILE ");
	    	sb.append("'");
	    	sb.append(file);
	    	sb.append("'");
	    	sb.append(" INTO TABLE ");
	    	sb.append(table);
			sb.append(" (line);");
	
			sqlText = sb.toString();
}
}

A star for you