Page 1 of 1
IDENTITY_INSERT on Compact Edition
Posted: Wed 20 May 2009 08:37
by Jatvarthur
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?
Posted: Thu 21 May 2009 13:16
by Dimon
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.
Posted: Mon 25 May 2009 07:03
by Jatvarthur
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.
Posted: Mon 25 May 2009 12:46
by Dimon
To solve the problem you should set the TUniTable.Options.RequiredFields property and the TUniTable.Options.SetFieldsReadOnly property to False.
Posted: Mon 25 May 2009 14:16
by Jatvarthur
It finally works! Thanks!