Board index » delphi » Local IB & Transactions

Local IB & Transactions

Using Delphi1 & Local IB & request live TQuerys (no ttables)
Can I use data aware components and still use transactions.
e.g. When I use a dbgrid and I add a row.... it is auto posted. Too late
for roll back.
Jeez... I dont understand this well enough to post a question..

Here's what I want to do. Make changes/additions in a One to many
configuration and commit the changes when the operator says "finish" . The
fields are spread over several forms but all the Queries are on a common
form (Globals) and are still open to the records Ive changed/added (except
those in the grids). The documentation keeps teasing me with things like
..USE TRANSACTIONS FOR MULTI RECORD UPDATES.. and the tantalizing example
of transaction usage.

Database.starttransaction;
{do some updates}
Database.Commit;

Duh!! Wow!! I'da never thunk a dat!!

 

Re:Local IB & Transactions


Quote
jami...@aol.com wrote:
> Using Delphi1 & Local IB & request live TQuerys (no ttables)
> Can I use data aware components and still use transactions.
> e.g. When I use a dbgrid and I add a row.... it is auto posted. Too late
> for roll back.
> Jeez... I dont understand this well enough to post a question..

> Here's what I want to do. Make changes/additions in a One to many
> configuration and commit the changes when the operator says "finish" . The
> fields are spread over several forms but all the Queries are on a common
> form (Globals) and are still open to the records Ive changed/added (except
> those in the grids). The documentation keeps teasing me with things like
> ..USE TRANSACTIONS FOR MULTI RECORD UPDATES.. and the tantalizing example
> of transaction usage.

> Database.starttransaction;
> {do some updates}
> Database.Commit;

> Duh!! Wow!! I'da never thunk a dat!!

That's right: it's as simple as that :-))

Of course, you have to use a TDatabase component and link all your
TQueries to it. Then, before you allow the user to change anything,
you use that first statement: Database1.StartTransaction;
From now on, they can post, delete or insert whatever they want. If
you have dropped two buttons, typically on some modal form asking them
if they want to commit or discard the changes, in the click handler
for the first, you code Database1.Commit; and for the second
Database1.Rollback;

There's just one drawback: you cann't call StartTransaction twice, so
you have to anticipate the user's skills to avoid clicking one of
those two buttons... So now, your first statement should read:
        with Database1 do
                if not InTransaction then StartTransaction;
etc...

Uhm, I'm not sure to understand myself either, so feel free to ask if
it's that bad an explanation...

Have fun otherwise!

Jasper

Re:Local IB & Transactions


Hi, Jasper, Jamie, and Steve..

I've got a *nagging* question about IB and transactions, as well.

In comp.lang.pascal.delphi.databases, Jasper Stil wrote:

Quote

> jami...@aol.com wrote:
> > Database.starttransaction;
> > {do some updates}
> > Database.Commit;

> > Duh!! Wow!! I'da never thunk a dat!!

> That's right: it's as simple as that :-))

I know u were only half-jesting, Jasper, :) but still I have to agree
with Jamie, here.. We were trying to find out the simple answer of
whether one has to use .Post before the .Commit or not. Borland's help
(both online and in manuals) on this was woefully lacking. (We are using
C-S IB).

Quote
> Of course, you have to use a TDatabase component and link all your
> TQueries to it. Then, before you allow the user to change
> anything, you use that first statement: > Database1.StartTransaction;
> From now on, they can post, delete or insert whatever they want...

To add to the confusion, we found that one DOES have to use .Edit
before making any transactions.  So we thought that we would use
a .Post as well. But when we used .Post, we got an exception:

EDBError Exception: Key violation. Violation of PRIMARY or UNIQUE KEY
constraint "INTEG_5" on table "INV".

But if we wrapped the .Edit and .Post with a StartTransaction and a
Commit, and *also* commented out the .Post, it was able to make the
change.

We are simply trying to change a field in a table. It is not even
the primary field. It is indexed, since we want to search on it, but
it is not the primary key.

