| Subject | RE: [IBO] loosing records Help |
|---|---|
| Author | Norman Dunbar |
| Post date | 2002-01-10T08:20:41Z |
Hi Helen,
this could make a nice TI :o)
Regards,
Norman.
-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar@...
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------
-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Thursday, January 10, 2002 5:49 AM
To: IBObjects@yahoogroups.com
Subject: Re: [IBO] loosing records Help
At 08:24 PM 09-01-02 -0800, Daniel Bertin wrote:
at all.
Post is a DATASET method which stores a new version (or a new record) as a
pending change in the database. At that point, it can be seen only by the
transaction which posted it. It is neither committed to ( = "stored
permanently in") the database, nor cancelled.
If your TRANSACTION (or IBODatabase) has Autocommit set True, then every
Post will be followed automatically by an attempt to commit; otherwise, the
transaction stays open and pending until you explicitly call COMMIT or
ROLLBACK.
Now, if you are using CachedUpdates, then the dataset buffer is effectively
deactivated and the current transaction ends. The user works in the cache
buffer and then make a calls to ApplyUpdates. That calls StartTransaction,
which temporarily deactivates autocommit behaviour, even if your
transaction/IBODatabase does have Autocommit set True.
Then, ApplyUpdates([...]) will POST the caches for the named datasets. If
any of these posts fails, you will get an ISC error and all of the changes
(including good ones) remain pending.
At this point your application needs to intervene by intercepting the ISC
error, interpreting it, and providing the user with a way to fix it. Here
you would typically send the user a message identifying the problem record
and telling her what to do about it. Following that, your code would call
Abort (not CancelUpdates) to resume user access to the cached records.
You would do all of this inside a try..except block. Beyond this block (in
error-free territory) you would call COMMIT in order to commit the changes
to the database.
Even if you are not using cached updates, you still have to ensure that a
COMMIT actually occurs. A commit (actually, commitretaining) that is
signalled by a call to Post when Autocommit is true still needs to be
checked to intercept errors from the database because, if an error (or
locking conflict) occurs, the Commit will fail. This failure applies to all
records in the transaction that had changes pending, not just the bad
record.
could check that by querying for it from a different transaction, e.g,
through IB_SQL, for example. And/or, in development, you can watch it in
the ib_monitor.
changes will be written permanently into the database. Possibly what is
happening here is that you are ignoring database errors and assuming that
everything else got committed...?
Now, where are these records?
1. Insert, Edit and Delete are client-side methods. The server has no
knowledge of them. All the server knows is that a transaction has a certain
set of records from one or some tables. That's all. So, after the SELECTed
dataset reaches the client, the application has them stored in some buffers
and the server knows that an uncommitted transaction has them. If the
dataset buffers are in insert, edit or delete state, the server has no
knowledge of it.
2. Now, a user performs some DML operation on a record in the client
buffers. Now the buffer "knows" that something has changed, but the server
still doesn't know anything more than it knew when the transaction started.
3. Next, the user posts some changes. Now, the client program sends
details of these changes across to the server and the server, in its cache,
creates a new record version for each affected record. If possible, it will
lock these records. Then, if the changes are valid, there will be no errors
and the Post has succeeded. But the records are still only "new record
versions", pending a successful Commit.
4. Next (through Autocommit or an explicit call to Commit) the client
attempts to commit these new record versions to the database. Even now, the
commit can fail through locking constraints on further records posted since
the last call to Post. Remember, if one record fails in the Commit
operation, all of the pending records will fail also.
So, if your users are genuinely NOT seeing their work committed, then you
have a hole in your application somewhere that is either failing to handle
failed commits or is actually failing to call commit by one means or
another.
But be sure that these apparently failed transactions really did fail, and
that you are not simply looking at datasets which your application code
failed to refresh after a Commit. The data that your users see in the
buffer really are just a snapshot of records that fitted the criteria of the
SQL statement at the moment they were requested. It is up to your
application to get a fresh snapshot when the user needs one.
There are plenty of properties and methods in IBO to get a fresh view for
the user - refresh, invalidate, buffer synchro flags, to name but a few...
regards,
Helen
All for Open and Open for All
Firebird Open SQL Database ยท http://firebirdsql.org
_______________________________________________________
___________________________________________________________________________
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 !
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
This email is intended only for the use of the addressees named above and
may be confidential or legally privileged. If you are not an addressee you
must not read it and must not use any information contained in it, nor copy
it, nor inform any person other than Lynx Financial Systems or the
addressees of its existence or contents. If you have received this email
and are not a named addressee, please delete it and notify the Lynx
Financial Systems IT Department on 0113 2892990.
this could make a nice TI :o)
Regards,
Norman.
-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar@...
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------
-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Thursday, January 10, 2002 5:49 AM
To: IBObjects@yahoogroups.com
Subject: Re: [IBO] loosing records Help
At 08:24 PM 09-01-02 -0800, Daniel Bertin wrote:
>Hi all,Daniel, cool down, take deep breaths. :))
>I 'm in a panic,
>does any one know of any instances of loosingNo. But you can "lose" records if they were never committed to the database
>records(rows) in a table without deleting?
at all.
>I complete a activity record by setting a complete flag to Y, Store thePost <> Commit.
>values of the record in variables, post to the table,
>then I. Insert into a IBOTable, store the variable values into the fields
>and post the changes.
Post is a DATASET method which stores a new version (or a new record) as a
pending change in the database. At that point, it can be seen only by the
transaction which posted it. It is neither committed to ( = "stored
permanently in") the database, nor cancelled.
If your TRANSACTION (or IBODatabase) has Autocommit set True, then every
Post will be followed automatically by an attempt to commit; otherwise, the
transaction stays open and pending until you explicitly call COMMIT or
ROLLBACK.
Now, if you are using CachedUpdates, then the dataset buffer is effectively
deactivated and the current transaction ends. The user works in the cache
buffer and then make a calls to ApplyUpdates. That calls StartTransaction,
which temporarily deactivates autocommit behaviour, even if your
transaction/IBODatabase does have Autocommit set True.
Then, ApplyUpdates([...]) will POST the caches for the named datasets. If
any of these posts fails, you will get an ISC error and all of the changes
(including good ones) remain pending.
At this point your application needs to intervene by intercepting the ISC
error, interpreting it, and providing the user with a way to fix it. Here
you would typically send the user a message identifying the problem record
and telling her what to do about it. Following that, your code would call
Abort (not CancelUpdates) to resume user access to the cached records.
You would do all of this inside a try..except block. Beyond this block (in
error-free territory) you would call COMMIT in order to commit the changes
to the database.
Even if you are not using cached updates, you still have to ensure that a
COMMIT actually occurs. A commit (actually, commitretaining) that is
signalled by a call to Post when Autocommit is true still needs to be
checked to intercept errors from the database because, if an error (or
locking conflict) occurs, the Commit will fail. This failure applies to all
records in the transaction that had changes pending, not just the bad
record.
>the table has a filter which is set to complete = N, so that users onlyHow can you tell that the record definitely gets written to the table? You
>have access to not complete records.
>
>the record definitely gets written to the table (as far as I can tell) and
>the process continues for about 200-300 records during the course of a day.
could check that by querying for it from a different transaction, e.g,
through IB_SQL, for example. And/or, in development, you can watch it in
the ib_monitor.
>every so often when the user returns to find the record(previouslyNo log file. But, as long as COMMIT gets called and it succeeds then the
>inserted) it has disapeared.I looked in the table and no trace of it. only
>the completed one for that particular truck ID. this really puzzles me.
>does FB have a log file somewhere?
changes will be written permanently into the database. Possibly what is
happening here is that you are ignoring database errors and assuming that
everything else got committed...?
>has anyone ever heard of this type of behavior? when I post a record, do IOK, no cached updates, so you ARE working inside an isolated transaction.
>directly write to the table (cache updates off) does the record get stored
>in a buffer somewhere? where did it go????
Now, where are these records?
1. Insert, Edit and Delete are client-side methods. The server has no
knowledge of them. All the server knows is that a transaction has a certain
set of records from one or some tables. That's all. So, after the SELECTed
dataset reaches the client, the application has them stored in some buffers
and the server knows that an uncommitted transaction has them. If the
dataset buffers are in insert, edit or delete state, the server has no
knowledge of it.
2. Now, a user performs some DML operation on a record in the client
buffers. Now the buffer "knows" that something has changed, but the server
still doesn't know anything more than it knew when the transaction started.
3. Next, the user posts some changes. Now, the client program sends
details of these changes across to the server and the server, in its cache,
creates a new record version for each affected record. If possible, it will
lock these records. Then, if the changes are valid, there will be no errors
and the Post has succeeded. But the records are still only "new record
versions", pending a successful Commit.
4. Next (through Autocommit or an explicit call to Commit) the client
attempts to commit these new record versions to the database. Even now, the
commit can fail through locking constraints on further records posted since
the last call to Post. Remember, if one record fails in the Commit
operation, all of the pending records will fail also.
So, if your users are genuinely NOT seeing their work committed, then you
have a hole in your application somewhere that is either failing to handle
failed commits or is actually failing to call commit by one means or
another.
But be sure that these apparently failed transactions really did fail, and
that you are not simply looking at datasets which your application code
failed to refresh after a Commit. The data that your users see in the
buffer really are just a snapshot of records that fitted the criteria of the
SQL statement at the moment they were requested. It is up to your
application to get a fresh snapshot when the user needs one.
There are plenty of properties and methods in IBO to get a fresh view for
the user - refresh, invalidate, buffer synchro flags, to name but a few...
regards,
Helen
All for Open and Open for All
Firebird Open SQL Database ยท http://firebirdsql.org
_______________________________________________________
___________________________________________________________________________
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 !
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
This email is intended only for the use of the addressees named above and
may be confidential or legally privileged. If you are not an addressee you
must not read it and must not use any information contained in it, nor copy
it, nor inform any person other than Lynx Financial Systems or the
addressees of its existence or contents. If you have received this email
and are not a named addressee, please delete it and notify the Lynx
Financial Systems IT Department on 0113 2892990.