Insert and Select in one Transaction: ADO - MS SQL

[LOTAS]

Hi all,

I have a real big problem.

We use ZEOS (6.6.1 - beta) to connect via ADO with a MS SQL Server 2005. (coding with Borland C++ Builder 6).

Sometimes it is needed to work with transactions, to have the possibility to rollback when something is wrong with the recently inserted data.

For this, with BDE we have following concept:

1) Start Transaction
2) Insert into Table
3) Select from table to check if everything is OK
4a) if everything is OK -> Commit
4b) if something went wrong -> Rollback

When I try to do this with the ZEOS Components, I always get following error :
"An EOleException occured. Message: 'Multiple Recordsets are not possible with a transaction using this cursor type. Change cursor type, do a commit or close one of the recordsets'"

Does anyone knows why this happens, where I can change the cursor type or what to do?

I tried to work with direct SQLs but this also isn't working.

Codes
1) With StartTransaction

Code: Select all

ZEOS->StartTransaction();
QyZEOS->Close();
QyZEOS->SQL->Text = "INSERT INTO table (foo1, foo2) VALUES ('bar1', 'bar2')";
QyZEOS->ExecSQL();
QyZEOS->Close();
QyZEOS->SQL->Text = "SELECT * FROM table1";
DsResult->DataSet = QyZEOS;
QyZEOS->Open();
ZEOS->Rollback();

2) With direct SQLs

Code: Select all

ZEOS->AutoCommit = false;
int res = ZEOS->DbcConnection->CreateStatement()->ExecuteUpdate("BEGIN TRAN");
QyZEOS->Close();
QyZEOS->SQL->Text = "INSERT INTO table (foo1, foo2) VALUES ('bar1', 'bar2')";
QyZEOS->ExecSQL();
QyZEOS->Close();
QyZEOS->SQL->Text = "SELECT * FROM table1";
DsResult->DataSet = QyZEOS;
QyZEOS->Open();
res = ZEOS->DbcConnection->CreateStatement()->ExecuteUpdate("ROLLBACK");

I Hope someone can help me

Johannes

[LOTAS]

Really noone who can help?

[seawolf]

Just 2 question:

1) With StartTransaction
you need Zeos->autocommit = false; before ZEOS->StartTransaction();
Have you missed to paste it?

2) Do in both cases the same error is raised?

[LOTAS]

Just 2 question:

1) With StartTransaction
you need Zeos->autocommit = false; before ZEOS->StartTransaction();
Have you missed to paste it?

It's not posted as it's set in the component itself through the objectinspector. I only set it manually for the second test.

2) Do in both cases the same error is raised?

Yes, it's always the same error.

[designshouse]

try to use QyZEOS->SQL->Text->Clear before SELECT query

Code: Select all

QyZEOS->ExecSQL();
QyZEOS->Close();
QyZEOS->SQL->Text->Clear;
QyZEOS->SQL->Text = "SELECT * FROM table1";