• Post Reply Bookmark Topic Watch Topic
  • New Topic

VERY URGENT --- Help please....executing SQLs within in EJB method ... failing

 
deepa karkala
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi everyone,
I am trying to execute one mass update sql statement followed by mass insert statement inside an ejb method. EJB is session type. App.server is weblogic. Database is Oracle.
When I call this method, I am capturing the sql statements using JDBC SPY driver. The update and insert statements logged by spy driver can be perfectly executed at TOAD without any error.
But method is throwing an SQL exception for Insert statement --- �non numeric character is found where numeric is expected�
Meanwhile, I called insert first and then update. This time what is happening is, insert statement has been executed properly without any SQL exception, but update failed throwing sql exception � IN and OUT parameters are missing�. Same update will be executed without any exception if I call it first inside the method but insert statement will fail.
So it clearly shows that inside an ejb method, always only the first SQL will be executed properly and 2nd one gives SQL exception no matter whether it is insert or update or delete.

What I think is, Oracle is not properly setting up the parameters to the prepared statement when it tries to execute 2nd SQL in the ejb method. I do not know why.
But when I see JDBC log written by SPY driver, the entire insert statement is perfectly alright and also the update statement. I can just cut and paste them in Toad and can execute them without any error. Now I am not able to execute both of them inside an EJB method and I get above said SQL exceptions.

I would highly appreciate if anyone can tell me what is went wrong.

Thanks in advance.
 
Dana Hanna
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Post some code...
 
deepa karkala
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
inside session bean :
public void massUpdateInsert(){
try{
massUpdate();
massInsert();
}catch(..){}
}
private void massUpdate(){
try{
// get the database connection -- from the pool
// create prepared statement for update --- stmtUpdate
stmtUpdate.setLong(1,...); -- I am setting modified by field
stmtUpdate.executeUpdate();
}
catch(throwable t){ throw .....}
finally{ // close the conn, stmt
}
}
private void massInsert(){
try{
// get the database connection -- from the pool
// create prepared statement for insert---
i=1;
stmt = con.prepareStatement(massInsertRatesSQL);
stmt.setDate(i++,new java.sql.Date(d1.getTime())); -- d1 is of type java.util.Date
stmt.setDate(i++,new java.sql.Date(d2.getTime())); --d2 is of type java.util.Date
stmt.setLong(i++, data.getModifiedBy().longValue());
rows=stmt.executeUpdate();
stmt.executeUpdate();
}
catch(throwable t){ throw .....}
finally{ // close the conn, stmt
}
}
In the parent method massUpdateInsert(), while calling insert, I get sql exception 'character data is found while numeric is expected'
If I call massInsert() first and massUpdate() later in massUpdateInsert(), I get sql exception while executing update statement 'missing' IN and OUT parameter'.
thanks.
 
deepa karkala
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
in the method massInsert(), there is a typo : I am calling stmt.executUpdate() twice. In my actual code, I am calling it only once.
 
Dana Hanna
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I can't see anything glaring... With complete code, I could see better. Why not post the whole class?
 
deepa karkala
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Dana for your response. However... I could figure out the problem. There was a problem in date conversion and I solved it.
thank you once again.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!