Data from .DBF File Not Copying To SQLite File

Question

I am using Delphi XE5 with Windows 7 OS.

I have .dbf file and this file data I need to move to SQLite file.

Observation: When I import this file in NaviCat for SQLite, there I see data in ASCII format. While Copying data from ADODataset (which hold the data from .dbf file), I see ftWideString and ftWideMemo, am I making mistake while assigning appropriate data type for "Query" component which is of type TFDQuery? But it is not the case always i.e. .dbf file could also contains normal alpha numeric characters also. Intention is to post any type of data from .dbf file to SQLite file.

Below procedure post the data into SQLite file, I am not getting any error while posting the data but when I open the SQLite file in NaviCat, I don't see blank record.

procedure TfrmMainForm.InsertDatabtnClick(Sender: TObject);

I am trying with below code:

Code:

const
  MyDBFile = 'C:\TempDB\MYSQLightDB.db';

type
  TfrmMainForm = class(TForm)
    ADOConnection1: TADOConnection;
    CreateTablebtn: TButton;
    ADODataSet1: TADODataSet;
    DataSource1: TDataSource;
    FDGUIxWaitCursor1: TFDGUIxWaitCursor;
    InsertDatabtn: TButton;
    FDQuery1: TFDQuery;
    SQLConnection1: TSQLConnection;
    ADODataSet2: TADODataSet;

    procedure CreateTablebtnClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure InsertDatabtnClick(Sender: TObject);
  private
    { Private declarations }
    Connection : TFDConnection;
    DriverLink : TFDPhysSQLiteDriverLink;
    Table : TFDTable;
    Query : TFDQuery;
  public
    { Public declarations }
  end;

var
  frmMainForm: TfrmMainForm;

implementation

{$R *.dfm}


procedure TfrmMainForm.FormShow(Sender: TObject);
begin
  CreateComponents;
end;

procedure TfrmMainForm.CreateTablebtnClick(Sender: TObject);
begin
  ConnectTodatabaseFile;
end;

procedure TfrmMainForm.ConnectTodatabaseFile;
var
  dbf_folder : string;
begin
  dbf_folder:='C:\TempDB';//set your dbf folder location here
  ADOConnection1.LoginPrompt:=false;
  ADOConnection1.ConnectionString:=Format('Provider=Microsoft.JET.OLEDB.4.0;Data Source=%s;Extended Properties=dBase IV;',[dbf_folder]);
  ADODataSet1.ConnectionString:=Format('Provider=Microsoft.JET.OLEDB.4.0;Data Source=%s;Extended Properties=dBase IV;',[dbf_folder]);
  try
    ADOConnection1.Connected:=True;
    ADODataSet1.CommandText:='Select * from MyFileName.dbf'; //make your SQL query using the name of the dbf file
    ADODataSet1.Open;

    CreateSQLiteTable;

    ShowMessage('Table created successfully');
  except
    on E : Exception do
      ShowMessage(E.Message);
  end;
end;

procedure TfrmMainForm.CreateSQLiteTable;
var
  FFieldName, FCreateSQL : string;
  FColumnCount : Integer;
begin
  FCreateSQL := 'Create Table MyTable1 (';
  for FColumnCount := 0 to ADODataSet1.FieldCount - 1 do
  begin
    FFieldName := ADODataSet1.Fields[FColumnCount].FieldName;
    FCreateSQL := FCreateSQL + FFieldName + ' ' + FieldTypeToSQLString(ADODataSet1.Fields[FColumnCount].DataType, ADODataSet1.Fields[FColumnCount].DataSize);

    if FColumnCount <> ADODataSet1.FieldCount - 1 then
      FCreateSQL := FCreateSQL + ', ';
  end;

  FCreateSQL := FCreateSQL + ')';

  Query.Close;
  Query.SQL.Clear;
  Query.SQL.Add(FCreateSQL);
  Query.ExecSQL;
end;

procedure TfrmMainForm.InsertDatabtnClick(Sender: TObject);
var
  FSQLString : String;
  FColumnCount : Integer;
begin
  Query.Close;
  Query.CachedUpdates := True;
  Query.SQL.Clear;
  Query.SQL.Add('Select * from MyTable1 where 1 = 2');
  Query.Active := True;

  ADODataSet1.First;
  while not ADODataSet1.eof do
  begin
    Query.Insert;

    for FColumnCount := 0 to ADODataSet1.FieldCount - 1 do
    begin
      Query.Fields[FColumnCount].Value := ADODataSet1.Fields[FColumnCount].Value;
    end;

    ADODataSet1.Next;
  end;

  Query.Edit;
  Query.Post;
  Query.CommitUpdates;

  ShowMessage('Data Inserted');
