AUTOINCUSESSCOPEIDENTITY
MSSQL has three ways of returning the auto-incremented field: @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT( 'table_name' ). All have problems.
IDENT_CURRENT( 'table_name' ) is not very useful as it returns the current identity regardless of which user last updated the table. So as soon as another user does an ADD between the calls to ADD and getting IDENT_CURRENT you get the wrong value.
@@IDENTITY gives you the last identity value set by this connection. This works fine unless you have an insert trigger that inserts other records that have identity fields. Then you end up getting the identity field for the wrong table. The default server side auto increment system uses this variable.
SCOPE_IDENTITY() returns the identity set by the statement executed on this statement handle.
As of Clarion version 10, If you add the driver string /AUTOINCUSESSCOPEIDENTITY=TRUE to the driver string of a MSSQL file, the driver will use SCOPE_IDENTITY() using the same statement handle as the INSERT to retrieve the auto-incremented value.
See Also:
Example code:
tzi FILE,DRIVER('MSSQL','/AUTOINC=SELECT @@identity
/TRUSTEDCONNECTION=TRUE'),PRE(tzi),OWNER(ownerVariable),NAME('tz')
pk KEY(tzi:id)
RECORD
Id LONG
Name STRING(20)
END
END
tzs FILE,DRIVER('MSSQL','/ AUTOINCUSESSCOPEIDENTITY=TRUE /TRUSTEDCONNECTION=TRUE'),PRE(tzs),OWNER(ownerVariable),NAME('tz')
pk KEY(tzs:id)
RECORD
Id LONG
Name STRING(20)
END
END
CODE
tzi{PROP:SQL} = 'DROP TABLE TZ'
tzi{PROP:SQL} = 'DROP TABLE TY'
tzi{PROP:SQL} = 'CREATE TABLE TZ (id int IDENTITY(1,1) PRIMARY KEY, name
varchar(20) NOT NULL)'
tzi{PROP:SQL} = 'CREATE TABLE TY (id int IDENTITY(100,5) PRIMARY KEY, name
varchar(20) NOT NULL)'
tzi{PROP:SQL} = 'CREATE TRIGGER Ztrig ON TZ FOR INSERT AS BEGIN INSERT TY VALUES () END'
OPEN(tzi)
tzi{PROP:ServerAutoInc, 1} = 1
OPEN(tzs)
tzs{PROP:ServerAutoInc, 1} = 1
tzi:name = 'Lisa'
ADD(tzi)
tzi:name = 'Mike'
ADD(tzi)
tzi:name = 'Carla'
ADD(tzi)
tzi{PROP:ServerAutoInc} = 1
tzi:name = 'Rosalie'
ADD(tzi)
MESSAGE(ERRORCODE() & ':' & tzi:id) ! tzi:id will be 115
tzs:name = 'Ilka'
tzs{PROP:ServerAutoInc} = 1
ADD(tzs)
MESSAGE(ERRORCODE() & ':' & tzs:id) ! tzs:id will be 5