XData and Firebird Transactions

BIZ TMS XData
1

Hi,
I have created a XData Server using Unidac->Firebird.

When I request entities from the server using TXDataClient, Unidac creates a Transaction(Firebird needs a active transaction to read data from DB). after sql has been executed on the DB server and xdata has delivered the entities to the client, the transaction is left active.

Is there a way to hande transactions without using service operations? how to commit the transaction on the DB server after client request and close the TXDataClient connection?

Thanks in advance,

Omar Zelaya

2

When processing requests of CRUD Endpoints, XData opens a transaction and then commits it at the end of the request processing.
Maybe there is some setting in your TUniConnection that is causing it?

3

Hi,

UniDac auto start a transaction when ejecuting sql statements under firebird. I have changed to FireDac and set AutoStart Transaction to false. If Client execute a Service method it works ok(The method start and commit a transaction). But now when the cliente executes the following code:
xDataClient.List<TA_CATEGORIA>('$filter=' + TSparkleUtils.PercentEncode('(ESTATUS eq 1)') +
'&$orderby=' + TSparkleUtils.PercentEncode('ID'));
I get a FireDac exception at the server server "Invalid transaction handle(expecting explicit transaction start)", It look like xdata is not starting a trasnsaction before executing SQL on DB server.

Thank in advance,

Omar Zelaya

4

Hi,

On the Service method I manually start and commit the transaction.

Thanks in advance,

Omar Zelaya

5

In our tests we set AutoCommit to False and EnableMemos to True for Interbase/Firebird, and it works fine - no other changes to default settings.

6

Hi,

Did you test setting AutoStart to False?

Thanks in advance,

Omar Zelaya

7

I didn't touch AutoStart, I just use the default settings, which I don't know what is.

8

Hi,

With AutoStart = True, FireDac will auto start a transaction(Default for Firebird,If there is no active transction) before excecuting SQL statements. If I set AutoStart = False, executing xdata queries return the exception that no transaction is active. If I set to True it works ok. Now Setting AutoStart = true and AutoComit = true executing XData queries will work ok and will not leave orphan transaction, but it looks like this is done by FireDac not XData.

I mention this because On version 2 improvments I found this "Improved : All server-side operation (entity CRUD, service operation execution) are now performed in database transactions".

My problem with this last aproach is with service operations that requires that multiple SQL estatements(updates to DB) need to be done within a single start and commit or rollback transaction.

With last aproach(AutoStart and AutoComit = true) every sql(update to DB) executed is commited after execution and I cannot rollback all changes if needed, leaving the DB in a inconsistent state.

Thanks in advance,
Omar Zelaya

9

Then explicitly start a transaction in your service operation, if you do, AutoCommit won't apply and the transaction will only be committed when you explicitly call Commit.

10

Hi,

I tought I already tried that before with bad results.

Thanks,

Omar Zelaya

11

Hi,

I see that I have tried that aproach before with Unidac. Using Unidac with autocommit does commit retaining on every sql statement so IDTransaction.RollBack has no effect to revert changes. FireDac works Ok.

Thanks

Omar Zelaya

12

I have the same problem using ZEOS. XData explicitly starts/commit a transaction only when updating data (C_UD) but NOT when reading (GET).

13

Hi,

I would recommend you to check how your data access components works in your particular case. In my case was a problem with UNIDAC on how it handles autocommit setting true, so I have moved to FireDac.

Omar Zelaya

2 Likes