TMSTable InsertId or SCOPE_IDENTITY()

[jmuehlenhoff]

Hi,

is there a way to get the identity value from the TMSTable or TMSQuery component after calling Append + Post?

I tried to run a query with 'SELECT SCOPE_IDENTITY() AS "InsertId"' after the insertion process, but I always get NULL from the database. Any ideas why this doesn't work? I looked at the dbMonitor output which looks good ...

[jmuehlenhoff]

Ok, I found out why I get NULL. It's "too late", because I call SCOPE_IDENTITY() in another batch (so in another scope):

http://stackoverflow.com/questions/7298 ... turns-null

This means that the only way to get the identity value is through TMSTable and/or TMSQuery. Is there such a way?

[AndreyZ]

Hello,

Both TMSTable and TMSQuery return the IDENTITY field value automatically by default. It is controlled by the QueryIdentity option (the default value is True). QueryIdentity is used to specify whether to request the IDENTITY field value on execution of the Insert or Append method. Here are code examples:

Code: Select all

MSTable1.TableName := 'tablename';
MSTable1.Open;
MSTable1.Append;
MSTable1.Post; // here the IDENTITY field will be filled with the value obtained from the server

Code: Select all

MSQuery1.SQL.Text := 'select * from tablename';
MSQuery1.Open;
MSQuery1.Append;
MSQuery1.Post; // here the IDENTITY field will be filled with the value obtained from the server

Please make sure that the QueryIdentity option is set to True. For more information, please refer to the SDAC documentation.

[jmuehlenhoff]

I get it now, you're automatically updating the field value using the returned SCOPE_IDENTITY(). That's really nice.

However this doesn't work for tables without identity fields that use a UNIQUEIDENTIFIER as the primary key instead.

I know that it's possible to get the value of that primary key field in SQL Server 2005 and newer:

INSERT INTO CorApps (Name, FirstBuildDate) OUTPUT inserted._ID VALUES('A', '2000-01-01')

I'm using the OUTPUT-clause here. Would it be possible for TMSTable to automatically update the primary key field using the OUTPUT-clause?

[AndreyZ]

When adding new rows, SQL Server fills values of IDENTITY fields automatically. SQL Server does not do this for any other fields, including UNIQUEIDENTIFIER fields. If you want SQL Server to generate values for UNIQUEIDENTIFIER fields, you can set the default value of these fields to NEWID() . Here is an example of using NEWID():

Code: Select all

CREATE TABLE TEST(
  ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT (newid())
)

In this case, when adding new rows, SQL Server generates new values for UNIQUEIDENTIFIER fields automatically. SDAC can use default values of any fields. For this, the DefaultValues options is used. DefaultValues is used to fill the DefaultExpression property of fields by an appropriate value obtained from the server. So, to make SDAC return UNIQUEIDENTIFIER field values (and any other field that have default values on the server), you can use the following code:

Code: Select all

MSTable1.Options.DefaultValues := True;
MSTable1.TableName := 'tablename';
MSTable1.Open;
MSTable1.Append;
MSTable1.Post; // here the UNIQUEIDENTIFIER field will be filled with the value obtained from the server

Code: Select all

MSQuery1.Options.DefaultValues := True;
MSQuery1.SQL.Text := 'select * from tablename';
MSQuery1.Open;
MSQuery1.Append;
MSQuery1.Post; // here the UNIQUEIDENTIFIER field will be filled with the value obtained from the server

For more information about the UNIQUEIDENTIFIER data type, please refer to http://msdn.microsoft.com/en-us/library/ms187942.aspx

[jmuehlenhoff]

That works just fine, thanks.

[AndreyZ]

If any other questions come up, please contact us.