Ok, let's pull up a chair and sit down and have a little discussion.

I think what the others were trying to do is to encourage you to consider
doing away with the cached updates layers your process uses.

I too might consider asking this of you as well since it would be more
sensible to just let the server take care of caching the updates inside of
its transaction system. That is the cleanest way to handle it IF you can.

What will make the most difference in this decision is if there is
dependency upon the end-user's actions in the mix. If you have a form
receiving data entry and a user can just come and go, you would rather not
have an active transaction open that is subject to a user walking away and
going home for the day, for example. Even a 10 minute coffee break could
cause a swell in overhead the server would have to deal with while it holds
onto that open transaction. So, while the server can manage this to some
extent, it does tax things enough to merit great care.

The unfortunate side of using cached updates is you are disconnected from
your server side validations until it is time to commit the entire batch of
edits from the user. So, then, you have to duplicate all of your business
rules on your client-side as the cached edits are being generated. This is a
big hassle and it puts you out of touch with the beauty of having a database
like Firebird where you can embed much of your business rules right in the
database.

What I eventually got around to doing is I would design my database in such
a way that all processes that involved business rules that needed to be
validated at the time an edit or insert was posted was to would generate the
data in tables that are separate from the permanent tables. That way, the
user would have all of their posts auto-commit to this "sandbox" and then
when they had done everything to complete their transaction then a stored
procedure would be called that would take everything in the "sandbox" and
process it and apply it in a permanent and binding way. If that process
completed then a commit would happen and everything would be complete and
final. If there was a problem then it would rollback and let the user go
back to right where they were before and hopefully address whatever the
problem was.

So, as you can see, I architected a special place for the updates to more or
less be cached in a "sandbox" area within my database and then I wrote
stored procedures to perform the actual operation to apply the updates.

This hybrid method also has some drawbacks too. The main thing for everyone
to do is to address the scope of complexity and user involvement and choose
the transaction model that works the best for the situation at hand.

Hope this helps.

Everyone, please feel free to chime in and share how you are handling
transactions in your application and a little bit as to why.

Thanks,
Jason


-----Original Message-----
From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com] On Behalf
Of masonwheeler
Sent: 04 October 2012 12:17 PM
To: IBObjects@yahoogroups.com
Subject: [IBO] Re: How do I post multiple datasets in a single transaction?

For heaven's sake, I know what transactions are! I've been using SQL
databases, both Firebird and SQL Server, for years, using a variety of
different frameworks. But I have *never* seen a framework that considers
all data live and has no concept of local data!

The way sane SQL data access works is like this: You query the database.
It pulls the data into local memory. THE TRANSACTION IS CLOSED AT THIS
POINT. All your data is now a local copy. You make your changes, then you
open a new transaction and save your changes back to the database.

(And before anyone says "but... but... concurrency!", consider this: if two
users are trying to edit the same row, the client is going to have to have
code to deal with it no matter what; just letting the DB raise an exception
and throw it in the user's face is unacceptable. So since you need an
explicit client-side locking mechanism of some sort for your data access to
work right anyway, there's no good reason to hold the connection open and
overcomplicated everything for concurrency's sake.)

All I'm asking is, how do I do local data access and decouple queries from
updates in IBO, like you can do in any other framework, even for Firebird?

Is that really such a difficult question?

--- In IBObjects@yahoogroups.com, Rohit Gupta <r.gupta@...> wrote:
>
> Mason,
>
> as Helen said, read up about transactions. They work the way they do in
> firebird (and hence IBO). Your wishing them to change will not make
> them change. What you are describing is how non-sql databases used to
> work such as btrieve. Thats not how firebird works.
>
> If you want multiple datasets to commit/rollback together, yo have to
> start them up with the same transaction, before you fetch data or create
> data.
>
> Rohit
>
> On 25/09/2012 9:20 a.m., masonwheeler wrote:
> >
> > If I was using DBX, and I wanted to ensure that changes from multiple
> > datasets were committed in a single transaction, it would go like this:
> >
> > tran := connection.BeginTransaction;
> > try
> > dataset1.ApplyUpdates;
> > dataset2.ApplyUpdates;
> > connection.CommitFreeAndNil(tran);
> > except
> > connection.RollbackFreeAndNil(tran);
> > end;
> >
> > IBO doesn't seem to work that way. And in fact, as I've just
> > discovered, it doesn't seem to work *at all* when trying to post
> > multiple datasets in one transaction.
> >
> > Here's what I'm trying, using datasets of type TIBOTable:
> >
> > tran := TIB_Transaction.Create(nil);
> > try
> > try
> > tran.IB_Connection := connection;
> > tran.StartTransaction;
> > dataset1.IB_Transaction := tran;
> > dataset2.IB_Transaction := tran;
> > dataset1.ApplyUpdates();
> > dataset2.ApplyUpdates();
> > tran.Commit;
> > except
> > tran.Rollback;
> > raise;
> > end;
> > finally
> > tran.Free;
> > end;
> >
> > The problem with this is that, for some bizarre reason, assigning the
> > transaction object to the dataset SILENTLY CLOSES THE DATASET, which
> > causes all the changes that I'm trying to commit to be lost!
> >
> > What in the world is going on here, and how do I get this to work
> > properly?
> >
> >
> >
> >
> > __________ Information from ESET Endpoint Antivirus, version of virus
> > signature database 7512 (20120924) __________
> >
> > The message was checked by ESET Endpoint Antivirus.
> >
> > http://www.eset.com
>
>
> --
> Regards
>
> *Rohit Gupta*
> B.E. Elec., M.E., Mem IEEE, Member IET
> Technical Director
> Computer Fanatics Ltd
>
> *Tel *4892280
> *Fax *4892290
> *Web *www.cfl.co.nz
> ------------------------------------------------------------------------
> This email and any attachments contain information, which is
> confidential and may be subject to legal privilege and copyright. If you
> are not the intended recipient, you must not use, distribute or copy
> this email or attachments. If you have received this in error, please
> notify us immediately by return email and then delete this email and any
> attachments.
>
>
> [Non-text portions of this message have been removed]
>




------------------------------------

___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !
Yahoo! Groups Links