Description:
I'm trying to make a simple query from my java program using mysql connector/j,
the query is "load data infile...";
Here my code:
MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setUser(dbUser);
dataSource.setPassword(dbPass);
dataSource.setDatabaseName(dbName);
dataSource.setServerName(serverName);
dataSource.setPort(3307);
//dataSource.setJdbcCompliantTruncation(false);
Connection c = dataSource.getConnection();
sql = "LOAD DATA INFILE 'C:/Users/Jess/DB/PRUEBA.txt'"+
"REPLACE INTO TABLE DB.prueba";
PreparedStatement p = c.prepareStatement(sql);
int x = p.executeUpdate();
p.close();
c.close();
Now, on the file PRUEBA.txt there is some field setted in null,
Here the file:
1 4
2 \N
3 \N
4 6
First field is a id(int,pk....) the second one is int, default NULL.
When the executeUpdate() is called, a error is displayed:
java.sql.SQLException: Incorrect integer value: 'N
' for column 'charaaa' at row 2
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2333)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2318)
at com.becoblohm.cr.PruebaConexion.main(PruebaConexion.java:105)
I made some tests:
First I tried to execute the query directly from mysql (using mysql client from a terminal/console), and got the same error. Looking on web, I found that I should comment the line sql-mode="STRICT_TRANS_TABLES.." on my.ini, that I shouldn't use Strict-mode, and this worked only on mysql client. (By worked, I should mention sometimes insert '0' instead NULL).
When using connector/j the problem persists. I read that connector/j forces strict mode, so I should set dataSource.setJdbcCompliantTruncation(false); . When I set JdbcCompliantTruncation=false, the error is not displayed but no row is inserted.
How to repeat:
Windows 7.
mysql 5.5
mysql-connector-java-5.1.17.jar
java version "1.6.0_12"
This is the table:
CREATE TABLE `prueba` (
`idprueba` int(11) NOT NULL,
`charaaa` int(11) DEFAULT NULL,
PRIMARY KEY (`idprueba`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$
This is the query string:
sql = "LOAD DATA INFILE 'C:/Users/Jess/DB/PRUEBA.txt' REPLACE INTO TABLE DB.prueba";
This is the file:
1 4
2 \N
3 \N
4 6
This is the error:
java.sql.SQLException: Incorrect integer value: 'N
' for column 'charaaa' at row 2
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2625)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2119)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2333)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2318)
at com.becoblohm.cr.PruebaConexion.main(PruebaConexion.java:105)