Hi Tim & Team,

I've got a few users having trouble with transactions being saved even
though the rollback command is being called.

Can I ask is there any situation where a rollback will still allow data
to be written to the database even though a StartTransaction(0) is being
used? For example there are some nested statements in my code that
rollback() in the transaction could this somehow cause a problem.

I added a flag so I could see if rollback is being called and it is, but
the transaction still gets written.

I would post code but the code is quite long. Just thought you might
have a theory or experienced what I'm doing wrong.

Thanks,

Phil.

Phil

<<
I've got a few users having trouble with transactions being saved even
though the rollback command is being called.
>>
It is impossible.

> Can I ask is there any situation where a rollback will still allow data to
> be written to the database even though a StartTransaction(0) is being
> used? For example there are some nested statements in my code that
> rollback() in the transaction could this somehow cause a problem.
I am thinking your data is stored in just one database. But if not, you must
check in which database you are starting the transaction.

> I would post code but the code is quite long. Just thought you might have
> a theory or experienced what I'm doing wrong.
Yes, this is a good idea and help us to help you.

Eduardo

Eduardo Wrote:

<<
It is impossible.
>>

Yes it sounds really strange, and it only happens when I choose NO on a
message dialog which then in turn calls a commit or rollback. (there are
3 of these message dialogs within the transaction)

The only thing to note is that one procedure called from within
transaction resides in a datamodule and it inserts a record using a
QUERY. Now on 2 of the 3 message dialogs the record is NOmessage dialog
I press NO, rollback is called and yet the record is written to the
database.

I'll take another look through the code, but I placed a ShowMessage
before each commit and rollback and indeed the rollback was called and
yet certain records where still written to the database.

"Phil Read" <phil@vizualweb.com> wrote in message
news:BFAA20E5-3346-4EFE-A596-BCF2D5453B79@news.elevatesoft.com...
>
> Yes it sounds really strange, and it only happens when I choose NO on a
> message dialog which then in turn calls a commit or rollback. (there are 3
> of these message dialogs within the transaction)
>
> The only thing to note is that one procedure called from within
> transaction resides in a datamodule and it inserts a record using a QUERY.
> Now on 2 of the 3 message dialogs the record is NOmessage dialog I press
> NO, rollback is called and yet the record is written to the database.
>
> I'll take another look through the code, but I placed a ShowMessage before
> each commit and rollback and indeed the rollback was called and yet
> certain records where still written to the database.
>
If those records are always writen then........they are not within a
transaction

"Phil Read" <phil@vizualweb.com> wrote in message
news:BFAA20E5-3346-4EFE-A596-BCF2D5453B79@news.elevatesoft.com...
> Eduardo Wrote:
>
> <<
> It is impossible.
>>>
>
> Yes it sounds really strange, and it only happens when I choose NO on a
> message dialog which then in turn calls a commit or rollback. (there are 3
> of these message dialogs within the transaction)

If you can make it happen, then you  should be able to trace it. My bet is
that you are getting an exception somewhere, and your rollback code is not
getting executed.

Robert

>
> The only thing to note is that one procedure called from within
> transaction resides in a datamodule and it inserts a record using a QUERY.
> Now on 2 of the 3 message dialogs the record is NOmessage dialog I press
> NO, rollback is called and yet the record is written to the database.
>
> I'll take another look through the code, but I placed a ShowMessage before
> each commit and rollback and indeed the rollback was called and yet
> certain records where still written to the database.
>

<< If you can make it happen, then you  should be able to trace it. My
bet is
that you are getting an exception somewhere, and your rollback code is not
getting executed.

Robert >>

OK I've traced it down to the following code:

// Clear the forecast table
ForecastQuery->SQL->Clear();
ForecastQuery->SQL->Add("EMPTY TABLE forecast;");

ForecastQuery->SQL->Add("CREATE TABLE IF NOT EXISTS
\"Memory\\forecastdates\"");
ForecastQuery->SQL->Add("(");
ForecastQuery->SQL->Add("forecastdate DATE,");
ForecastQuery->SQL->Add("PRIMARY KEY (forecastdate,RecordID) COMPRESS
FULL");
ForecastQuery->SQL->Add("LOCALE CODE 0");
ForecastQuery->SQL->Add("USER MAJOR VERSION 1");
ForecastQuery->SQL->Add(");");

ForecastQuery->SQL->Add("CREATE INDEX IF NOT EXISTS GroupBy ON
\"Memory\\forecastdates\" (forecastdate) COMPRESS FULL;");

ForecastQuery->SQL->Add("EMPTY TABLE \"Memory\\forecastdates\"");
ForecastQuery->ExecSQL();

Now I know things like CREATE are not within the scope of a DBISAM
transaction but I'm not bothered about this memory table it's just used
to output some data and is DELETED afterwards anyhow. But it appears
this process somehow COMMITS the transaction without commit ever being
called.

What's the best way around this?

THanks guys!

Phil,

<< But it appears this process somehow COMMITS the transaction without
commit ever being called. >>

http://www.elevatesoft.com/manual?action=mantopic&id=dbisam4&product=d&version=7&category=1&topic=12

Incompatible Operations

The following operations are not compatible with transactions and will cause
a transaction to commit if encountered during a transaction.

..   Backing Up and Restoring Databases

..   Verifying and Repairing Tables

..   Creating and Altering Tables

..   Adding and Deleting Indexes from a Table

..   Optimizing Tables

..   Upgrading Tables

..   Deleting Tables

..   Renaming Tables

..   Emptying Tables

..   Copying Tables

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Thanks for clearing that up, that code got added later on during a mod a
few months back, so we didn't put 2 and 3 together at the time.

THanks to all for your input

Cheers!

Still curious as to why the rollback did not raise an exception.

Robert

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:79AAC60E-784E-4A57-920A-DC47C9ECFC73@news.elevatesoft.com...
> Phil,
>
> << But it appears this process somehow COMMITS the transaction without
> commit ever being called. >>
>
> http://www.elevatesoft.com/manual?action=mantopic&id=dbisam4&product=d&version=7&category=1&topic=12
>
> Incompatible Operations
>
> The following operations are not compatible with transactions and will
> cause a transaction to commit if encountered during a transaction.
>
> .   Backing Up and Restoring Databases
>
> .   Verifying and Repairing Tables
>
> .   Creating and Altering Tables
>
> .   Adding and Deleting Indexes from a Table
>
> .   Optimizing Tables
>
> .   Upgrading Tables
>
> .   Deleting Tables
>
> .   Renaming Tables
>
> .   Emptying Tables
>
> .   Copying Tables
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Robert,

<< Still curious as to why the rollback did not raise an exception. >>

Yes, it certainly should have raised an exception.  However, without seeing
the entire set of code and running it myself, I really can't say why it
didn't.

--
Tim Young
Elevate Software
www.elevatesoft.com