Using TUniLoader with Firebird embedded [Delphi XE2, UniDAC 5.0.1]

[acetylator]

Hello,

I am trying to find a solution on how to insert a lot of data into a Firebird database using Firebird embedded server v2.5 and TUniLoader component.
I have a string list of data (mixed types - integer, string, etc.) formatted like this:

Code: Select all

123,abc,1,2
456,def,2,3
...

And a table with 4 fields. Currently, I am using a workaround - I pass my data to a procedure where I parse them and create SQL statements like this:

Code: Select all

EXECUTE BLOCK AS BEGIN
INSERT OR UPDATE INTO table_name VALUES (.....);
...
END

Then I pass prepared SQL script to TUniScript, which executes it. The problem is that the execution is relatively slow - it does not exceed approx. 1000 records per second. Also, HDD light is blinking very actively during the operation. I think that there must be some bottleneck there of which I am not aware of... I think that 1000 rec/sec is a very low number.
Anyway, I wanted to give TUniLoader a try. Accordgin to the documentation, it should do basically the same I do, but I thought maybe I miss something and TUniLoader will be quicker.
The problem is that I am not able to load data that I have into some dataset which can be used with TUniLoader.LoadFromDataSet. As I said, I have a string list with data of various types. I tried to create TVirtualTable, added needed number of fields of ftBlob type - then I was able to populate TVirtualTable. But when I call LoadFromDataSet, it crashes. I would be very grateful if someone could help me and post some piece of code which shows how can I add data from delimitered string list into a database using TUniLoader.

Thank you!

[AndreyZ]

Hello,

Here is a code example:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  UniLoader1.TableName := 'test';
  UniLoader1.Load;
end;

procedure TForm1.UniLoader1PutData(Sender: TDALoader);
var
  strData, strTemp: TStringList;
  i: integer;
begin
  strData := TStringList.Create;
  strTemp := TStringList.Create;
  try
    // strData imitates your list of data
    strData.Add('123,abc,1,2');
    strData.Add('456,def,2,3');
    strTemp.Delimiter := ',';
    for i := 0 to strData.Count - 1 do begin
      strTemp.DelimitedText := strData[i];
      Sender.PutColumnData(0, i + 1, strTemp[0]);
      Sender.PutColumnData(1, i + 1, strTemp[1]);
      Sender.PutColumnData(2, i + 1, strTemp[2]);
      Sender.PutColumnData(3, i + 1, strTemp[3]);
    end;
  finally
    strTemp.Free;
    strData.Free;
  end;
end;

, where test is the following table:

Code: Select all

CREATE TABLE TEST (
    ID    INTEGER PRIMARY KEY,
    VC    VARCHAR(20),
    INT1  INTEGER,
    INT2  INTEGER
);