Problem with TUniLoader, SQLServer and Identity Columns

[JimMellish]

I am using Delphi XE on Windows XP for this.

I have an sql script which contains thousands of inserts into different tables. I am trying to convert this script to use TUniLoader to decrease the loading times.

Each insert statement does not always contain all the columns for the table and each insert statement never includes an identity column.

For these reasons, in my PutData procedure, I want to initialise every column to null and then set the values I want to insert.

If the table being inserted has an identity column then an 'EOLEDBError' 'Unspecified error' exception is raised when the second row is inserted. If the table has no identity column then the inserts work correctly.

The following code demonstrates the problem. If table AgrH contains an identity column then the code will fail in PutData when i = 1.

{------------------------------------------------------------------------------}

procedure TForm1.PutData (Sender: TDALoader);
var i, j: integer;
begin
for i := 0 to 1 do
begin
{ Initialise every column to null }
for j := 0 to Sender.Columns.Count - 1 do
Sender.PutColumnData (j, i+1, null);

{ Load the actual data }
...
end;
end;

{------------------------------------------------------------------------------}

procedure TForm1.Button1Click(Sender: TObject);
var UniLoader: TUniLoader;
begin
UniLoader := TUniLoader.Create (nil);
try
UniLoader.Connection := UniConnection;
UniLoader.TableName := 'AgrH';
UniLoader.CreateColumns;
UniLoader.OnPutData := PutData;
UniLoader.Load;
finally
UniLoader.Free;
end;
end;

[AlexP]

Hello,

You can't insert the Null value to Identity fields. On a standard INSERT INTO table(Identity) values (null) you will get an error message:
"DEFAULT or NULL are not allowed as explicit identity values.", but since UniLоader uses the FastLoader interface, you get another error message.
To solve the problem, you should remove insertion to the Identity column.

[JimMellish]

Hi Alex,

I replace null with 0 (zero) for the identity columns and the program now works correctly.

With SqlServer, using TUniLoader has resulted in a reduction of 75% in runtime so this was worth doing.

[AlexP]

Hello,

Yes, in contrast to the null value, 0 is acceptable to be inserted to the identity field, therefore you can use this value. However, since when inserting the record, the server will automatically generate the value of this field (if this option is not disabled forcibly), there is no need to insert even the 0 value to this field.