Devart Forums

Posted: **Mon 27 Mar 2017 12:24**

Hi SDAC Support Team,

i've added two components to a form: TMSConnection and TMSScript
The AutoCommit property of the TMSConnection component is set to false.
In design mode i try to execute the following two statements in the TMSScript Editor:

Code: Select all

UPDATE AdventureWorks2014.HumanResources.Employee
SET
  VacationHours = 98
WHERE
  BusinessEntityID = 1;
COMMIT;

The following error occurred after the execution of the two statements :

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION

That is an unexpected behavior of the TMSConnection component for me, because i've set the AutoCommit property to false and the update statement starts the transaction implicit,
During runtime i set a breakpoint at the AfterExecute-TMSScript event and check the transaction state:
After the above UPDATE Statement the session wasn't in transaction !! ( MSConnection1->InTransaction ) What should i do, if want to execute SQL scripts with COMMIT or ROLLBACK statements ?
In this case MSConnection1->StartTransaction doesn't work.

Best regards

C++Builder 10.1 Update 1
SDAC 7.3.16 Professional Edition / Direct Connection Mode

Posted: **Tue 04 Apr 2017 13:15**

To use COMMIT/ROLLBACK in SQL script separate them with a keyword GO . For example:

Code: Select all

UPDATE AdventureWorks2014.HumanResources.Employee
SET
  VacationHours = 98
WHERE
  BusinessEntityID = 1;
GO 
COMMIT;

Code: Select all

  MSConnection->AutoCommit = False;
  MSConnection->Connect();
  MSConnection->StartTransaction();
  MSScript->Execute();

Posted: **Thu 13 Apr 2017 09:43**

Hi Devart Team,

thank you for your answer.
When i follow your advice in my application, at first it seems to work, but at the moment i finish the application, i got the following error message :

Code: Select all

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Followed by

Code: Select all

One of the connections in the transaction is not acitive

At the debug session i can't see in the AfterExecute event any "GO" sql statement !!
First SQL :

Code: Select all

UPDATE AdventureWorks2014.HumanResources.Employee
SET
  VacationHours = 98
WHERE
  BusinessEntityID = 1;

Second SQL :

Code: Select all

COMMIT;

Where is the Go-Statement ?
So i ommit the GO Statement in the SQL and i have the same behavior. (?)

I think the problem is, that you don't recognize in a session/ connection, that there is an open transaction
and your "solution" is to start the transaction before the statement is executed.

At this point you can't know what kind of SQL statements will come.
So your advice is is not expedient to solve my problem.

I've been working for many years with ODAC and i 'm very content with this components, but SDAC doesn't work in the same way.

Best regards

Posted: **Thu 20 Apr 2017 12:47**

Execution of T-SQL commands COMMIT/ROLLBACK changes the transaction status on the server side, but does not change the state of the transaction instance used in TMSConnection. So if you execute COMMIT/ROLLBACK in TMSScript, there is no need to call the methods TMSConnection->Commit() or TMSConnection->Rollback() again.

In SQL query the GO keyword for TMSScript is a separator of T-SQL commands, and each of such commands will be performed to SQL Server in a separate block. TMSScript does not perform the GO command itself to the server.

Devart Forums

Unexptected behavior of AutoCommit property

Unexptected behavior of AutoCommit property

Re: Unexptected behavior of AutoCommit property

Re: Unexptected behavior of AutoCommit property

Re: Unexptected behavior of AutoCommit property