JDBC Not returning ResultSet

Question

We're migrating this from WAS 5 to WAS 7 and we're stumped as to why this is now causing a problem. The executeQuery() line is throwing a SQLException saying that it's not returning a ResultSet. Does anyone know what has changed over the course of WAS's life that's not allowing this to work anymore? Is this a JDBC upgrade, or JRE thing?? I've googled around, but I'm really not sure what I should be searching for, so my searches have been unfruitful.

        query = "insert into ST_Users ";
        query += "(ST_U_First_Name, ST_U_Middle_Name,
                               ST_U_Last_Name, ST_U_Facility_Name,
                               ST_U_Last_Trans_ID, ST_U_Last_Trans_Time) ";
        query += "values (?,?,?,?,?,?)";
        query += ";select SCOPE_IDENTITY() as UserId";

        pStmt = tokenConn.prepareStatement(query);
        pStmt.setString(1, user.getFirstName());
        pStmt.setString(2, user.getMiddleName());
        pStmt.setString(3, user.getLastName());
        pStmt.setString(4, user.getFacilityName());
        pStmt.setString(5, sysId.getSystemId());
        pStmt.setDate(6, new java.sql.Date(Calendar.getInstance()
                                         .getTime().getTime()));

        resultSet = pStmt.executeQuery();

TIA

What is the query actually returning? Also, what are the details of the SQLException? There should be a code and state that may give hints as to the problem... — BenCole, Feb 23 '12 at 19:15

score 1 · Answer 1 · answered Feb 23 '12 at 19:22

1

You have two queries there. You should execute the first one with executeUpdate(), and then execute the second one with executeQuery().

answered Feb 23 '12 at 19:22

JB Nizet

678,734
91
1,224
1,255

score 1 · Answer 2 · answered Feb 23 '12 at 19:28

I realise this isn't a straight answer but I think you could do the same thing (ie. return a generated key) by using the generated key features of the JDBC api.

    query = "insert into ST_Users ";
    query += "(ST_U_First_Name, ST_U_Middle_Name,
                           ST_U_Last_Name, ST_U_Facility_Name,
                           ST_U_Last_Trans_ID, ST_U_Last_Trans_Time) ";
    query += "values (?,?,?,?,?,?)";

    pStmt = tokenConn.prepareStatement(query);
    pStmt.setString(1, user.getFirstName());
    pStmt.setString(2, user.getMiddleName());
    pStmt.setString(3, user.getLastName());
    pStmt.setString(4, user.getFacilityName());
    pStmt.setString(5, sysId.getSystemId());
    pStmt.setDate(6, new java.sql.Date(Calendar.getInstance()
                                     .getTime().getTime()));

    pStmt.executeUpdate(Statement.RETURN_GENERATED_KEYS);
    ResultSet key = pStmt.getGeneratedKeys();

    key.next(); // should always be true ... you should throw an exception if it isn't
    long generatedKey = key.getLong(1);

I suspect that somewhere in the depths of WAS the above looks like just an insert statement, so it skips the result set.

JDBC Not returning ResultSet

2 Answers2