Multiple queries with Firebird and PHP 7.3

Question

It's my first time working with Firebird database and I need some help in the transactions department. I need to run multiple functions inside a ''main function'', those functions have queries, but if one fails, I need them all to rollback. Can I open the transaction in the top of the main function and close it at the bottom or do I have to open and close for each query? I'll post an example.

public function Main_function()
{
    $id = $this->create_user_id();

    $connection = ibase_connect($this->_db, $this->_username, $this->_password, '100');
    $trans = ibase_trans($connection, IBASE_READ+IBASE_COMMITTED+IBASE_REC_NO_VERSION);

    $query = "INSERT INTO USERS (user_id, name) VALUES ('john', '$id')";

    $newuser = ibase_query($trans, $query);

    $return = $this->insert_new_job($id);

    ibase_commit($trans);
    ibase_close($connection);
}

public function create_user_id()
{
    $id = '2';
    return $id;
}

public function insert_new_job($id)
{
    ///DO I NEED TO OPEN A NEW TRANSACTION OR THE OTHER IS STILL ACTIVE?
    $query = "INSER INTO jobs (name, id, job) VALUES ('john',$id,'developer') ";
    $result = ibase_query($trans, $query);
    return $result;
}

Because you call `insert_new_job()` inside the previously started transaction (before you call commit) it all happens within the one transaction — RiggsFolly, Aug 26 '21 at 16:43
Assuming you use Firebird 2.5 or newer you can use TraceAPI (i would suggest http://fbprofiler.sf.net) to map queries to transaction handles, this way you would see if your queries come in the same or different TXs. Also, you can check Statesments `Monitoring Table` if logging in with the same user name (or SYSDBA) https://github.com/FirebirdSQL/firebird/blob/master/doc/README.monitoring_tables — Arioch 'The, Aug 26 '21 at 17:00

score 0 · Answer 1 · answered Aug 26 '21 at 18:06

If you want all statements executed in a single transaction, then you need to start the transaction once, use it for all statements you want in that transaction, and then commit at the end of the unit work. In other words: you should not start a transaction for each individual statement.

Starting a transaction for each statement would not have your desired result, as each would be executed in its own transaction, which is - by your own words - not what you want. In addition, as Firebird supports multiple active transactions on a single connection, you would have the added problem that you need to ensure that you keep track of all transactions handles to properly commit or rollback, as starting a new transaction will not automatically end a previous transaction.

As an aside, I don't program in PHP, but it looks like $connection and $trans (and other variables) are global variables, and it would be better to declare them as local variables and pass them to your query methods instead of relying on access to global variables.

Multiple queries with Firebird and PHP 7.3

1 Answers1