TMSLoader with an Identity Column

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
GNiessen
Posts: 28
Joined: Wed 18 Jan 2012 20:15

TMSLoader with an Identity Column

Post by GNiessen » Thu 16 Jun 2016 20:41

Is it possible to insert data into a table that has an Identity field as the first column?

I have a Table:

Code: Select all

CREATE TABLE [dbo].[LipidCoord](
	[LipidCoord] [int] IDENTITY(1,1) NOT NULL,
	[LipidID] [int] NOT NULL,
	[LipidX] [numeric](15, 8) NOT NULL,
	[LipidY] [numeric](15, 8) NOT NULL,
 CONSTRAINT [PK_LipidCoord] PRIMARY KEY CLUSTERED 
(
	[LipidCoord] ASC,
	[LipidID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
Which I am filling by query:

Code: Select all

INSERT INTO dbo.LipidCoord (LipidID, LipidX, LipidY)
VALUES(:pLipidID, :pLipidX, :pLipidY)
But I want to use the loader to improve performance for the 1000+ entries.

but I am getting an "Unspecified Error" in the PutData when I call the PutColumnData for the second row.

Code: Select all

  for Idx := 1 to FLines.Count-13 do        //Skips header
  begin
    Sender.PutColumnData(0,Idx,Null);
    Sender.PutColumnData(1,Idx,FLipidID);
    DataLine.DelimitedText := FLines[Idx+12];
    X := StrToFloatDef(DataLine[0],-1);
    Y := StrToFloatDef(DataLine[1],-1);
    Sender.PutColumnData(2,Idx,X);
    Sender.PutColumnData(3,Idx,Y);
  end;

ViktorV
Devart Team
Posts: 2299
Joined: Wed 30 Jul 2014 07:16

Re: TMSLoader with an Identity Column

Post by ViktorV » Fri 17 Jun 2016 09:31

Thank you for the information. We will investigate the behavior of SDAC and inform you about the results.
As a workaround, to make server insert a value for the IDENTITY field automatically, set the TMSLoader.Options.KeepIdentity property to False. And in the TMSLoader.OnPutData event handler, set a non-Null value for this field.
If you need to insert a user value for the IDENTITY field, then before calling the TMSLoader.Load method, set the TMSLoader.Options.KeepIdentity property to True, and in the TMSLoader.OnPutData event handler, set this field to the needed value.

Post Reply