Calling executeQuery after executeUpdate to get trigger-filled values...

bganoush
bganoush used Ask the Experts™
on
I have some JDBC code that creates a connection and then calls executeUpdate to insert a new record.  Now the table has some triggers for the insert statement that seems to complete the record by adding a status and then a status message. How can I get these new values from the table?  The current code looks like this:

Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(SUBMIT_SQL);
pstmt.setInt(1, uniqueKey);
pstmt.setString(2, issueNo);
int retCode = pstmt.executeUpdate();
System.out.println("executeUpdate retCode: " + retCode);
if (retCode != 1)
      return (false);

I have a SQL statement called "GETSUBMITTED_SQL" that keys on the uniqueKey to get the record but I would rather not make another call... is there a more efficient way to get the data from the record?  The fields I want to get are "status" and "statusMsg" (INT and VARCHAR). The database is Oracle.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You could create a stored procedure on the database and call that instead of doing the insert.

Author

Commented:
I don't have access to CREATE... Only INSERT, UPDATE and SELECT.
Distinguished Expert 2018

Commented:
I don't have access to CREATE... Only INSERT, UPDATE and SELECT.

You should ask the db admin to change the procedure to return the values you need. Otherwise your only solution is a second db call.

Author

Commented:
I was thinking more of doing this from a Java point of view... I have some code that does an insert and then gets the inserted item's generated keys and returns that key. I basically want to do the same thing but instead of getting the generated key, I want to get the actual record including these trigger-generated values.
Distinguished Expert 2018

Commented:
Java by default returns only the key when you insert or update a record. If you want to get other data I'm afraid you will have to use a separate query.
You can at least get the newly created key at the same time as executing the INSERT (or UPDATE) statement like this:
            
stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) ;
stmt.executeUpdate();
                  
ResultSet rs = stmt.getGeneratedKeys() ;

Then you'll need to do a SELECT after that using this key to get the new values.

Doug

Author

Commented:
@girionis:

I guess I could just reuse the same connection and call executeQuery just after the update?  At least this would save having to create a new connection (or get a new connection from a pool)...
Yes that's right you can use the same connection for the SELECT.

Doug

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial