dbkonverter_paradox/Unit1.pas

UNIT Unit1;

INTERFACE

USES
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Vcl.Buttons, Data.DB,
  Bde.DBTables, Vcl.Grids, Vcl.DBGrids, JvExDBGrids, JvDBGrid,
  ZAbstractRODataset, ZAbstractDataset, ZAbstractTable, ZDataset,
  ZAbstractConnection, ZConnection, Vcl.ComCtrls, Vcl.ExtCtrls, Vcl.DBCtrls,
  JvDBControls, IniFiles, AppPath, JvExControls, JvLabel, STR_Tool_UTF;

TYPE
  Tmain = CLASS(TForm)
    BitBtn1: TBitBtn;
    OD: TOpenDialog;
    sfl: TLabel;
    T1: TTable;
    DS: TDataSource;
    JvDBGrid1: TJvDBGrid;
    ZDB: TZConnection;
    ZT1: TZTable;
    BitBtn2: TBitBtn;
    ZQ1: TZQuery;
    ST: TStatusBar;
    JvDBNavigator1: TJvDBNavigator;
    AP: TAppPath;
    PROCEDURE BitBtn1Click(Sender: TObject);
    PROCEDURE BitBtn2Click(Sender: TObject);
    PROCEDURE FormCreate(Sender: TObject);
  PRIVATE
    { Private-Deklarationen }
  PUBLIC
    { Public-Deklarationen }
  END;

VAR
  main: Tmain;
  sfile: STRING;
  Server, User, Password, Catalog: STRING;

IMPLEMENTATION

{$R *.dfm}
// -----------------------------------------------------------------------------

PROCEDURE Tmain.BitBtn1Click(Sender: TObject);

BEGIN
  IF OD.Execute THEN
  BEGIN
    sfile := OD.FileName;
    sfl.Caption := OD.FileName;
    T1.Active := False;
    T1.DatabaseName := ExtractFileDir(OD.FileName);
    T1.TableName := ExtractFilename(OD.FileName);
    T1.Active := TRUE;
    ST.Panels[0].Text := IntToStr(T1.RecordCount) + ' Datensätze';
    ST.Refresh;
    BitBtn2.Enabled := TRUE;
  END;
END;

// -----------------------------------------------------------------------------

PROCEDURE Tmain.BitBtn2Click(Sender: TObject);

VAR
  i, rc, fc: Integer;
  fn: STRING;
  createSQL: TStringList;

