Using CopyDataSet to transfer a ParadoxTable to an SQLquery

[dpap]

I  use the code bellow

ParadoxDataset1.Open; // opens and shows the data in a TDBgrid
  SQLite3Connection1.open;
//  SQLQuery1.FieldDefs.Assign(ParadoxDataset1.FieldDefs);

  SQLQuery1.CopyFromDataset(ParadoxDataset1,TRUE);

The command raises the exception "cannot create when there are no fielddefinitions or fields" but the Help says that this function creats the definitions from source table!! The same happens even I uncomment the assign command.
What I am missing here?

[wp]

I'm not an SQLite3 expert, but my feeling is that you first must create the table by sql ("CREATE TABLE..."). I found an old post of mine converting a dBase table to SQLite3 (https://forum.lazarus.freepascal.org/index.php/topic,57542.msg428181.html#msg428181), and based on this I wrote the following procedure (It handles only a few field types, but it should be easy to extend to others):

Code: Pascal  [Select][+][-]

1. function FixFieldName(AFieldName: String): String;
2. begin
3.   Result := StringReplace(AFieldName, ' ', '_', [rfReplaceAll]);
4.   Result := StringReplace(Result, '(', '', [rfReplaceAll]);
5.   Result := StringReplace(Result, ')', '', [rfReplaceAll]);
6. end;
7.  
8. procedure CopyToSqlite3(ADataset: TDataset; AFileName: String);
9. var
10.   SQLiteConn: TSQLite3Connection;
11.   Transaction: TSQLTransaction;
12.   SQLQuery: TSQLQuery;
13.   f: TField;
14.   fieldName: String;
15.   sql: String;
16.   values: String;
17.   bm: TBookmark;
18. begin
19.   DeleteFile(AFileName);
20.  
21.   { Set up databse components }
22.   SQLiteConn := TSQLite3Connection.Create(nil);
23.   SQLiteConn.DatabaseName := AFileName;
24.   Transaction := TSQLTransaction.Create(SQLiteConn);
25.   Transaction.DataBase := SQLiteConn;
26.   Transaction.Action := caCommit;
27.   SQLiteConn.Open;
28.  
29.   { Create database and its fields }
30.   sql := 'CREATE TABLE "data" (';
31.   for f in ADataset.Fields do
32.   begin
33.     fieldName := FixFieldName(f.FieldName);
34.     case f.DataType of
35.       ftString: sql := sql + Format('"%s" VARCHAR(%d), ', [fieldName,  f.Size]);
36.       ftInteger: sql := sql + Format('"%s" INTEGER, ', [fieldName]);
37.       ftFloat: sql := sql + Format('"%s" FLOAT, ', [fieldName]);
38.       ftMemo: sql := sql + Format('"%s" VARCHAR, ', [fieldName]);
39.       else raise Exception.Create('Field type not supported here.');
40.     end;
41.   end;
42.   sql[Length(sql)-1] := ')';
43.   SQLiteConn.ExecuteDirect(sql);
44.   Transaction.Commit;
45.  
46.   { Add records as copy of dataset's records }
47.   sql := 'INSERT INTO "data" (';
48.   values := 'VALUES (';
49.   for f in ADataset.Fields do
50.   begin
51.     fieldName := FixFieldName(f.FieldName);
52.     sql := sql + Format('%s, ', [fieldName]);
53.     values := values + Format(':%s, ', [fieldName]);
54.   end;
55.   sql[Length(sql)-1] := ')';
56.   values[Length(values)-1] := ')';
57.   sql := sql + values;
58.  
59.   SQLQuery := TSQLQuery.Create(nil);
60.   SQLQuery.DataBase := SQLiteConn;
61.   SQLQuery.Transaction := Transaction;
62.   SQLQuery.SQL.Add(sql);
63.   bm := ADataset.GetBookmark;
64.   try
65.     ADataset.First;
66.     while not ADataset.EoF do
67.     begin
68.       for f in ADataset.Fields do
69.       begin
70.         fieldName := FixFieldName(f.FieldName);
71.         case f.DataType of
72.           ftString: SQLQuery.ParamByName(fieldName).AsString := f.AsString;
73.           ftInteger: SQLQuery.ParamByName(fieldName).AsInteger := f.AsInteger;
74.           ftFloat: SQLQuery.ParamByName(fieldName).AsFloat := f.AsFloat;
75.           ftMemo: SQLQuery.ParamByName(fieldName).Asstring := f.AsString;
76.         end;
77.       end;
78.       SQLQuery.ExecSQL;
79.       ADataset.Next;
80.     end;
81.     ADataset.GotoBookmark(bm);
82.   finally
83.     ADataset.FreeBookmark(bm);
84.   end;
85.  
86.   Transaction.Commit;
87.  
88.   SQLQuery.Close;
89.   SQLQuery.Free;
90.   Transaction.Free;
91.   SQLiteConn.Free;
92. end;

[dpap]

Thank you for your suggestion. I already use this approach but I was hopping that copyDataset would do it more easy and more robust!

[Zvoni]

Thank you for your suggestion. I already use this approach but I was hopping that copyDataset would do it more easy and more robust!

Why would you expect, just by assigning a PX-Dataset to an "sqlite"-Dataset, that it would create the table for you?
A Dataset is exactly just that: A representation of a List of "records" of Data with specific Datatypes.
you can even create unbound Datasets, which have never seen a Database-Table

[egsuh]

Code: Pascal  [Select][+][-]

1.         fieldName := FixFieldName(f.FieldName);
2.         case f.DataType of
3.           ftString: SQLQuery.ParamByName(fieldName).AsString := f.AsString;
4.           ftInteger: SQLQuery.ParamByName(fieldName).AsInteger := f.AsInteger;
5.           ftFloat: SQLQuery.ParamByName(fieldName).AsFloat := f.AsFloat;
6.           ftMemo: SQLQuery.ParamByName(fieldName).Asstring := f.AsString;
7.         end;

Here, case statement is not necessary.

        SQLQuery.ParamByName(fieldName).Asstring := f.AsString;

will suffice

[Zvoni]

Code: Pascal  [Select][+][-]

1.         fieldName := FixFieldName(f.FieldName);
2.         case f.DataType of
3.           ftString: SQLQuery.ParamByName(fieldName).AsString := f.AsString;
4.           ftInteger: SQLQuery.ParamByName(fieldName).AsInteger := f.AsInteger;
5.           ftFloat: SQLQuery.ParamByName(fieldName).AsFloat := f.AsFloat;
6.           ftMemo: SQLQuery.ParamByName(fieldName).Asstring := f.AsString;
7.         end;

Here, case statement is not necessary.

        SQLQuery.ParamByName(fieldName).Asstring := f.AsString;

will suffice

How do you figure that?
AsString with Params will add automatically Single-Quotes, which you don't want for Integers and floats

EDIT: FWIW @wp
There is an "easier" way to construct such a SQL-Statement without incremental concatenating, where you have to catch the last comma.
Keyword: Collect Fieldnames and values/paramnames in an Array each, and then "Join" the Array

[dpap]

@Zvoni, "Why would you expect, just by assigning a PX-Dataset to an "sqlite"-Dataset, that it would create the table for you?"
Because, Lazarus Help says "CopyFromDataset is a procedure used to copy field definitions and optional record data for the dataset specified in the Dataset argument.".
Doesn't this mean what it says?

[cdbc]

Hi

"CopyFromDataset is a procedure used to copy field definitions and optional record data for the dataset specified in the Dataset argument."

Hmmm... I thought that only pertains to 'TMemDataset'?!?
The /copy-method/ is kinda crude and it certainly doesn't support blobs.
Regards Benny

[Zvoni]

@Zvoni, "Why would you expect, just by assigning a PX-Dataset to an "sqlite"-Dataset, that it would create the table for you?"
Because, Lazarus Help says "CopyFromDataset is a procedure used to copy field definitions and optional record data for the dataset specified in the Dataset argument.".
Doesn't this mean what it says?

No. It gets the Field-Definitions from the Source-Dataset, not any "target"-table.
Question: Is your TSQLQuery bound to a SQLite-Table (via SQLite3Connection and/or DBGrid or whatever)?

[Zvoni]

Hi

"CopyFromDataset is a procedure used to copy field definitions and optional record data for the dataset specified in the Dataset argument."

Hmmm... I thought that only pertains to 'TMemDataset'?!?
The /copy-method/ is kinda crude and it certainly doesn't support blobs.
Regards Benny

How do you figure it doesn't support Blobs?
https://www.freepascal.org/docs-html/fcl/bufdataset/tcustombufdataset.copyfromdataset.html

Look into the Source-Code.
It certainly supports Blobs

Code: Pascal  [Select][+][-]

1. procedure TCustomBufDataset.CopyFromDataset(DataSet: TDataSet; CopyData: Boolean);
2.  
3. Const
4.   UseStreams = ftBlobTypes;                                                      
5. //........................
6.                  ftBCD,
7.                  ftFmtBCD   : F1.AsBCD:=F2.AsBCD;
8.             else
9.               if (F1.DataType in UseStreams) then
10.                 begin
11.                 S.Clear;
12.                 TBlobField(F2).SaveToStream(S);
13.                 S.Position:=0;
14.                 TBlobField(F1).LoadFromStream(S);
15.                 end
16.               else  
17.                 F1.AsString:=F2.AsString;                  

[egsuh]

 

   Here, case statement is not necessary.

            SQLQuery.ParamByName(fieldName).Asstring := f.AsString;

    will suffice

How do you figure that?
AsString with Params will add automatically Single-Quotes, which you don't want for Integers and floats

Simply, following works without any problem. Well, I tested with Firebird.

Code: Pascal  [Select][+][-]

1. procedure TForm1.Button1Click(Sender: TObject);
2. begin
3.    with sqlquery1 do begin
4.       SQL.text := 'insert into test values (:f0, :f1, :f2, :f3, :f4)';
5.       // field types:  integer, varchar(40), date, float, blob(memo)
6.  
7.       transaction.Active:= true;
8.  
9.       Params[0].AsString := '99991';
10.       Params[1].AsString := 'this is code 99991';
11.       Params[2].AsString := '2025-2-25';
12.       Params[3].AsString := '9876.543211';
13.       Params[4].AsString := 'A memo'#13#10'text';
14.  
15.       execsql;
16.  
17.       Params[0].AsString := '99992';
18.       Params[1].AsString := 'this is code 99992';
19.       Params[2].AsString := '2025-2-26';
20.       Params[3].AsString := '54678.543211';
21.       Params[4].AsString := 'Second memo'#13#10'text 2';
22.  
23.       execsql;
24.  
25.       (Transaction as TSQLTransaction).commit;
26.    end;
27.  
28.    with SQLQuery2 do begin
29.       Close;
30.       Transaction.Active := True;
31.       Open;
32.       (Transaction as TSQLTransaction).RollbackRetaining;
33.    end;
34. end;
35.  

[Zvoni]

   Here, case statement is not necessary.

            SQLQuery.ParamByName(fieldName).Asstring := f.AsString;

    will suffice

How do you figure that?
AsString with Params will add automatically Single-Quotes, which you don't want for Integers and floats

Simply, following works without any problem. Well, I tested with Firebird.

You are relying on implicit type-conversion, which is...... bad practice

[egsuh]

You are relying on implicit type-conversion, which is...... bad practice

Could be true, but in the case of database fields, the target types are clear.

[Zvoni]

You are relying on implicit type-conversion, which is...... bad practice

Could be true, but in the case of database fields, the target types are clear.

And that's exactly the problem: You're used to it working from Firebird.
You switch to sqlite, and it blows up in your face.

In SQLite you define a Field with Type integer, and you'll get no Error when doing
INSERT INTO MyTable(MyInt) VALUES('SomeText');

Though there is a way to force SQlite to complain.....

It is not good to rely/get used to implicit conversion.
It's a ticking timebomb

[CharlyTango]

just my 2 cents

Data migration is and was always a pain in the ass. I always tried to avoid to do this but i seldom succeeded ;-)
My advice: if there is another way to migrate Paradox Data to SQLite than coding yourself, find one.
Except you do it for other reasons than the mere success