Insert into table using TUniLoader from TStringList

[miguelenguica]

Hi. I have not used TUniLoader before. Can anyone help me and post a simple example of how to (programmatically) insert the content of a TStringList where the values are separated by ";", using a TUniLoader?

Code: Select all

String list:
112;10;90;1
(...)
90;1;10;1

Code: Select all

Into table:
  field1 : int;
  field2 : int;
  field3 : int;
  field4 : int;

My StringList has about a million records and it's too slow to perform regular inserts with transactions.

Thank you!

[AlexP]

hello,

There is a small application below, that demonstrates the work with UniLoader

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

uses
  SysUtils,
  Classes,
  DALoader,
  UniLoader,
  DB,
  DBAccess,
  Uni,
  UniProvider,
  PostgreSQLUniProvider;

type
  TMyclass = class
  private
    FUniConnection: TUniConnection;
    FUniLoader: TUniLoader;
    FStringList: TStringList;
  protected
    procedure UniLoaderPutData(Sender: TDALoader);
  public
    constructor Create(ProviderName: String; Server: String; DB: String; Login: String; Password: String; Port: Integer; LoginPromt: boolean = False);
    destructor Destroy; override;
    procedure PutDataFromStringList(FileName: String; TableName: String);
  end;
{ TMyclass }

constructor TMyclass.Create(ProviderName, Server, DB, Login, Password: String;
  Port: Integer; LoginPromt: boolean);
begin
  FUniConnection := TUniConnection.Create(nil);
  FUniConnection.ProviderName := ProviderName;
  FUniConnection.Server := Server;
  FUniConnection.Port := Port;
  FUniConnection.Database := DB;
  FUniConnection.LoginPrompt := LoginPromt;
  FUniConnection.Username := Login;
  FUniConnection.Password := Password;

  FUniLoader := TUniLoader.Create(nil);
  FUniLoader.Connection := FUniConnection;
  FUniLoader.OnPutData := UniLoaderPutData;
end;

procedure TMyclass.UniLoaderPutData(Sender: TDALoader);
var
  i, count: Integer;
  tmp: TStringList;
begin
  count := FStringList.Count;
  tmp := TStringList.Create;
  tmp.Delimiter := ';';
  for i := 1 to count do begin
    tmp.DelimitedText := FStringList[i - 1];
    Sender.PutColumnData(0, i, tmp[0]);
    Sender.PutColumnData(1, i, tmp[1]);
    Sender.PutColumnData(2, i, tmp[2]);
    Sender.PutColumnData(3, i, tmp[3]);
    tmp.Clear;
  end;
  tmp.Free;
end;

procedure TMyclass.PutDataFromStringList(FileName, TableName: String);
begin
  if not Assigned(FStringList) then
    FStringList := TStringList.Create
  else
    FStringList.Clear;
  FStringList.LoadFromFile(FileName);
  FUniConnection.ExecSQL('DROP TABLE IF EXISTS '+ TableName, []);
  FUniConnection.ExecSQL('CREATE TABLE ' + TableName + ' (fireld1 integer, fireld2 integer, fireld3 integer, fireld4 integer);', []);
  FUniLoader.TableName := TableName;
  FUniLoader.Load;
end;

var
  MyClass: TMyclass;

destructor TMyclass.Destroy;
begin
  FStringList.Free;
  FUniLoader.Free;
  FUniConnection.Free;
end;

begin
  MyClass := TMyclass.Create('PostgreSQL', 'Serevr', 'DB', 'postgres', 'postgres', 5432);
  MyClass.PutDataFromStringList('D:\1.txt', 'uni_loader');
  MyClass.Free;
  Readln;
end.