I have been wrestling with a problem for the last two or three days
concerning the acquisition of the LastAutoIncValue and finally after trying
practically everything I could think of found out the following for which I
would be grateful for confirmation.

I am developing an application which currently is based on a Local Session.
On opening the application is creates around 30 .dat files in localdir
cache.

I want to start a transaction, create a master record and post it then,
using the LastAutoIncValue from the master table, insert a new record in the
detail table(s) so that they are linked for future queries.  If the user
cancels the input from the detail record then a rollback situation occurs
removing the master record and restoring the last autoinc value to its
previous one.

For the past couple of days on querying the last autoinc value of the master
table I have been getting an absurd value in excess of 67 million; either
using the LastAutoIncValue method or doing a select max(id) from
mastertable.  The record was not written to the master table and the whole
scenario was regularly collapsing without completing the data write.

Creating a quick and dirty test application with only the one table on board
and testing the value of last autoinc after inserting to the table worked
perfectly; even my colleague had no problems accessing and writing to the
table. It was as soon as I used the current application that the problem
kept persisting.

To solve the situation I created an extra datasession and database, linked
the master table to these new creations and inserted a new row.  It worked
perfectly, returned a valid correct last autoinc value and wrote the records
to the detail tables as expected.

Two to three days this cost me to sort it so far.  Maybe I miss the point
here but I would have thought that only 30 tables open in my local cache
would not cause the problem to occur and quite honestly I couldn't find any
reference to a limit or to the problem anywhere.

Is there a way round this - or has anybody else encountered this?

Are there any more wrinkles or limitations like this that we should be aware
of?  This one nearly drove me to distraction and caused me seriously to
consider abandoning DBISAM for something else.

cheers - David

Forgot to mention:  Delphi 7, DBISAM 4.22.4

Sorry!
>

David


If all you did was to create another session & database it suggests to me a threading / isolation problem although the ones I know of haven't affected lastautoinc.

Also a value in excess of 67 million suggests either a very large table or, more likely, there is corruption. DBISAM is pretty robust, but it is possible to screw it up.

On the positive side, if you can create a demo that causes the problem on a repeatable basis Tim would be "delighted" to identify the root cause and fix it. Its one of the genuine benefits of using DBISAM as I know. The main problem I had was producing the demo app to reproduce the problem on a reliable basis.

Roy Lambert

