Clarion Community Help

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:

AUTOINC

PREAUTOINC

PROP:ServerAutoInc

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