jdbc: values from mysql table always null

[Ryan Beckes]

Hello,

I am trying to copy one table to another using jdbc (see code below). The
table is copied fine, but the last column is NULL for all values. I am also
having problems retrieving the value from that table (the value is always
returned as null). If I run the same sql commands via the mysql client, the
table is copied OK.

Thanks,
Ryan





Connection conn = null;
PreparedStatement ps1 = null;
PreparedStatement ps2 = null;
PreparedStatement ps3 = null;
try {
String userName = "user";
String password = "password";
String url = "jdbc:mysql://localhost:3306/plus";
Class.forName ("com.mysql.jdbc.Driver").newInstance ();
conn = DriverManager.getConnection (url, userName, password);
System.out.println ("Database connection established");
String dropQuery="DROP TABLE IF EXISTS CLASSMEMBERSHIPTMP";
ps1 = conn.prepareStatement(dropQuery);
ps1.executeUpdate();
String createTmpQuery="CREATE TABLE IF NOT EXISTS CLASSMEMBERSHIPTMP
(ID bigint(20), SUBCLASS1 varchar(255), SUBCLASS2 varchar(255), SUBCLASS3
varchar(255), SUBCLASS4 varchar(255), SUBCLASS5 varchar(255), SUBCLASS6
varchar(255), CLASSMEMBERSHIP_ITEM_ID bigint(20))";
ps2 = conn.prepareStatement(createTmpQuery);
ps2.executeUpdate();
String copyQuery="INSERT INTO CLASSMEMBERSHIPTMP select * from
CLASSMEMBERSHIP";
ps3 = conn.prepareStatement(copyQuery);
ps3.executeUpdate();
} catch (Exception e) {
LOGGER.info ("Cannot connect to database server");
} finally {
if (conn != null) {
try {
conn.close ();
LOGGER.info ("Database connection terminated");
} catch (Exception e) {
//ignore close errors
}
}
}

mysql> desc CLASSMEMBERSHIP;
+-------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+-------------------------+--------------+------+-----+---------+----------------+
| ID | bigint(20) | NO | PRI | NULL |
auto_increment |
| SUBCLASS1 | varchar(255) | YES | | NULL
| |
| SUBCLASS2 | varchar(255) | YES | | NULL
| |
| SUBCLASS3 | varchar(255) | YES | | NULL
| |
| SUBCLASS4 | varchar(255) | YES | | NULL
| |
| SUBCLASS5 | varchar(255) | YES | | NULL
| |
| SUBCLASS6 | varchar(255) | YES | | NULL
| |
| CLASSMEMBERSHIP_ITEM_ID | bigint(20) | YES | MUL | NULL
| |
+-------------------------+--------------+------+-----+---------+----------------+
8 rows in set (0.01 sec)

mysql> select * from CLASSMEMBERSHIPTMP;
+------+-----------+----------------+----------------------+-----------+-----------+-----------+-------------------------+
| ID | SUBCLASS1 | SUBCLASS2 | SUBCLASS3 | SUBCLASS4 |
SUBCLASS5 | SUBCLASS6 | CLASSMEMBERSHIP_ITEM_ID |
+------+-----------+----------------+----------------------+-----------+-----------+-----------+-------------------------+
| 1 | C1CORE | C1COREC2APPLE | C1COREC2APPLEC3REDAP |
| | | 1 |
| 2 | C1DELMO | | |
| | | 1 |
| 3 | C1JRSIM | | |
| | | 1 |
| 4 | C1MACAD | | |
| | | 1 |
| 5 | P1B5 | | |
| | | 1 |

[Todd Farmer]

Hey Ryan,

Post by Ryan Beckes
I am trying to copy one table to another using jdbc (see code below). The
table is copied fine, but the last column is NULL for all values. I am also
having problems retrieving the value from that table (the value is always
returned as null). If I run the same sql commands via the mysql client, the
table is copied OK.

You say that the same exact commands executed in the CLI work fine and
produce different results than when executed from the JDBC client
application. If that's the case, the most probably root cause is SQL
Mode (for JDBC compliance, Connector/J sets SQL Mode to
"strict_trans_tables"). The various SQL modes are described here:

http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html

The fastest way to see exactly what's happening is to turn on the MySQL
Server general query log, and issue the same statements from both the
CLI and the JDBC application. This will log the statements before they
are executed - including statements to set the SQL Mode - and should
help confirm exactly what the server is receiving from the JDBC application.

Best regards,