Re: Getting result of odbc's INSERT INTO command

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

On Sun, 24 Apr 2005 12:15:09 +0200, Joe <joerider@xxxxxxxxx> wrote:

>Severian [MVP] wrote:
>
>>>>BEGIN TRANSACTION
>>>>INSERT INTO...
>>>>SELECT MAX(id) FROM table
>>>>END TRANSACTION
>>>
>>>I found something about
>>>SELECT @@IDENTITY
>>>
>>>What is your opinion about that?
>>
>>
>> Not sure, but AFAIK it's not standard SQL. It may be an extension in
>> one or more databases.
>
>My problem is that other clients could add records in the meantime. My
>module is using only one connection to serve multiple clients. So I can
>use a lock.
>
>The Select MAX(id) in this case seems dangerous to me.

Of course, that's why I said:

>Depending on the transaction isolation level, and assuming you're
>using transactions and auto-increment IDs, you *may* be able to:
>
>BEGIN TRANSACTION
>INSERT INTO...
>SELECT MAX(id) FROM table
>END TRANSACTION
>
>But I don't if this is failsafe under any specific conditions. The
>same isolation levels that make this work may allow you to receive an
>ID inserted by another connection, and not all databases support all
>isolation levels.

--
Phillip Crews aka Severian
Microsoft MVP, Windows SDK
Posting email address is real, but please post replies on the newsgroup.
.

Quantcast