File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Oracle/Java - JDBC call hangs!!!?? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle/Java - JDBC call hangs!!!??" Watch "Oracle/Java - JDBC call hangs!!!??" New topic
Author

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

Zorin Speigler
Greenhorn

Joined: Oct 31, 2003
Posts: 3
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

Joined: Jul 09, 2001
Posts: 1879

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

Joined: Oct 31, 2003
Posts: 3
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

Joined: Jul 09, 2001
Posts: 1879

try using executeUpdate() instead of just execute()
Zorin Speigler
Greenhorn

Joined: Oct 31, 2003
Posts: 3
Thanks.
executeUpdate() worked out fine. However, I am still wondering "why?". Can you tell me?
Wayne L Johnson
Ranch Hand

Joined: Sep 03, 2003
Posts: 399
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!
 
jQuery in Action, 2nd edition
 
subject: Oracle/Java - JDBC call hangs!!!??
 
Similar Threads
Insert date and time into Oracle database
NullPointerException at stmt.execute
Simple JDBC problem - Connection throws Nullpointerexception
Webapp hang when calling stored procedure
Everytime getting an empty ResultSet