IDENTITY_INSERT on Compact Edition

Jatvarthur · Post by **Jatvarthur** » Wed 20 May 2009 08:37

We use UniDAC to copy data between databases and need to insert explicit values to IDENTITY PRIMARY KEY columns to SQLCE3.5 databases. Before running copy procedure I issue SET IDENTITY_INSERT ON statement, and then use TUniTable.Insert/TUniTable.FieldByName().Value/TUniTable.Post to set all table fields (including an 'ID' field, which is the primary key for a table) to their proper values. TUniTable.Post raises an exception with SQLCE message saying that field 'ID' requires a value and cannot be NULL, so it looks like TUniTable doesn't include this field in the INSERT query. How could I insert an explicit value to the 'ID' field?

Dimon · Post by **Dimon** » Thu 21 May 2009 13:16

The problem is that SQL Server Compact edition sets the readonly attribute for IDENTITY fields even if you set IDENTITY_INSERT to ON.
To solve this problem you should set the TFieldDesc.ReadOnly property to False after opening table, like this:

Code: Select all

  MSQuery.GetFieldDesc('Fieldname').ReadOnly := False;

Or you can use SQLInsert property to specify the SQL statement that will be used when inserting to a dataset.

Jatvarthur · Post by **Jatvarthur** » Mon 25 May 2009 07:03

i've tried this but it doesn't do the trick. I received to following error:

Code: Select all

The column can't contain null values [0, 0, 0, Column name: id, Table name: tbl,]

which is the same as I had received before. Here is the code from my test sample

Code: Select all

  ScriptOn.Execute;
  Table.Open;
  Table.Insert;
  Table.GetFieldDesc('id').ReadOnly:= False;
  Table.FieldByName('id').ReadOnly:= False;
  Table.FieldByName('id').Value:= 1;
  Table.FieldByName('title').Value:= 'qwe1';
  Table.Post;
  Table.Close;
  ScriptOff.Execute;
  Table.Open;
  Table.Insert;
  Table.FieldByName('title').Value:= 'qwe10';
  Table.Post;
  Table.Close;

ScriptOn/ScriptOff execute SET IDENITY_INSERT ON and SET IDENTITY_INSERT OFF SQL statements respectively.

Also, it is ver inconvenient for me to use SQLInsert property and wrute a stament, becase I need the same piece of code to work with different tables in my DB. Any help would be greatly appreciated.

Dimon · Post by **Dimon** » Mon 25 May 2009 12:46

To solve the problem you should set the TUniTable.Options.RequiredFields property and the TUniTable.Options.SetFieldsReadOnly property to False.

Jatvarthur · Post by **Jatvarthur** » Mon 25 May 2009 14:16

It finally works! Thanks!