end;

procedure TfrmMainForm.CreateComponents;
begin
  DriverLink := TFDPhysSQLiteDriverLink.Create(Self);
  Connection := TFDConnection.Create(self);

  Connection.Params.Values['DriverID'] := 'SQLite';
  Connection.Params.Values['Database'] := MyDBFile;
  Connection.Connected := True;

  Table := TFDTable.Create(self);
  Query := TFDQuery.Create(self);

  Query.Connection := Connection;
  Table.Connection := Connection;
end;

procedure TfrmMainForm.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  DeleteComponents;
end;

procedure TfrmMainForm.DeleteComponents;
begin
  Connection.Free;
  DriverLink.Free;
  Table.Free;
  Query.Free;
end;

Function TfrmMainForm.FieldTypeToSQLString(T : TFieldType; L : Longint) : String;
Begin
  case T of
    ftString : Result := 'VARCHAR('+IntToStr(L)+')';
    ftSmallint : Result := 'SMALLINT';
    ftInteger : Result := 'INTEGER';
    ftWord : Result := 'SMALLINT';
    ftBoolean : Result := 'BOOLEAN';
    ftFloat : Result := 'FLOAT';
    ftCurrency : Result := 'MONEY';
    ftBCD : Result := 'DECIMAL';
    ftDate : Result := 'DATE';
    ftTime : Result := 'TIME';
    ftDateTime : Result := 'TIMESTAMP';
    ftBytes : Result := 'BLOB('+IntToStr(L)+',2)';
    ftVarBytes : Result := 'BLOB('+IntToStr(L)+',2)';
    ftAutoInc : Result := 'AUTOINC';
    ftBlob : Result := 'BLOB('+IntToStr(L)+',1)';
    ftMemo : Result := 'BLOB('+IntToStr(L)+',1)';
    ftGraphic : Result := 'BLOB('+IntToStr(L)+',5)';
    ftFmtMemo : Result := 'BLOB('+IntToStr(L)+',3)';
    ftParadoxOle : Result := 'BLOB('+IntToStr(L)+',4)';
    ftDBaseOle : Result := 'BLOB('+IntToStr(L)+',4)';
    ftTypedBinary : Result := 'BLOB('+IntToStr(L)+',2)';
    ftFixedChar : Result := 'CHAR('+IntToStr(L)+')';
    ftWideString : Result := 'VARCHAR('+IntToStr(L)+')';
    ftWideMemo : Result := 'NTEXT';
    ftLargeInt : Result := 'INTEGER'
  else
    Result := 'UNKNOWN!';
  end;
End;

end.

In your ConnectTodatabaseFile, why do you repeatedly call CreateSQLiteTable, once for each row of AdoDataSet1? Surely it only needs to be called once? — MartynA, Apr 26 '16 at 06:32
In this case, Yes it is not needed. I have corrected the code. Thank You. Any guess about data not posting? — Vishal Tiwari, Apr 26 '16 at 06:36

MartynA · Answer 1 · 2016-04-26T19:07:34.213

1

In your loop below, you should be calling Query.Post for each row that you insert, and as you have already called Query.Insert, you don't need the call to Query.Edit. In any case calling .Edit and then immediately calling .Post would achieve nothing, but I'm not sure whether that's just a careless mistake or a fundamental lack of understanding of how to work with datasets.

  while not ADODataSet1.eof do
  begin
    Query.Insert;

    for FColumnCount := 0 to ADODataSet1.FieldCount - 1 do
    begin
      Query.Fields[FColumnCount].Value := ADODataSet1.Fields[FColumnCount].Value;
    end;

    //Query.Edit;
    Query.Post;
    ADODataSet1.Next;
  end;

  Query.CommitUpdates;

  ShowMessage('Data Inserted');

In view of these elementary mistakes and the one I pointed out in my comment, I think you should very carefully review your code from beginning to end.

Regarding your own answer, I have a couple of comments:

You don't need to keep calling Query.Close because calling Query.ExecSQL does not leave it open.
It would be much better (and safer in a SqlInjection context) to set up a parameterised version of your Insert SQL before your while not AdoDataset1.Eof loop and then only supply the parameter values inside the loop. You would avoid a lot of messing around with QuotedStr() if you did that and in any case you should avoid dynamically constructing SQL because of the SqlInjection point.

edited Apr 26 '16 at 19:07

