MySQL rollback problem.

[Kusno]

I have this method :

public void ExecuteSQL(String SQL)
{
Koneksi getCn = new Koneksi();
Connection cnExecute = null;
try {
cnExecute = getCn.getConnection();
int rowNgefek = 0;
Statement stExecute = cnExecute.createStatement();
cnExecute.setAutoCommit(true);
stExecute.executeUpdate(SQL, rowNgefek);
cnExecute.commit();
closingConnection (cnExecute,stExecute);
}
catch (SQLException e)
{
System.out.println(e.toString());
}
}

If insert/delete/update raise error, I want to rollback all transactions.
It means I have to use cnExecute.rollback();
but I dont know where I should put that code..
In catch(SQLException e) does not recognize cnExecute.rollback();

Thanks,

Kusno

[Kusno]

I have found the answer. Thanks anyway.

public class Data
{
Connection cnExecute = null;
int rowNgefek = 0;
Statement stExecute;


public void ExecuteSQL(ArrayList<String>  SQLArray)
{
Koneksi getCn = new Koneksi();
String SQL;
rowNgefek = 0;
try {
cnExecute = getCn.getConnection();
stExecute = cnExecute.createStatement();
cnExecute.setAutoCommit(false);
for (int i=0;i <= SQLArray.size()-1; i++)
{
SQL = SQLArray.get(i).toString().trim();
rowNgefek = stExecute.executeUpdate(SQL);
}
}
catch (SQLException e)
{
rowNgefek = 0;
JOptionPane.showMessageDialog(null, e.toString(),"Warning",JOptionPane.OK_OPTION);
}
finally
{
if(rowNgefek<=0)
{
try
{
cnExecute.rollback();
}
catch(SQLException e)
{


}
}
else
{
try
{
cnExecute.commit();
}
catch(SQLException e)
{
System.out.println(e.toString());
}
}
closingConnection (cnExecute,stExecute);
}



}

[Jens]

I can be wrong here, but I think that rollback only works for a current transaction. If you use commit, you close that transaction.

So you should catch your error before the commit, and roll back then too. If all is fine you can proceed to commit.

Also, autocommit should be set to false I think. Else your program is going to commit as soon as you release your query.

Edit: Ack, excuse me. Sort of scrolled down as soon as I saw the problem and thought of a possible answer. I should watch before I type :p. Glad you found a solution though.

[bugmenot]

public static boolean INSERT(Payment argPayment, Connection con) {
        boolean result = false;
        Connection mastercon = MyConnection.getMasterConnection();

        try {
//            String selectStatement = "SELECT SUM(AMOUNT) AS TOTALAMOUNT, SUM(PAID) AS TOTALPAID,OUTSTANDING FROM PAYMENT WHERE SUBID=? AND OFFICEID=? GROUP BY SUBID";
            String selectStatement = "SELECT PAID AS TOTALPAID,OUTSTANDING FROM PAYMENT WHERE SUBID=? AND OFFICEID=? AND ID = (SELECT MAX(ID) FROM PAYMENT WHERE SUBID=? AND OFFICEID=?) GROUP BY SUBID";
            PreparedStatement stmt = con.prepareStatement(selectStatement);
            stmt.setInt(1, argPayment.getSubid());
            stmt.setInt(2, argPayment.getOfficeID());
            stmt.setInt(3, argPayment.getSubid());
            stmt.setInt(4, argPayment.getOfficeID());
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                argPayment.setSumPaid(rs.getFloat("TOTALPAID"));
                argPayment.setOutstanding(rs.getFloat("OUTSTANDING"));
//                argPayment.setSumAmount(rs.getFloat("TOTALAMOUNT"));
            }

            String insertStatement = "INSERT INTO PAYMENT(SUBID,AMOUNT,PAID,OUTSTANDING,ENTRYDATE,REMARK,COURIERCHARGE,LESSPAYMENT,OFFICEID) VALUES (?,?,?,?,?,?,?,?,?)";
            PreparedStatement stat = con.prepareStatement(insertStatement);
            stat.setInt(1, argPayment.getSubid());
            stat.setFloat(2, argPayment.getAmount());
            stat.setFloat(3, argPayment.getAmount() + argPayment.getSumPaid());
            stat.setFloat(4, argPayment.getOutstanding() - argPayment.getAmount());

            stat.setDate(5, argPayment.getEntryDate());
            stat.setString(6, argPayment.getRemark());
            stat.setFloat(7, argPayment.getCourierCharge());
            stat.setFloat(8, argPayment.getLessPayment());
            stat.setInt(9, argPayment.getOfficeID());

            con.setAutoCommit(false);
            if (stat.executeUpdate() > 0) {
                int subid = argPayment.getSubid();
                Purchase purchase = DBPurchase.retrive(subid, argPayment.getOfficeID(), con);
                int partyid = purchase.getParty().getPartyID();
                Account partyAccount = DBAccount.retrieveAccountByIDandType(partyid, "PARTY", mastercon);
                Account officeAccount = DBAccount.retrieveAccountByIDandType(argPayment.getOfficeID(), "OFFICE", mastercon);
                AccountTrack partyAccountTrack = new AccountTrack();
                partyAccountTrack.setId(partyAccount.getId());
                partyAccountTrack.setCredit(true);
                partyAccountTrack.setDate(argPayment.getEntryDate());
                partyAccountTrack.setAmount(argPayment.getAmount());
                partyAccountTrack.setDescription("Payment for payment id-" + argPayment.getId());
                if (DBAccountTrack.insert(partyAccountTrack)) {
                    AccountTrack officeAccountTrack = new AccountTrack();
                    officeAccountTrack.setId(officeAccount.getId());
                    officeAccountTrack.setCredit(false);
                    officeAccountTrack.setDate(argPayment.getEntryDate());
                    officeAccountTrack.setAmount(argPayment.getAmount());
                    officeAccountTrack.setDescription("Payment for payment id-" + argPayment.getId());

                    if (DBAccountTrack.insert(officeAccountTrack)) {
                        AccountTrack pAccountTrack = new AccountTrack();
                        pAccountTrack.setId(partyAccount.getId());
                        pAccountTrack.setCredit(false);
                        pAccountTrack.setDate(argPayment.getEntryDate());
                        float amount = argPayment.getLessPayment() + argPayment.getCourierCharge();
                        pAccountTrack.setAmount(amount);
                        pAccountTrack.setDescription("Less Payment and Courier Charge-" + argPayment.getId());

                        if (DBAccountTrack.insert(pAccountTrack)) {
                            AccountTrack oAccountTrack = new AccountTrack();
                            oAccountTrack.setId(officeAccount.getId());
                            oAccountTrack.setCredit(true);
                            oAccountTrack.setDate(argPayment.getEntryDate());
                            float officeAmount = argPayment.getLessPayment() + argPayment.getCourierCharge();
                            oAccountTrack.setAmount(amount);
                            oAccountTrack.setDescription("Less Payment and Courier Charge-" + argPayment.getId());

                            result = DBAccountTrack.insert(oAccountTrack);
                            con.commit();                            
                        }

                    }
                }
            }

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);
        }finally{
            try{
                con.setAutoCommit(true);
                mastercon.close();
            }catch(Exception e){
            }
        }
        return result;
    }

[stephen84s]

@bugmemnot,
I am pretty sure the thread starter has already solved his problem by now.