"Dave Harrison" <daveh_18824@spammore.com> wrote in message
news:C32F83A7-4659-480D-AB80-049A3B4DAE10@news.elevatesoft.com...
>
> The only drawback I see of doing it your way is what happens if another
> user deletes the record you just added? You won't be able to retrieve it
> to get the last autoinc value.
>

If the record can be deleted by another user, then you have the same generic
situation that you have with any other multi-user table where your current
record can be changed or deleted by another user. You need to trap the
exception after you attempt to post the update. But I don't think that was
the issue here.

Your original question was:
---
Ok so before the process starts I need to append a new record to the
table and fill in all the information except for ElapsedTIme.

with taLogTable do
  begin
    Append;
    FieldByName('Log_Date').AsDateTime := Now();
    FieldByName('Description').AsString := '....';
    Post;
  end;

Then when the process completes I will come back and update the ElapsedTime.

with taLogTable do
  begin
    Edit;
    FieldByName('Elapsed_Time').AsFloat := ElapsedTime;
    Post;
  end;


So my question is can I simple assume the log table is on the current
record (last record appended) when I go to write the ElapsedTime?

---

The answer is that you don't care if it is the last record appended. It is
the record YOU appended, and that's enough as far as your logic is
concerned. As long as nothing in YOUR program has changed or repositioned
your cursor, if you added record number 3, that's what will still be sitting
on your current record, even if thru additions by other users the
lastautoinc is now 100000.

Using  lastautoinc is contrary to multi-user design. I'm not saying there is
NEVER any use for the beast, simply that it is by definition a single user
type field, and trying to use it in to control your logic in multi user
systems can cause all kinds of unnecessary grief.

Robert

Dave


Sounds both interesting and a disaster waiting to happen.

Roy Lambert

Dave,

<< The only drawback I see of doing it your way is what happens if another
user deletes the record you just added? You won't be able to retrieve it to
get the last autoinc value. >>

Is that a possibility in this case ?  At any rate, like Robert indicated
DBISAM will simply issue an #8708 in such a case.  However, such an instance
should be fairly rare since most applications aren't in the habit of
deleting brand new rows added by another session.

--
Tim Young
Elevate Software
www.elevatesoft.com

Robert wrote:
> "Dave Harrison" <daveh_18824@spammore.com> wrote in message
> news:C32F83A7-4659-480D-AB80-049A3B4DAE10@news.elevatesoft.com...
>
>>The only drawback I see of doing it your way is what happens if another
>>user deletes the record you just added? You won't be able to retrieve it
>>to get the last autoinc value.
>>
>
>
> If the record can be deleted by another user, then you have the same generic
> situation that you have with any other multi-user table where your current
> record can be changed or deleted by another user. You need to trap the
> exception after you attempt to post the update. But I don't think that was
> the issue here.
> Your original question was:
> ---
> Ok so before the process starts I need to append a new record to the
> table and fill in all the information except for ElapsedTIme.
>
> with taLogTable do
>    begin
>      Append;
>      FieldByName('Log_Date').AsDateTime := Now();
>      FieldByName('Description').AsString := '....';
>      Post;
>    end;
>
> Then when the process completes I will come back and update the ElapsedTime.
>
> with taLogTable do
>    begin
>      Edit;
>      FieldByName('Elapsed_Time').AsFloat := ElapsedTime;
>      Post;
>    end;
>
>
> So my question is can I simple assume the log table is on the current
> record (last record appended) when I go to write the ElapsedTime?
>
> ---
>
> The answer is that you don't care if it is the last record appended. It is
> the record YOU appended, and that's enough as far as your logic is
> concerned. As long as nothing in YOUR program has changed or repositioned
> your cursor, if you added record number 3, that's what will still be sitting
> on your current record, even if thru additions by other users the
> lastautoinc is now 100000.
>
> Using  lastautoinc is contrary to multi-user design. I'm not saying there is
> NEVER any use for the beast, simply that it is by definition a single user
> type field, and trying to use it in to control your logic in multi user
> systems can cause all kinds of unnecessary grief.
>
> Robert

