Hello, Aleksey

It's very interesting to observe how one can present a bad situation as a good one. Well... I'll keep this thread till someone will do something about it.

We have two cases: SQLSETPROP(connection,"Transactions",1) and SQLSETPROP(connection,"Transactions",2). Let's have a closer look at both of them:

To protect the server, I use a shared connection (I always did that). So:

Case 1: That's my way to do a multitable update:

lnResult = SQLEXEC(connection,"BEGIN TRANSACTION")
IF lnResult = -1
* Error checking here and return.
ENDIF

* At this moment I have one open transaction on the backend. Looks ok to me.

llSuccess = TABLEUPDATE(.T.,.F.,"cursoradapter1") && as soon as this executes, transaction is gone.
IF NOT llSuccess
*Error checking here and return
ENDIF

llSucces = TABLEUPDATE(.T.,.F.,"cursoradapter2") && this one is not in a transaction anymore.

Then I thought: maybe the cursoradapter wants to enclose it's own update in a transaction, but it can't start it, because of the SQLSETPROP() setting. Let's make it have the transaction begun, then. So I modified base class, adding a SQLEXEC("BEGIN TRANSACTION") in BeforeCursorUpdate() event, hoping the multitable update structure will look like this:

BEGIN TRASACTION (1) - started programatically

BEGIN TRASACTION (2) - in BeforeCursorUpdate()
update
COMMIT TRAN && made automatically by the CA - can't disable that

BEGIN TRANSACTION (2) - in BeforeCursorUpdate()
update
COMMIT TRAN && made automatically by the CA - can't disable that

COMMIT TRANSACTION (1) - executed programatically.

In this case, I would be somehow happy. No luck. In this case, the code works like this:

lnResult = SQLEXEC(connection,"BEGIN TRANSACTION")
IF lnResult = -1
* Error checking here and return.
ENDIF

* At this moment I have one open transaction on the backend. Looks ok to me.

llSuccess = TABLEUPDATE(.T.,.F.,"cursoradapter1")
* Stepping on through the code, BeforeCursorUpdate fires, then it executes the BEGIN TRANSACTION, and I have 2 open transactions. This looks ok too. After the update, trancount is 1, and it behaves at expected.

IF NOT llSuccess
*Error checking here and return
ENDIF

* Not to the second CA:

llSucces = TABLEUPDATE(.T.,.F.,"cursoradapter2")
* Stepping through the code, I see BEGIN TRANSACTION executed, switched to Enterprise Manager, see 2 open transactions, then follows the update. After the update BOTH TRANSACTIONS ARE GONE!!!!!

Heh? Can you tell me again to study the examples, PLEASE?


Case 2 - manual transactions.

One of the windows' standards are MDI forms. My application is compliant with that. So, I can open two or more forms at once.

Connection is shared, and first CA opens one transaction. In the form's Unload I have to SQLCOMMIT() or SQLROLLBACK() it, right?

Go figure what happens with the second form, if the first one closes the transaction.

Now I'm angry. Please prove me I am wrong, I miss all the problem here and I'm completely out of the programming stuff. I will thank you if you'll be able to do that.

I'll keep this thread going till this CA will be patched or my UT membership will be cancelled. I have my deadlines and I thought this CA will save my a##, because I have to make the app to work with MSSQL and MySQL in the same time. Without transaction there is no app. Without the app, there is no salary.

And you tell me to study the examples? where in the examples is this problem?












>Hi Andy,
>
>Logically it is correct. All data reads/writes will be included into the same transaction until SQLCOMMIT()/SQLROLLBACK(). Even if backend doesn't start transaction physically, subsequent SQLCOMMIT()/SQLROLLBACK() do not fail. It is only a plus if back end is able to avoid unnecessary physical transactions.
>
>Thanks,
>Aleksey.