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!
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.
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.
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
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.
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?
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!
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?
Did you try this
SELECT SCOPE_IDENTITY();
How to get the value of autoincrement of last row at the insert
Cheers
Hi,
I have a similar situation…
On the DCT, I’ve set the field with IsIdentity=True
Inside the transaction block, after a successful TryInsert(), I use the following code to retrieve the new ID:
myTable{PROP:SQLRowSet} = 'declare @new_id int ' &|
' SELECT @new_id =@@Identity FROM myTable' &|
' SELECT @new_id '
Next(myTable)
Just in case you are trying to reinvent the wheel badly, maybe google “mssql import json nested tables”, and decide whether what you are doing has any advantage. Even if you eventually want your imported data to have some sort of identity value that is not in the JSON data, the cleanest way is probably to import your JSON data into staging tables, then worry about copying your parentIDs into the children when you do the transfer into your real tables.
And that transfer is (IMO) something you would do in a stored procedure, so it all happens in the database.
I thought if you had isIdentity=true in your dct Clarion automatically retrieved the Identity value.
Also, I don’t think @@Identity is reliable in a multi-user system? ScopeIdentity is better.
IsIdentity=True worked for me.
That feature is only available in C10 or newer.
Hi,
Really don’t know…
We have this piece of code working for years here at work, with a MS SQL Server on premise…
Maybe it’s not the better way, but it works as desired…
Don is Available from clarion 6.0
@@Identity isn’t reliable if there are triggers on your table that might also insert a row in a different table that has an identity column also.
@@Identity returns the last identity created on your connection regardless of the table.
It is reliable for most databases.
Users please Quote the part of the message that pertains to your reply so the reader can understand without scrolling back past other replies to read the original.
@Flavio_Suarez you replied to @PurpleEdge2214.
That message has 2 parts. I’d guess your “We have this piece of code working for years” was referring to this part?
@Omar_Squiabro you replied “Don is Available from clarion 6.0” but there was a Reply in the middle. You should have quoted Don:
Don is Available from clarion 6.0
To Quote a post simply select the desired text with your mouse and a Popup menu will offer “Quote | Edit | Copy Quote”. Clikc on “Quote” amd it will be inserted. You can edit the quote, like in Don’s I took out the double space,