jdbc: values from mysql table always null

--f46d040714cdae951104b3e88f92
Content-Type: text/plain; charset=ISO-8859-1

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 |

--f46d040714cdae951104b3e88f92--