Using CopyDataSet to transfer a ParadoxTable to an SQLquery
lazarus / 16-Databases

----------------------------------------------------------------------
dpap · 2025-02-24 14:16

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 · 2025-02-24 19:01

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.freepa
scal.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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
FixFieldName(AFieldName: String): String;begin  Result :=
StringReplace(AFieldName, ' ', '_', [rfReplaceAll]);  Result :=
StringReplace(Result, '(', '', [rfReplaceAll]);  Result :=
StringReplace(Result, ')', '', [rfReplaceAll]);end; procedure
CopyToSqlite3(ADataset: TDataset; AFileName: String);var  SQLiteConn:
TSQLite3Connection;  Transaction: TSQLTransaction;  SQLQuery:
TSQLQuery;  f: TField;  fieldName: String;  sql: String;  values:
String;  bm: TBookmark;begin  DeleteFile(AFileName);   { Set up
databse components }  SQLiteConn := TSQLite3Connection.Create(nil); 
SQLiteConn.DatabaseName := AFileName;  Transaction :=
TSQLTransaction.Create(SQLiteConn);  Transaction.DataBase :=
SQLiteConn;  Transaction.Action := caCommit;  SQLiteConn.Open;   {
Create database and its fields }  sql := 'CREATE TABLE "data" (';  for
f in ADataset.Fields do  begin    fieldName :=
FixFieldName(f.FieldName);    case f.DataType of      ftString: sql :=
sql + Format('"%s" VARCHAR(%d), ', [fieldName,  f.Size]);     
ftInteger: sql := sql + Format('"%s" INTEGER, ', [fieldName]);     
ftFloat: sql := sql + Format('"%s" FLOAT, ', [fieldName]);     
ftMemo: sql := sql + Format('"%s" VARCHAR, ', [fieldName]);      else
raise Exception.Create('Field type not supported here.');    end; 
end;  sql[Length(sql)-1] := ')';  SQLiteConn.ExecuteDirect(sql); 
Transaction.Commit;   { Add records as copy of dataset's records } 
sql := 'INSERT INTO "data" (';  values := 'VALUES (';  for f in
ADataset.Fields do  begin    fieldName := FixFieldName(f.FieldName); 
 sql := sql + Format('%s, ', [fieldName]);    values := values +
Format(':%s, ', [fieldName]);  end;  sql[Length(sql)-1] := ')'; 
values[Length(values)-1] := ')';  sql := sql + values;   SQLQuery :=
TSQLQuery.Create(nil);  SQLQuery.DataBase := SQLiteConn; 
SQLQuery.Transaction := Transaction;  SQLQuery.SQL.Add(sql);  bm :=
ADataset.GetBookmark;  try    ADataset.First;    while not
ADataset.EoF do    begin      for f in ADataset.Fields do      begin 
     fieldName := FixFieldName(f.FieldName);        case f.DataType
of          ftString: SQLQuery.ParamByName(fieldName).AsString :=
f.AsString;          ftInteger:
SQLQuery.ParamByName(fieldName).AsInteger := f.AsInteger;         
ftFloat: SQLQuery.ParamByName(fieldName).AsFloat := f.AsFloat;       
 ftMemo: SQLQuery.ParamByName(fieldName).Asstring := f.AsString;     
 end;      end;      SQLQuery.ExecSQL;      ADataset.Next;    end;   
ADataset.GotoBookmark(bm);  finally    ADataset.FreeBookmark(bm); 
end;   Transaction.Commit;   SQLQuery.Close;  SQLQuery.Free; 
Transaction.Free;  SQLiteConn.Free;end;

----------------------------------------------------------------------
dpap · 2025-02-24 19:14

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 · 2025-02-25 07:20

Quote from: dpap on February 24, 2025, 08:14:50 pmThank 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 · 2025-02-25 07:33

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i        
fieldName := FixFieldName(f.FieldName);        case f.DataType of    
    ftString: SQLQuery.ParamByName(fieldName).AsString := f.AsString; 
        ftInteger: SQLQuery.ParamByName(fieldName).AsInteger :=
f.AsInteger;          ftFloat: SQLQuery.ParamByName(fieldName).AsFloat
:= f.AsFloat;          ftMemo:
SQLQuery.ParamByName(fieldName).Asstring := f.AsString;        end;
Here, case statement is not necessary.

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

will suffice

----------------------------------------------------------------------
Zvoni · 2025-02-25 07:36

Quote from: egsuh on February 25, 2025, 08:33:26 amCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i        
fieldName := FixFieldName(f.FieldName);        case f.DataType of    
    ftString: SQLQuery.ParamByName(fieldName).AsString := f.AsString; 
        ftInteger: SQLQuery.ParamByName(fieldName).AsInteger :=
f.AsInteger;          ftFloat: SQLQuery.ParamByName(fieldName).AsFloat
:= f.AsFloat;          ftMemo:
SQLQuery.ParamByName(fieldName).Asstring := f.AsString;        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 · 2025-02-25 07:45

@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 · 2025-02-25 07:52

Hi
Quote"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 · 2025-02-25 07:52

Quote from: dpap on February 25, 2025, 08:45:57 am@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 · 2025-02-25 07:59

Quote from: cdbc on February 25, 2025, 08:52:31 amHi
Quote"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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TCustomBufDataset.CopyFromDataset(DataSet: TDataSet; CopyData:
Boolean); Const  UseStreams = ftBlobTypes;                           
                          //........................               
 ftBCD,                 ftFmtBCD   : F1.AsBCD:=F2.AsBCD;           
else              if (F1.DataType in UseStreams) then               
begin                S.Clear;               
TBlobField(F2).SaveToStream(S);                S.Position:=0;        
      TBlobField(F1).LoadFromStream(S);                end           
 else                  F1.AsString:=F2.AsString;

----------------------------------------------------------------------
egsuh · 2025-02-25 08:15

Quote   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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button1Click(Sender: TObject);begin   with sqlquery1 do begin 
   SQL.text := 'insert into test values (:f0, :f1, :f2, :f3, :f4)';  
  // field types:  integer, varchar(40), date, float, blob(memo)     
transaction.Active:= true;       Params[0].AsString := '99991';     
Params[1].AsString := 'this is code 99991';      Params[2].AsString :=
'2025-2-25';      Params[3].AsString := '9876.543211';     
Params[4].AsString := 'A memo'#13#10'text';       execsql;      
Params[0].AsString := '99992';      Params[1].AsString := 'this is
code 99992';      Params[2].AsString := '2025-2-26';     
Params[3].AsString := '54678.543211';      Params[4].AsString :=
'Second memo'#13#10'text 2';       execsql;       (Transaction as
TSQLTransaction).commit;   end;    with SQLQuery2 do begin     
Close;      Transaction.Active := True;      Open;      (Transaction
as TSQLTransaction).RollbackRetaining;   end;end;

----------------------------------------------------------------------
Zvoni · 2025-02-25 08:32

Quote from: egsuh on February 25, 2025, 09:15:11 am Quote   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 · 2025-02-25 08:51

QuoteYou 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 · 2025-02-25 09:11

Quote from: egsuh on February 25, 2025, 09:51:29 amQuoteYou 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 · 2025-02-26 08:48

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