We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Execute procedure inside Mvc\Model isolated transaction

Hello!

Right now in my code i have a procedure that i need to execute followed by other ORM updates/inserts. To execute my procedure i use

    $result = $this->getDI()->get('db')->query(
            'SELECT * FROM procedure(:param)', 
            array(
                'param' => $param
            )
        );

I want to do something like

    $result = $this->getDI()
        ->get('db')
        ->setTransaction($transaction)
        ->query(
            'FOOBAR'
        );
    $model = new MyModel();
    $model->setTransaction($transaction);
    $model->save();

    if ($isOk) {
        $transaction->commit();
    }


58.6k
Accepted
answer

I haven't tried this, but I think you need your DI to use the transaction connection instead which would probably be tied to the transaction

    <?php
    use Phalcon\Mvc\Model\Transaction\Manager as TransactionManager;

    try {
        $transactionManager = new TransactionManager();
        $transaction = $transactionManager->get();

        // Try This
        $transaction->getConnection(); 
        // See if your needed calls are here: // https://docs.phalcon.io/en/latest/api/Phalcon_Db_AdapterInterface.html

        $model = new MyModel();
        $model->setTransaction($model);
        $model->save();

        $transaction->commit();
    }
    catch(Phalcon\Mvc\Model\Transaction\Failed $e){
        echo 'Failed, reason: ', $e->getMessage();
    }


11.2k

@Jesse Boyer answer helped me to find an way to do that

    $txManager = new TxManager();
    $transaction = $txManager->get();

    // I am using the isolated transaction separated connection. So, implictly, this query is being executed inside the transaction
    // Note: I am using "execute" because i am not expecting an answer from DB
    $transaction->getConnection()
        ->execute(
            'FOOBAR'
        );

    $model = new MyModel();
    $model->setTransaction($transaction);
    $model->save();

    if ($isOk) {
        $transaction->commit();
    }

Awesome !!!!