Using SQLInsert with identity column and refreshing after insert

[swm4]

I am in the process of migrating from Oracle to SQL Server 2012. I have used Unidac 4.6.11. I was hoping that moving database platforms would be pretty straightforward if I use standard SQL to do my queries.

This is an example of a problem I am having when inserting records. I have a table called Person with a primary key called person_id. In SQL Server person_id is set up as an identify column. Here is the query object setup:

Code: Select all

SQL: SELECT * FROM person
SQLInsert: INSERT INTO person (person_id, name) VALUES (:PERSON_ID, :NAME)
SQLRefresh: WHERE person_id = :PERSON_ID
KeyFields = person_id
Options.RefreshOptions = roAfterInsert
Options.ReturnParams = True
Options.RequiredFields = False

When connected to Oracle:

Code: Select all

SpecificOptions.Oracle.KeySequence = person_seq

For an Oracle connection, after posting an inserted record and the record is refreshed, it shows the new value for person_id.

But connected SQL Server, when posting an inserted record, I get an error that says "cannot insert explicit value for identity column in table person when identity_insert is set to OFF".

Is there a way to insert into a table with an identity column in SQL Server so that the UniDac query object behaves the same as the Oracle insertion (refreshing after insert) without needing to change the queries?

[CristianP]

Hello,

For SQL Server you can write your values to identity fields only with:

Code: Select all

SET IDENTITY_INSERT person ON

But this is usually used for moving data between tables because identity is an auto incrementing type and should be used usually with IDENTITY_INSERT = OFF

You should change to:

Code: Select all

SQLInsert: INSERT INTO person (name) VALUES (:NAME)

Or if you do not need autoincrementing field use simply integer.

Best Regards,
Cristian Peta

[AndreyZ]

1. If you do not use your own query for the INSERT operation, UniDAC automatically fills the IDENTITY field with the value returned from the server. It is controlled by the QueryIdentity specific 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 is a code example:

Code: Select all

begin
  UniQuery1.SQL.Text := 'select * from test_ident'; // the test_ident table has two fields: id INT IDENTITY, name VARCHAR(20)
  UniQuery1.Open;
  UniQuery1.Append;
  UniQuery1.FieldByName('name').AsString := 'test';
  UniQuery1.Post;
  ShowMessage(IntToStr(UniQuery1.FieldByName('id').AsInteger));
end;

2. If you want to use your own query for the INSERT operation, you should not specify the IDENTITY field in the INSERT statement, but you should get the value of SCOPE_IDENTITY() and set it to a corresponding parameter. Also, you should use the BeforeUpdateExecute event handler to set the ParamType property of the corresponding parameter to ptInputOutput. Here is a code example:

Code: Select all

begin
  UniQuery1.SQL.Text := 'select * from test_ident';
  UniQuery1.SQLInsert.Text := 'insert into test_ident (name) values (:name); set :id = SCOPE_IDENTITY()';
  UniQuery1.Options.ReturnParams := True;
  UniQuery1.Open;
  UniQuery1.Append;
  UniQuery1.FieldByName('name').AsString := 'test';
  UniQuery1.Post;
  ShowMessage(IntToStr(UniQuery1.FieldByName('id').AsInteger));
end;

procedure TForm1.UniQuery1BeforeUpdateExecute(Sender: TDataSet;
  StatementTypes: TStatementTypes; Params: TDAParams);
begin
  if stInsert in StatementTypes then
    Params.ParamByName('id').ParamType := ptInputOutput;
end;