BEGIN
  FOR fc := 0 TO OD.Files.count - 1 DO
  BEGIN
    sfile := OD.Files[fc];
    sfl.Caption := OD.Files[fc];
    T1.Active := False;
    T1.DatabaseName := ExtractFileDir(OD.Files[fc]);
    T1.TableName := ExtractFilename(OD.Files[fc]);
    T1.Active := TRUE;
    ST.Panels[0].Text := IntToStr(T1.RecordCount) + ' Datensätze';
    ST.Refresh;
    fn := ExtractFilename(OD.Files[fc]);
    fn := Copy(fn, 1, POS('.', fn) - 1);
    ZQ1.SQL.Clear;
    ZDB.Connect;
    ZQ1.SQL.Add('USE ' + UPPERCASE(Catalog) + ';');
    ZQ1.SQL.Add('DROP TABLE IF EXISTS ' + lowercase(fn) + ';');
    ZQ1.SQL.Add('CREATE TABLE ' + lowercase(fn) + ' (');
    FOR i := 0 TO T1.Fielddefs.count - 1 DO
    BEGIN
      IF T1.Fielddefs[i].Datatype = ftString THEN
        ZQ1.SQL.Add('['+T1.Fielddefs[i].Name+']' + ' varchar(max) NULL,')
      ELSE IF T1.Fielddefs[i].Datatype = ftMemo THEN
        ZQ1.SQL.Add('['+T1.Fielddefs[i].Name+']' + ' varchar(max) NULL,')
      ELSE IF T1.Fielddefs[i].Datatype = ftBoolean THEN
        ZQ1.SQL.Add('['+T1.Fielddefs[i].Name+']' + ' bit NULL,')
      ELSE IF T1.Fielddefs[i].Datatype = ftFloat THEN
        ZQ1.SQL.Add('['+T1.Fielddefs[i].Name+']' + ' float NULL,')
      ELSE IF T1.Fielddefs[i].Datatype = ftCurrency THEN
        ZQ1.SQL.Add('['+T1.Fielddefs[i].Name+']' + ' decimal(19,2) NULL,')
      ELSE IF T1.Fielddefs[i].Datatype = ftDate THEN
        ZQ1.SQL.Add('['+T1.Fielddefs[i].Name+']' + ' date NULL,')
      ELSE IF T1.Fielddefs[i].Datatype = ftTime THEN
        ZQ1.SQL.Add('['+T1.Fielddefs[i].Name+']' + ' datetime NULL,')
      ELSE IF T1.Fielddefs[i].Datatype = ftSmallint THEN
        ZQ1.SQL.Add('['+T1.Fielddefs[i].Name+']' + ' int NULL,')
      ELSE IF T1.Fielddefs[i].Datatype = ftInteger THEN
        ZQ1.SQL.Add('['+T1.Fielddefs[i].Name+']' + ' int NULL,')
      ELSE
      BEGIN
        ZQ1.SQL.Add('['+T1.Fielddefs[i].Name+']' + ' varchar(max) NULL,')
      END;
    END;
    ZQ1.SQL[ZQ1.SQL.count - 1] := FilterChar(ZQ1.SQL[ZQ1.SQL.count - 1], ',');
    ZQ1.SQL.Add(');');
    ZQ1.ExecSQL;
    ZT1.Active := False;
    ZT1.TableName := UPPERCASE(Catalog) + '.dbo.' + lowercase(fn);
    ZT1.Active := TRUE;
    T1.First;
    rc := 0;
    REPEAT
      inc(rc);
      ST.Panels[0].Text := 'Verarbeite Datensatz ' + IntToStr(rc) + ' von ' + IntToStr(T1.RecordCount);
      ST.Refresh;
      ZT1.Append;
      FOR i := 0 TO T1.Fields.count - 1 DO
      BEGIN
        IF T1.Fields[i].value <> NULL THEN
        BEGIN
          TRY
            IF T1.Fielddefs[i].Datatype = ftString THEN
              ZT1.Fields[i].Text := T1.Fields[i].Text
            ELSE
              ZT1.Fields[i].value := T1.Fields[i].value
          EXCEPT
            ON e: Exception DO
            BEGIN
              ShowMessage(T1.Fielddefs[i].Name);
            END;
          END;
        END;
      END;
      TRY
        ZT1.Post;
      EXCEPT
        ON e: Exception DO
        BEGIN
          ShowMessage(e.Message);
        END;
      END;
      T1.Next;
      application.ProcessMessages;
    UNTIL T1.Eof;;
  END;
  MessageDlg('Vorgang beendet', mtInformation, [mbOK], 0);
END;

// -----------------------------------------------------------------------------

PROCEDURE Tmain.FormCreate(Sender: TObject);

VAR
  ini: TIniFile;

BEGIN
  ZDB.Connected := False;
  ini := TIniFile.Create(AP.AppPath + 'config.ini');
  Server := ini.ReadString('SETTINGS', 'SERVER', '');
  User := ini.ReadString('SETTINGS', 'USER', '');
  Password := ini.ReadString('SETTINGS', 'PASSWORD', '');
  Catalog := ini.ReadString('SETTINGS', 'CATALOG', '');
  ini.Free;
  ZDB.Database := 'Provider=SQLNCLI11.1;Persist Security Info=False;User ID=sa;Initial Catalog="";Data Source=' + Server + ';Initial File Name="";Server SPN=""';
  ZDB.HostName := Server;
  ZDB.User := User;
  ZDB.Password := Password;
  ZDB.Catalog := Catalog;
  ZDB.Connected := TRUE;
  ST.Panels[1].Text := 'Server: ' + Server;
  ST.Panels[2].Text := 'Datenbank: ' + Catalog;
  ST.Panels[3].Text := 'DB-User: ' + User;
  ST.Refresh;
END;

// -----------------------------------------------------------------------------

END.