answered Apr 26 '16 at 06:45

MartynA

30,454
4
32
73

AStill there is a blank record in SQLite file. – Vishal Tiwari Apr 26 '16 at 06:55
1

Then do some debugging. – MartynA Apr 26 '16 at 06:58
After data post if I try to fetch the data using "Query" component, it shows Query.RecordCount is zero. Let me see now. This means data is not posting at all. – Vishal Tiwari Apr 26 '16 at 07:01
Well, no-one else has your data available to test. If it were me, I would check that when you call .Post on the Query, its fields actually contain data. If your Query's Where clause still has "Where 1 =2" in it, it is hardly surprising that not data is returned, is it? You don't need that Where clause even to insert the data. – MartynA Apr 26 '16 at 07:30
Even I removed "Where 1 = 2" still record count was zero. But now I got the success. Please look at my answer. Thanks a lot for you kind help. – Vishal Tiwari Apr 26 '16 at 09:39
Although not completely solve the problem, the answer is helpful and should be evaluated. +1 – Val Marinov Apr 26 '16 at 10:21
I do feel the same. Anything you find missing? Would you please elaborate, if possible? – Vishal Tiwari Apr 26 '16 at 13:16
Thanks. I've added a couple of comments about the code in your answer. – MartynA Apr 26 '16 at 19:40
Thanks MartynA. I agree with you comments. It was just a demo project I was trying. But when I import the same .DBF file via NaviCat and check the data posted from this procedure. I find that the most of the table ( in which data posted using our procedure) columns having some extra ascii characters at the end. – Vishal Tiwari Apr 27 '16 at 06:05
Used TrimRight and now data is same. FSQLString := FSQLString + QuotedStr(TrimRight(ADODataSet1.Fields[FColumnCount].AsString)); – Vishal Tiwari Apr 27 '16 at 06:23

Vishal Tiwari · Answer 2 · 2016-04-26T13:18:58.017

Below is the way, I got my data posted to SQLite file.

Code:

procedure TfrmMainForm.InsertDatabtnClick(Sender: TObject);
var
  FSQLString : WideString;
  FColumnCount : Integer;
  FSQLPrepared : Boolean;
begin
  FSQLPrepared := False;
  Query.Close;
  Query.CachedUpdates := True;

  if not ADODataSet1.Active then
    ADODataSet1.Active := True;

  ADODataSet1.First;
  while not ADODataSet1.eof do
  begin
    //Also script component could be used to execute bulk amount of insert SQL
    FSQLString := 'Insert Into MyTable1 Values(';

    for FColumnCount := 0 to ADODataSet1.FieldCount - 1 do
    begin
      //Query.Fields[FColumnCount].Value := ADODataSet1.Fields[FColumnCount].Value;
      FSQLString := FSQLString + QuotedStr(ADODataSet1.Fields[FColumnCount].AsString);

      if FColumnCount <> ADODataSet1.FieldCount - 1 then
        FSQLString := FSQLString + ', ';
    end;

    FSQLString := FSQLString + ')';

    Query.Close;
    Query.SQL.Clear;
    Query.SQL.Add(FSQLString);

    if not FSQLPrepared then
    begin
      Query.Prepared := True;
      FSQLPrepared := True;
    end;

    Query.ExecSQL;

    ADODataSet1.Next;
  end;

  ShowMessage('Data Inserted');
end;

score 0 · Accepted Answer · answered Apr 29 '16 at 09:52

I got it resolved in another way by modifying the procedure mentioned in the problem. Just added below statement before comiting changes to the database.

Query.ApplyUpdates(0);

And "Query.Post;" statement is not needed in the loop.

Complete procedure:

procedure TfrmMainForm.InsertDatabtnClick(Sender: TObject);
var
  FSQLString : String;
  FColumnCount : Integer;
begin
  Query.Close;
  Query.CachedUpdates := True;
  Query.SQL.Clear;
  Query.SQL.Add('Select * from MyTable1 where 1 = 2');
  Query.Active := True;

  ADODataSet1.First;
  while not ADODataSet1.eof do
  begin
    Query.Insert;

    for FColumnCount := 0 to ADODataSet1.FieldCount - 1 do
    begin
      Query.Fields[FColumnCount].Value := ADODataSet1.Fields[FColumnCount].Value;
    end;

    ADODataSet1.Next;
  end;

  Query.ApplyUpdates(0);
  Query.CommitUpdates;

  ShowMessage('Data Inserted');
end;

Data from .DBF File Not Copying To SQLite File

3 Answers3