Returning server side newid() uniqueidentifier after insert

[treierts]

I have the following table in SQL Server:

Code: Select all

CREATE TABLE [dbo].[Category](
	[CategoryID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ParentCategoryID] [uniqueidentifier] NULL,
	[Name] [nvarchar](255) NOT NULL,
	[Description] [nvarchar](1024) NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 

In my Delphi application I have the following setup:

DataSource -> ClientDataSet -> DataSetProvider -> TMSQuery

The TMSQuery.SQL.Text is:

Code: Select all

TMSQuery.SQL.Text := 'SELECT * FROM [Category] WHERE [CategoryID]=:CategoryID;';

I have set the Parameter CategoryID to be of type GUID.

All fields have been added to both the TMSQuery component AND the ClientDataSet. The parameter has also been added to the ClientDataSet.

When the system inserts a new record, I do the following (the CategoryID parameter value is NIL as it will be created on the server):

Code: Select all

ClientDataSet.Open;
ClientDataSet.Append;
ClientDataSetName.AsString := 'Test';
ClientDataSet.ApplyUpdates(0);

How can I get the CategoryIDas it is created on the server (using newid())? Are there any settings I can set on the TMSQuery (or DataProvider) to get this value back?

Best Regards,

Tom Reiertsen

[AndreyZ]

Hello,

To obtain the value of the NEWID() function, you should use your own SQL code for inserting records and the BeforeUpdateExecute and AfterUpdateExecute event handlers. Here is a code example:

Code: Select all

procedure TMainForm.BitBtnWorkClick(Sender: TObject);
begin
  MSQuery.SQL.Text := 'SELECT * FROM [Category] WHERE [CategoryID]=:CategoryID;';
  MSQuery.SQLInsert.Clear;
  MSQuery.SQLInsert.Add('DECLARE @myid uniqueidentifier;');
  MSQuery.SQLInsert.Add('SET @myid = NEWID();');
  MSQuery.SQLInsert.Add('insert into category(CategoryID, ParentCategoryID, Name, Description)');
  MSQuery.SQLInsert.Add('values (@myid, :ParentCategoryID, :Name, :Description);');
  MSQuery.SQLInsert.Add('SET :CategoryID = @myid;');
  DataSetProvider.ResolveToDataSet := True;
  ClientDataSet.Open;
  ClientDataSet.Append;
  ClientDataSetName.AsString := 'Testing';
  ClientDataSet.ApplyUpdates(0);
end;

procedure TMainForm.MSQueryBeforeUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
begin
  if stInsert in StatementTypes then
    Params.ParamByName('CategoryID').ParamType := ptInputOutput;
end;

procedure TMainForm.MSQueryAfterUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
begin
  if stInsert in StatementTypes then
    ShowMessage(Params.ParamByName('CategoryID').AsString); // here you will see the value of the NEWID() function
end;