Page 1 of 1
TMSTable InsertId or SCOPE_IDENTITY()
Posted: Wed 17 Oct 2012 11:20
by 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 ...
Re: TMSTable InsertId or SCOPE_IDENTITY()
Posted: Wed 17 Oct 2012 11:28
by 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?
Re: TMSTable InsertId or SCOPE_IDENTITY()
Posted: Wed 17 Oct 2012 12:18
by 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.
Re: TMSTable InsertId or SCOPE_IDENTITY()
Posted: Wed 17 Oct 2012 12:52
by 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?
Re: TMSTable InsertId or SCOPE_IDENTITY()
Posted: Thu 18 Oct 2012 07:31
by 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
Re: TMSTable InsertId or SCOPE_IDENTITY()
Posted: Thu 18 Oct 2012 09:00
by jmuehlenhoff
That works just fine, thanks.
Re: TMSTable InsertId or SCOPE_IDENTITY()
Posted: Thu 18 Oct 2012 09:53
by AndreyZ
If any other questions come up, please contact us.