"David Mustard" <d dot m dot mustard at chello dot nl> wrote in message
news:3DEC5538-0ED4-407C-9770-F6A3D5D31843@news.elevatesoft.com...
>I have been wrestling with a problem for the last two or three days
>concerning the acquisition of the LastAutoIncValue and finally after trying
>practically everything I could think of found out the following for which I
>would be grateful for confirmation.
>
....
>
> I want to start a transaction, create a master record and post it then,
> using the LastAutoIncValue from the master table, insert a new record in
> the detail table(s) so that they are linked for future queries.  If the
> user cancels the input from the detail record then a rollback situation
> occurs removing the master record and restoring the last autoinc value to
> its previous one.
>
This last line sounds strange to me (but I'm no expert) : Can you share your
code into showing us how you restore lastautoinc ? Is this a single user
application ?

I don't actually physically "restore" the last autoinc value; perhaps I
oversimplify the situation in my thread.  The master record is written
within a transaction pernding committal on acceptance of the detail record
(by the user clicking OK).  If OK then the detail record (within the same
transaction) is written and the on the following commit statement both
master and detail are written to the physical table.

If the user clicks cancel on the detail entry form then the transaction is
rolled back which removes the master record from the buffers and the autoinc
value of the master table is not increased since nothing has been
effectively posted to the physical table.  Maybe restoring was the wrong
word to use in this case.

Of course it is possible to adjust the last autoinc value in code or in a
DML sql statement as in ALTER TABLE tablename LAST AUTOINC value but if the
ID also happens to be the primary index this has to be done with some care
since it would cerainly have to be an unique value.

Yes, in this current case it is a single user application but also written
with a view to mulit-user usgae once thoroughly tested so most of the
activity is transactional based.

"Frans van Daalen" <Account@is.invalid> wrote in message
news:17713DDA-0594-4422-8789-B7FE275F138A@news.elevatesoft.com...
>
> "David Mustard" <d dot m dot mustard at chello dot nl> wrote in message
> news:3DEC5538-0ED4-407C-9770-F6A3D5D31843@news.elevatesoft.com...
>>I have been wrestling with a problem for the last two or three days
>>concerning the acquisition of the LastAutoIncValue and finally after
>>trying practically everything I could think of found out the following for
>>which I would be grateful for confirmation.
>>
> ...
>>
>> I want to start a transaction, create a master record and post it then,
>> using the LastAutoIncValue from the master table, insert a new record in
>> the detail table(s) so that they are linked for future queries.  If the
>> user cancels the input from the detail record then a rollback situation
>> occurs removing the master record and restoring the last autoinc value to
>> its previous one.
>>
> This last line sounds strange to me (but I'm no expert) : Can you share
> your code into showing us how you restore lastautoinc ? Is this a single
> user application ?
>

Hi Roy,

The table actually "offically" in testing has 13 record rows so not as large
as you suspect!  Although it would be possible to use such a large number by
setting the last autoinc value in either code or in an sql statement.

No I believe that this situation was possibly caused by the number of
handles allocated at the time of attempting to read from and write to the
table.  By experimenting and adding a temporary session to address the table
this allowed access to the master table and produced the correct reading of
the last autoinc value.  It also committed the record to the database.  The
reason why an eccessive number of tables were open is actaully a mystery
because there shouldn't have been right at that moment but the master and
detail grids do make a lot of use of lookup tables so that could explain it.

I believe that the number of concurrently open tables can be adjusted in the
engine but I need to take a closer look at the documentation and the source
before I move further on this.  I'm just relieved that after a couple of
days there was some movement in the blockage although it was a pretty
desperate action which solved it.

The application and source are pretty complicated to cobble up an example
for Tim to pore through and I'm not sure if I could actually extract just
this one module from the application to show the problem adequately
reliably.  If I can I will certainly send Tim a demo if it cannot be sorted
out after a little more reading up on the documnetaion and the source.

cheers - David.

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:572089AF-FFFF-4DDB-98E4-94DE04294416@news.elevatesoft.com...
> David
>
>
> If all you did was to create another session & database it suggests to me
> a threading / isolation problem although the ones I know of haven't
> affected lastautoinc.
>
> Also a value in excess of 67 million suggests either a very large table
> or, more likely, there is corruption. DBISAM is pretty robust, but it
> is possible to screw it up.
>
> On the positive side, if you can create a demo that causes the problem on
> a repeatable basis Tim would be "delighted" to identify the root cause and
> fix it. Its one of the genuine benefits of using DBISAM as I know. The
> main problem I had was producing the demo app to reproduce the problem on
> a reliable basis.
>
> Roy Lambert
>

David,

<< For the past couple of days on querying the last autoinc value of the
master table I have been getting an absurd value in excess of 67 million;
either using the LastAutoIncValue method or doing a select max(id) from
mastertable.  The record was not written to the master table and the whole
scenario was regularly collapsing without completing the data write. >>

Did you verify the tables to ensure that none were corrupted ?  You can do
so interactively using the DBSYS utility that comes with the Additional
Software and Utilities download.  That would be the first place to look.  If
that doesn't solve the problem, then my next suggestion would be to simply
try a SELECT Max(ID) query from DBSYS on the table in question.  If that
works, then something is wrong in your code.  You can also view the
LastAutoIncValue property for a table in DBSYS, so that should answer any
questions about whether the table has a whacky value in it for the last
autoinc value.

Also, just so you know - using transactions while waiting for user input is
strongly discouraged with DBISAM.  I noticed that you mentioned this in your
other post, and I just wanted to point it out.  You should only use
transactions when you know that there is a definite and short timeframe for
the transaction.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks for the reply Tim; I had done all the checks you mention (honest!)
and although I would be the first to suspect my own code I had a colleague
check it through twice - we also tried

- the sql select max() route,
- using an on-the-fly copy of the table and shooting for the
lastautoincvalue,
- testing the sql code in DBSYS,
- testing input in DBSYS,
- testing the code in a standalone application,
- stepping through the code on tip-toe (!),
- white heather and a rabbit's foot,
- voodoo dolls,
- totally rebuilding the table and resetting the data,
- doing a repair on all tables,
- crossing our fingers and rebuilding everything,
- crossing our fingers and upgrading to 4.22.4 from 4.18,
- crossing our eyes just in case it looked better,
- crossing our legs because things were getting pretty dire,
- doing a rename and copy and rename and using practically all the tools
available in DBSYS (yes we were getting pretty desperate by this time) and
- finally without making any changes in the actual code itself but just
adding in another session and database to the data module and linking the
table into the new session and database it all worked.

Setting the database and session back to the original caused the same
problem.  The code I used was the simplest you could imagine:

 LastMasterID := tblAURolMaster.LastAutoIncValue;

which kept failing whether or not the table was open or not.  The debugger
inspector was showing that some of the values were causing violations so it
appeared that the table couldn't be opened for writing properly.  This part
of the application is too large and complicated to reproduce without sending
all tables and source and I rely on DevExpress components so I would have to
include the source on that as well.  The crazy thing is that it had worked
in the past and I had done no work on it recently so there is/was no reason
to suspect the code.

I take your point on the transaction matter and will rearrange the timing on
writing data to the database - maybe the detail first, then the master then
update the detail with the new master record id.  That would mean that I
could do this within a transaction on the user accepting the new detail
record and would guarantee that the master record autoinc value returned
would genuinely be the last one on the table.

As I mentioned before I was getting really distressed on this one and still
don't know what was causing the problem having covered practically every
angle I and my coileague could think of.

cheers - David

Starange thing is that
"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:ACBD86BF-9D56-4B6D-8D2B-032703064E11@news.elevatesoft.com...
> David,
>
> << For the past couple of days on querying the last autoinc value of the
> master table I have been getting an absurd value in excess of 67 million;
> either using the LastAutoIncValue method or doing a select max(id) from
> mastertable.  The record was not written to the master table and the whole
> scenario was regularly collapsing without completing the data write. >>
>
> Did you verify the tables to ensure that none were corrupted ?  You can do
> so interactively using the DBSYS utility that comes with the Additional
> Software and Utilities download.  That would be the first place to look.
> If that doesn't solve the problem, then my next suggestion would be to
> simply try a SELECT Max(ID) query from DBSYS on the table in question.  If
> that works, then something is wrong in your code.  You can also view the
> LastAutoIncValue property for a table in DBSYS, so that should answer any
> questions about whether the table has a whacky value in it for the last
> autoinc value.
>
> Also, just so you know - using transactions while waiting for user input
> is strongly discouraged with DBISAM.  I noticed that you mentioned this in
> your other post, and I just wanted to point it out.  You should only use
> transactions when you know that there is a definite and short timeframe
> for the transaction.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

David


Ah someone else who uses my debugging system - but you forgot the step which starts "get drunk" (and often ends there as well)

Had you thought of using memory tables for the user interface? Nice and easy, you can use all the normal data aware tools but it isolates things from the live data until you're ready to commit.


Roy Lambert

David,

<< Thanks for the reply Tim; I had done all the checks you mention (honest!)
and although I would be the first to suspect my own code I had a colleague
check it through twice - we also tried >>

What was the result of the checks that I recommended ?  Is the table okay
and so is the last autoinc value for the table ?

--
Tim Young
Elevate Software
www.elevatesoft.com