hello i'm porting a wbsphere application to tomcat, i have to work on two database on the same server and i've integrated tomcat with atomikos transactionessential. This is my first project with jta, and the oracle dba has told me i don't need xa and two phase commit because the schemas are on the same server. So i've used the non-xa approach with atomikos. the following code on a single schema works fine (commit and rollback as expected):
utx.begin();
conn = //get connection
if (sAzione.equals("1"))
sql = "UPDATE parametri set valore =to_char(sysdate,'dd/mm/yyyy HH24:MI:ss') where id_parametri= 9 ";
//execute query
sql = "SELECT SEQ_LOTTO.nextval id FROM dual";
//other operations
sql = "INSERT INTO LOTTO (id_lotto, numero_lotto, id_area, id_stato_lavorazione, id_autore, id_tipo)";
sql = sql + " VALUES (" + id + ", " + numero + ", " + request.getParameter("idArea") + ",1,"+ session.getAttribute("id_anagrafica")+ "," + request.getParameter("idTipo") + ")";
//execute import and release connection
utx.commit();
in another place , the following oracle function gets called and try to change both schemas, and it returns the code 1 . I don't know pl-slq but it appears to me the return value would mean there has been an exception at first delete , yet the second delete gets executed and committed . Someone could explain me the meaning of this function ? below is the function and the code that calls it
create or replace FUNCTION FN_ELIMINA_RACC (idracc IN NUMBER, idlotto IN NUMBER)
RETURN NUMBER
IS
retvalue NUMBER (1);
BEGIN
retvalue := 1;
DELETE FROM npa_collaudo.documento_raccomandata
WHERE id_raccomandata = idracc;
retvalue := 2;
DELETE FROM raccomandata_out
WHERE id_racc_out = idracc;
retvalue := 3;
IF idlotto != 0
THEN
UPDATE lotto
SET numero_racc = numero_racc - 1
WHERE id_lotto = idlotto;
END IF;
retvalue := 0;
COMMIT;
RETURN retvalue;
EXCEPTION
WHEN OTHERS
THEN
RETURN retvalue;
END;
//the calling code
utx.begin();
//get connection
sql = "FN_ELIMINA_RACC(" + idRacc + ", " + idLotto + ");";
ret = connessioneDB.eseguiSP(sql);
if (!(ret == 0)){
throw new Exception("exception");
utx.commit();
//since it returns 1 an exception is raised and rollback gets called
thank you in advance for any help
EDIT: investigating further into this (awful) code , and thanks to your answers ,i've found this into the infamous "eseguiSP" :
//strSQL is "FN_ELIMINA_RACC(..."
DBOracle dbType = new DBOracle();
String SQL = "";
int retValue = 0;
SQL = " DECLARE ";
SQL = SQL + " ret NUMBER; ";
SQL = SQL + " BEGIN ";
SQL = SQL + " ret := " + strSQL;
SQL = SQL + " END; ";
try {
stmt = conn.prepareCall(SQL);
retValue = stmt.executeUpdate(SQL);
} catch (SQLException e) {
//retValue = false;
}
return retValue;
And i've changed it to:
c = ds.getConnection();
java.sql.CallableStatement cstmt = c.prepareCall("{?=call FN_ELIMINA_RACC(?,?)}");
cstmt.registerOutParameter(1,java.sql.Types.INTEGER);
cstmt.setInt(2, idRacc);
cstmt.setInt(3, idLotto);
cstmt.execute();
ret = cstmt.getInt(1);
now it works fine (or at least returns 0).Why the old piece of code always returned 1 even if it deleted records from raccomandata_out?
How do you know the function is returning 1? The exception you're throwing isn't reporting the ret value. The call itself may be broken - try removing the trailing ; from the sql string. Although you ought to get a more helpful exception from eseguiSP(sql) if that's the case, but it might be hidden elsewhere in your code (maybe something further up is adding something that makes it looks like a 1 was returned?); and neither delete should take effect, unless it's trying to treat it as two commands and only complaining when it sees the second is null. That sounds unlikely but you never know, so I'd try removing the semi-colon anyway.
Also, you should probably be using bind parameters for the call, not embedding the values in sql.
You also said rollback would be called on exception, and you have utx.commit(), but that's redundant with a commit in the function too.