In other places we are able to .Edit and .Post without needing to use
transactions, and we get no error.  We did look into the INTEG_5
constraint on the IB server, and apparently that is a constraint set
up by IB for the PRIMARY field (Inventory #), not for the field we are
changing (Location).

Why would it feel that a PRIMARY field's uniqueness has been violated
when all we are changing is another field?

And why do .Edit and .Post work for us on other fields and tables
(without having to wrap with a transaction)?  In fact, if we make a
DBEdit for the field in question, we find we can change the field
easily and with no problems. (But we want it to be via a menu choice
with an explicit call, so that we can monitor changes to this
particular field: a location change on a particular piece of
inventory, etc.).

Does either of you (or anyone else) know the answer to this?  If
anyone's got any clues on this, we'd be eternally grateful, because
we're really knocking our heads on the wall trying to figure it out..

Thanks for any light that any of you may be able to shed..

Jeff Stern
Social Sciences Computing Services
University of California, Irvine
Irvine, CA 92697-0001 USA
jast...@uci.edu

Re:Local IB & Transactions


Sorry.. Netscape config probs.. Please reply to jast...@uci.edu.
Thanks.

Jeff Stern
Social Sciences Computing Services
University of California, Irvine
Irvine, CA 92697-0001 USA
jast...@uci.edu

Re:Local IB & Transactions


Quote
geradl <gwlan...@uci.edu> wrote:
> I've got a *nagging* question about IB and transactions, as well.
> I know u were only half-jesting, Jasper, :) but still I have to agree
> with Jamie, here.. We were trying to find out the simple answer of
> whether one has to use .Post before the .Commit or not. Borland's help
> (both online and in manuals) on this was woefully lacking. (We are using
> C-S IB).

We all have to learn that what we feel obvious, isn't always for
others. I'm trying hard a very long time now, but it still hurts when
discovering I didn't succeed by now in learning something easy but
important like that, while I have good knowledge of some (very)
complicated but far less important things. :-(( It hits me hard again
here: having had Oracle training before seeing Delphi (it didn't exist
in 1994! Not for sale, that is), I am so conditioned to send a
"commit" after some dml that it was really obvious to me, not
realizing that it could be different for others. :-))

Quote
> To add to the confusion, we found that one DOES have to use .Edit
> before making any transactions.  So we thought that we would use
> a .Post as well. But when we used .Post, we got an exception:

> EDBError Exception: Key violation. Violation of PRIMARY or UNIQUE KEY
> constraint "INTEG_5" on table "INV".

> But if we wrapped the .Edit and .Post with a StartTransaction and a
> Commit, and *also* commented out the .Post, it was able to make the
> change.

You mean: starting isql after that, you actually saw the changes
committed to the database?

Quote
> We are simply trying to change a field in a table. It is not even
> the primary field. It is indexed, since we want to search on it, but
> it is not the primary key.

> In other places we are able to .Edit and .Post without needing to use
> transactions, and we get no error.  We did look into the INTEG_5
> constraint on the IB server, and apparently that is a constraint set
> up by IB for the PRIMARY field (Inventory #), not for the field we are
> changing (Location).

> Why would it feel that a PRIMARY field's uniqueness has been violated
> when all we are changing is another field?

> And why do .Edit and .Post work for us on other fields and tables
> (without having to wrap with a transaction)?  In fact, if we make a
> DBEdit for the field in question, we find we can change the field
> easily and with no problems. (But we want it to be via a menu choice
> with an explicit call, so that we can monitor changes to this
> particular field: a location change on a particular piece of
> inventory, etc.).

> Does either of you (or anyone else) know the answer to this?  If
> anyone's got any clues on this, we'd be eternally grateful, because
> we're really knocking our heads on the wall trying to figure it out..

So many things can go wrong and will, according to Murphy's law, that
it would be only wild-guessing in the dark here without some suitable
portion of your source-code, preferably with some sample data. Causes
go from simple, possibly offending suggestions to the undiscoverable,
like stored procs calling each other on different tables in the
database, firing "en passant" some trigger you weren't even aware of,
etc... I actually built some trigger in the past, calling a stored
proc to update another record in some other table. The trigger was on
insert and used a generator, the stored proc got the last value from
that generator to use as referential integrity key. I added some code,
more code, a lot of code and finally, said to myself: "hey, if I put a
simple if right here (somewhere half way), I can use the same for the
insert"... Forgot about the generator and BANG, {*word*76}y wall, let me
go! Took me days to find it out. Same message as you get now. I don't
say it is what is happening to you, but it's just an illustration of
those wild guesses.

There's an essential difference between the "value" (which you
probably set from that edit-box) and the "text" property, (which is
set when typing in a DBEdit-box)...

You could have referenced by number instead of ByName, and picked the
wrong index (I know, highly improbable, but believe me, I really did
all those things at least once...)

Etc..., etc...

Jasper

Other Threads