Getting Previous MS SQL Indent Value

1

Good day everyone,

I have been researching how to retrive the previously (server side) created auto-numbered/auto-incremented field value in an MS SQL table.

I found an example that looked like this:

SomeSQLTable{PROP:SQL} = 'SELECT IDENT_CURRENT(<39>' & SomeSQLTable & '<39>)'
Access:SomeSQLTable.Next()
Relate:SomeSQLTable.Delete(0)

The SELECT IDENT_CURRENT works and I understand the need for the NEXT but what is the reason for the DELETE (If it’s needed at all.)?

Thank you!

2

I believe that gives you the Identity across all connections/users, not just the record that you created. There’s a help topic in Clarion " Server Side Auto incrementing" that addresses the issue. Otherwise, you can just access the highest numbered identity field and simplfy your code.

3

Turns out it is real simple.

For example is the first field in the table is:

SysID Long

And there is a primary key associated with that field:

PKSysIDKey

Simply insert an option on the SysID field.

IsIdentity=True

Now, the value of SysID will be available after you perform an Insert or Add.

ScreenHunter 111
ScreenHunter 1111429×1165 160 KB

SomeTable       FILE,DRIVER('MSSQL'),OWNER(Glo:Owner),NAME('dbo.SomeTable'),PRE(Som),CREATE
PKSysIDKey        KEY(Som:SysID),PRIMARY,NOCASE
record            RECORD
SysID               LONG
                  END
                END
4

The new MSSQL2 driver makes use ofvthe RETURNING clause. When you do an ADD , and the table contains identity [1] fields then those are returned by the INSERT INTO and automatically populated into the record for you.

In other words, theres nothing for you to fo, the new value is just “there”.

[1] returning works on other fields to. Like ReadOnly and fields-with-defaults-not-included-in-the-add.

5

There’s also a help topic AUTOINCUSESSCOPEIDENTITY.

I guess it’s a possible X Y problem, though.
What are you trying to accomplish by getting the auto-numbered value?

6

I am reading incoming JSON where data is parsed into several MSSQL tables.

I needed a way to get the server side auto-numbered value immediately after the ADD.

Bruce,

That’s pretty cool!

7

I still don’t understand what you’re trying to do, Don.

Are you inserting into MSSQL or is a different app doing it?
Do you have control over the SQL table (could you use a GUID instead of autonumber?)
What are you going to do with the just-inserted identity value when you retrieve it?

8

Did you try this

SELECT SCOPE_IDENTITY();

How to get the value of autoincrement of last row at the insert

Cheers