Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle/Java - JDBC call hangs!!!??

 
Zorin Speigler
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am a connecting to Oracle database. Everything works perfectly fine until I do
boolResult = stmt.execute(strbufSQL.toString()); //see code below in bold
The programs just hangs in there. No exception thrown. Nothing happens.
I have checked the DB box, it's all fine and running.
I have put the code below. Help me please!!!
private boolean processMedstations(Connection dbConnection)
{
System.out.println ("Entering medstation process");
PreparedStatement checkStmt = null;
ResultSet rs = null;
boolean boolResult = true;
StringBuffer strbufSQL = new StringBuffer("");
Statement stmt = null;
try{
System.out.println ("Trying to check the medstations");
checkStmt = dbConnection.prepareStatement("SELECT ms_nursing_unit,TO_CHAR(ms_update_datetime,'yyyymmddhh24miss') FROM pyxis_medstations WHERE ms_name=?");

checkStmt.setString (1, m_medstation_name);
rs = checkStmt.executeQuery();
}
catch(Exception e) {
System.out.println ("This is in PFSAProcess.java");
e.printStackTrace();
boolResult = false;
AuditLogger.logError(this,e);
return boolResult;
}

try{
if (!(rs.next()))
{
strbufSQL.append("INSERT INTO pyxis_medstations ");
strbufSQL.append("(ms_name,ms_nursing_unit,ms_update_datetime) ");
strbufSQL.append("VALUES('");
strbufSQL.append(m_medstation_name);
strbufSQL.append("','");
strbufSQL.append(m_nursing_unit);
strbufSQL.append("',");
strbufSQL.append("TO_DATE(");
strbufSQL.append(m_transaction_time);
strbufSQL.append(",'yyyymmddhh24miss'))");
}
else
if (Long.parseLong(m_transaction_time) > rs.getLong("ms_update_datetime"))
{
strbufSQL.append("UPDATE pyxis_medstations ");
strbufSQL.append("SET ms_nursing_unit='");
strbufSQL.append(m_nursing_unit);
strbufSQL.append("',");
strbufSQL.append("ms_update_datetime=TO_DATE(");
strbufSQL.append(m_transaction_time);
strbufSQL.append(",");
strbufSQL.append("'yyyymmddhh24miss') ");
strbufSQL.append("WHERE ms_name='");
strbufSQL.append(m_medstation_name);
strbufSQL.append("'");
}
stmt = dbConnection.createStatement();
System.out.println(strbufSQL.toString());
boolResult = stmt.execute(strbufSQL.toString());
}
catch(Exception e) {
System.out.println ("This is in PFSAProcess.java");
e.printStackTrace();
boolResult = false;
AuditLogger.logError(this,e);
return boolResult;
}
finally{
try {
if ( checkStmt != null )
checkStmt.close();
}
catch(Exception e) { }
checkStmt = null;
try {
if ( stmt != null )
stmt.close();
}
catch(Exception e) { }
stmt = null;
}
System.out.println("I succeeded with medstations"+boolResult);
return boolResult;
}
[ October 31, 2003: Message edited by: Zorin Speigler ]
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
could it be possible that another process/user has a record locked that you are trying to retrieve?
Also, try setting the Statement to time out after a certain period of time:
stmt.setQueryTimeout(int seconds);
 
Zorin Speigler
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Jamie.
However, there is no one workig on this project except me. I even cut and paste the query i got out of System.out.println() and execute it thru SQLPlus and neatly created a table with the entries.
I am not sure how timing it out would help, since I need this action to take place.
Do you think the dbConnection object needs to be somehow cleared. This might sound stupid. Help!!!
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
try using executeUpdate() instead of just execute()
 
Zorin Speigler
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks.
executeUpdate() worked out fine. However, I am still wondering "why?". Can you tell me?
 
Wayne L Johnson
Ranch Hand
Posts: 399
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's a bit puzzling ... I tried to re-created this against an Oracle database that I have running on my desktop and I couldn't get it to hang, even when using the ".execute()" statement.
In general, you use "executeQuery()" when doing a 'SELECT', "executeUpdate()" when doing a 'INSERT', 'UPDATE' or 'DELETE', and "execute()" when you either don't know which type you have (because the query may be coming from an outside source) or you have multiple operations specified. So the ".execute()" should work just fine.
It may be that you are running against an older version of Oracle, or have old versions of the JDBC drivers. I used to run into funny situations like this a few years ago, but after updating to the later drivers they went away. That may be an issue.
A couple of additional observations. First, if you put your code inside of the CODE tags it makes it much easier to read.
Second, if something fails in your first try/catch block you return immediately w/out closing either the statement or result set. This can leave database resources open and dangling.
Third, in the main "if" statement, you have the following:

If the record exists in the database and the "ms_update_datetime" is after the parameter, then you will skip both sections of the IF and your "strbufSQL" will be empty, causing an SQL error when you try and do stmt.execute("");
When using Oracle databases, it's important to close both the Statement AND the ResultSet instances. Failing to do so can lead to memory leaks and other database ugliness.
That said, your original code should have worked just fine. I'd say close your resources, make sure you have the latest JDBC drivers, and good luck!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic