transaction commit

[christoforosKor]

Hello, I am trying to make insert and update to my database and if an error occurs to rollback the transaction.
The code I use is

Code: Select all

try {
            $db->transactionStart();
            $query->setQuery("update bst_elg_bst_stats st inner join bst_elg_bst_calcnewstats nst
                        on st.idPredictor = nst.idPredictor and st.idBetType = nst.idBetType and st.idMatchType = nst.idMatchType
                        set st.predictWin = st.predictWin + nst.predictWin,
                        st.predictSum = st.predictSum + nst.predictSum,
                        st.oddWin = st.oddWin + nst.oddWin,
                        st.oddSum = st.oddSum + nst.oddSum,
                        st.riskSum = st.riskSum + nst.riskSum"
                        );
            $db->execute();
            $query->setQuery("insert into bst_elg_bst_stats
                        (idPredictor, idBetType, idMatchType, predictWin, predictSum, oddWin, oddSum, riskSum)
                        select cp.idPredictor, cp.idBetType, m.idMatchType, count(*) as predictWin, sum(cs.predictSum), sum(cp.odd) as oddWin, sum(cs.oddSum),  sum(cs.maxOdd) 
                        from bst_elg_bst_calcpredictor cp
                        inner join bst_elg_bst_match m on cp.idMatch = m.idMatch 
                        inner join bst_elg_bst_calcsums cs on cp.idPredictor = cs.idPredictor and cp.idMatch = cs.idMatch and cp.idBetType = cs.idBetType
                        left join bst_elg_bst_stats bs on cs.idPredictor = bs.idPredictor and m.idMatchType = bs.idMatchType and cs.idBetType = bs.idBetType
                        where bs.idPredictor is null and bs.idBetType is null and bs.idMatchType is null and cp.idBetOptionStatus = 4
                        group by cp.idPredictor, cp.idBetType, m.idMatchType, cp.idBetOptionStatus");
            $db->execute();
            $db->transactionCommit();
        }
        catch (Exception $ex)
       {
            JLog::add($ex->getCode() . ' ' . $ex->File() . ' ' . $ex->getMessage(), JLog::ERROR);
            $db->transactionRollback();
        }
       
    }

When I run the same code without the $db->transactionStart() and $db->transactionCommit(), It works fine.
I have read the example https://docs.joomla.org/Using_transactions_in_Joomla and I can't see something wrong.

thanks for any help.

[gws]

I am no coder but is the table of the correct type to use transactions?

[christoforosKor]

I am no coder but is the table of the correct type to use transactions?

It;s an InnoDb table, the abstraction is standard joomla JTable class, I don't see why there is any problem with the transaction