prepareStatement returns 0?

[Jeppe Sommer]

I have a problem. PrepareStatement returns 0 in my code but the query returns the correct result in my sql editor?!

I�m working within a servlet. First I do make a database connection and share that connection through the servlet lifetime... Inside the doPost method I first call a synchronized method which do the following task:

synchronized method_1 ()
1) SELECT MAX(id)
2) UPDATE date for the max(id) (I use type 2 historic)
3) INSERT request values
4) SELECT MAX(id) (from the insert)
5) return new MAX(id)

- That works fine. After that I call yet another synchronized method where I do the same task, just in another table:

synchronized method_2 ( int invoiceID )
1) SELECT MAX(id) returns 0 //FAILS!
2) ...

I close my resultSet after every Query. I have tried to close my preparedStatement too (stat.close()). I have also tried to close my connection after doing the task in each synchronized method, and open a new connection. That doesn�t work either...

The code looks like the following:

 
 
class InvoiceSetup extends HttpServlet{
 
public void doPost ( HttpServletRequest request, HttpServletResponse response ) throws ServletException, IOException {
 
try {
 
//request.getParameter
taxTotalCode = request.getParameter("taxTotalCode");
...
 
//Create database connection
conn = new dbConn.DBConn();
 
//call the first synchronized method
//It works fine!
int invoiceID = insertInvoice( compID );
 
//call the second synchronized method.
//Here the SELECT MAX(id) returns 0?
int buyerPartyID = insertBuyerParty( invoiceID );
...
 }
 catch ( Exception e ){
    
    e.printStackTrace();
 } 
 finally {
   stat.close();
   conn.close();
 }
}
 
  public synchronized int insertInvoice( compID )throws Exception {
     
     1) SELECT MAX(id) 
     2) UPDATE date for the max(id) (I use type 2 historic)
     3) INSERT request values
     4) SELECT MAX(id) (from the insert)
     5) return new MAX(id)
  }
 
  public synchronized int insertBuyerParty( invoiceID ) throws Exception {
     
     //SELECT MAX(id) //FAILS!
      stat = conn.prepareStatement("SELECT MAX(oio_BuyerPartyID) FROM oio_buyerparty WHERE oio_buyerparty.oio_EditableIsNew=? AND oio_buyerparty.oio_invoiceID=?");
      stat.setInt( 1, OIO_EDITABLE_IS_NEW );
      stat.setInt( 2, invoiceID );
      rs = stat.executeQuery();
    
      if ( rs.next() ){
     :confused:  
           //Print out 0?
           //Same query returns i.e. max id = 68 in my sql editor???   
   System.out.println( rs.getInt(1) );
      }
      rs.close();
      ... 
  }
 
}
 

[ January 29, 2005: Message edited by: Jeppe Fjord ]
[ January 29, 2005: Message edited by: Jeppe Fjord ]

[Jeanne Boyarsky]

Jeppe,
Are you sure the inserts are being committed to the database? Also, out of curiousity, why are you using a synchronized method rather than a transaction?

[Bear Bibeault]

Jeppe, please do not re-post the same question more than once. Please continue any discussion in the original post.
[ January 29, 2005: Message edited by: Bear Bibeault ]