Sorry for adding to the confusion. You have answered my original
question correctly and that's what I ended up using.

My last couple of posts did change the "question" because I was feeling
out how lastautoinc could be used in general and how it compared to
MySQL's Last_Insert_Id(). If I were using "Insert into table..." then I
can see some benefit to using LastAutoInc.

Normally I prefer to use standard SQL and stay away from live
table/queries in case I need to switch to a different database.

Dave

"Dave Harrison" <daveh_18824@spammore.com> wrote in message
news:2675C9DF-6736-41B2-BFCF-D27C7FE2AC37@news.elevatesoft.com...
>
> Normally I prefer to use standard SQL and stay away from live
> table/queries in case I need to switch to a different database.
>

There are C/S solutions usually involving a trigger and in file sharing
mode, you can use a table contining the next value. But using lastautoinc on
an active table with multiple users is inherently bad, IMO. Either you end
up locking the table[s] with a transaction, or sooner or later you will have
a system problem.

I always thought that the query should have a read only property containing
the last autoinc number assigned to the current record, so that one could do
inserts with a query and still have the autoinc value available for cases
such as master / detail. But nobody listens to me, so I end up using
tTables.

Robert

Robert wrote:
> "Dave Harrison" <daveh_18824@spammore.com> wrote in message
> news:2675C9DF-6736-41B2-BFCF-D27C7FE2AC37@news.elevatesoft.com...
>
>>Normally I prefer to use standard SQL and stay away from live
>>table/queries in case I need to switch to a different database.
>>
>
>
> There are C/S solutions usually involving a trigger and in file sharing
> mode, you can use a table contining the next value. But using lastautoinc on
> an active table with multiple users is inherently bad, IMO. Either you end
> up locking the table[s] with a transaction, or sooner or later you will have
> a system problem.
>
> I always thought that the query should have a read only property containing
> the last autoinc number assigned to the current record, so that one could do
> inserts with a query and still have the autoinc value available for cases
> such as master / detail. But nobody listens to me, so I end up using
> tTables.
>

Exactly! With queries you're pretty much limited. MySQL has solved the
problem with Last_Insert_Id() returning the last auto inc that was
posted by the user which can then be used to add the detail records for
the master that was just posted. I can understand now why you're
recommending using tables.

Tim, are you listening?

Dave

Dave,

<< Tim, are you listening? >>

Use this:

SELECT @@IDENTITY

It returns the last autoinc value assigned for the current session.

AFAIK, no one has ever asked me specifically for this except for you.   The
above has been in DBISAM for some time now - it was needed for the ODBC
driver to support some MS apps.

--
Tim Young
Elevate Software
www.elevatesoft.com

Dave,

BTW, the proper way to handle such a situation is via output parameters that
output the generated column value after the INSERT.   The session identity
value thing is a bit of a kludge because it can't handle different generated
columns and doesn't even distinguish between different tables.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
> Dave,
>
> << Tim, are you listening? >>
>
> Use this:
>
> SELECT @@IDENTITY
>
> It returns the last autoinc value assigned for the current session.
>
> AFAIK, no one has ever asked me specifically for this except for you.

Well, that's how we reference generated numbers in MySQL. I'm glad to
see you've got something comparable for us SQL-lovers.

>  The
> above has been in DBISAM for some time now - it was needed for the ODBC
> driver to support some MS apps.
>

Is there a topic for @@IDENTITY in the v4.x help file? I couldn't find
it. Thanks.

Dave

Dave,

<< Is there a topic for @@IDENTITY in the v4.x help file? I couldn't find
it. Thanks. >>

No, it has always been an undocumented thing that was simply there for
proper operation of the ODBC driver under certain MS apps.  It's similar to
the "Database"\"TableName" issue with Crystal Reports.   These type of
things have to be implemented in the main engine, but are not for general
use.

--
Tim Young
Elevate Software
www.elevatesoft.com