Laravel DB:transaction with Firebird connection

Question

I have some trouble with the Laravel transaction.

• Laravel 9+
• PHP 8+
• Firebird 2.5

I have two DB connection MySQL (default) and Firebird. MySQL works fine, like this. I get the connection data.

DB::transaction(function ($conn) use ($request) { 
 dd($conn)
});

When I try to use with my other connection ('firebird'), it always throws "There is already an active transaction" error.

DB::connection('firebird')->transaction(function ($conn) use ($request) {
 dd($conn);
 $conn->table('A')->insert();
 $conn->table('B')->insert();
 $conn->table('C')->insert();
});

I tried this version too, but I get the same error if I use the 'firebird' connection:

DB::connection('firebird')->beginTransaction();

If I leave out the transaction, both are working just fine, but I want to use rollback if there is any error. Any thoughts why? I'm stuck at this.

Answer 1

The solution:
Need to turn off auto commit(PDO::ATTR_AUTOCOMMIT), when you define the 'Firebird' connection in 'config/database.php' Example:

'firebird' => [
        'driver'   => 'firebird',
        'host'     => env('DB_FIREBIRD_HOST', '127.0.0.1'),
        'port'     => env('DB_FIREBIRD_PORT', '3050'),
        'database' => env('DB_FIREBIRD_DATABASE', 
          'path\to\db\DEFAULT.DATABASE'),
        'username' => env('DB_FIREBIRD_USERNAME', 'username'),
        'password' => env('DB_FIREBIRD_PASSWORD', 'password'),
        'charset'  => env('DB_FIREBIRD_CHARSET', 'UTF8'),
        'options' => array(
            PDO::ATTR_PERSISTENT => false,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_AUTOCOMMIT => false,
        )
    ],

Then you can use Laravel transactions like:

try{
    DB::connection('firebird')->beginTransaction();
    DB::connection('firebird')->insert();

    DB::connection('firebird')->commit();
} catch (Exception $exception) {
    DB::connection('firebird')->rollBack();
    throw $exception;
}

Or you can use this too and this do the commit or rollback automatic:

DB::connection('firebird')->transaction(function () use 
  ($request) {
        DB::connection('firebird')->insert($request);
})

But dont forget! If you do this, you must start the transaction every time! Even when you are just Select some data.

DB::connection('firebird')->beginTransaction();

or you will get SQL error like:

SQLSTATE[HY000]: General error: -901 invalid transaction handle (expecting explicit transaction start)

Thank you everybody!