• Registered: 2014-09-06
  • Posts: 805

Re: Solution for transferring records to new database

teco049 wrote:

You are using databases with references to other tables.


The export is catching the table names from the master table.


For export this is fine, because you can export table contant in any order without side effects - in theory.


In practise you must import records in a database with references between 'Master' and 'Slave' Tables in a strict top-down order.


This means first the origin tables where you later reference to and then the tables with the records who are using the - I like to call it 'Master' tables.


The script has the problem that this master-slave or top-down order with respecting the table references is not taken into account.


So the export from this script can not be imported. You would need to manual delete all references, import the records and then restablish the references. Alternative you need to change the export script from automatic catching the tables to a manual preset. In this preset you have to take into account the top down structure of your database.


Many work.


One of the reasons why I directly change the database structure before the main software starts.


A common problem with databasees who uses table references.


In other words: you have reach the limits of MVD. Without a manual script for the table updates it will be hard to work.


If you will not bear the costs of RAD Studio, take a look into Lazarus-IDE or Pilotlogic Code Typhoon. Both are Free Pascal with is mostly similar to the definition of Delphi 7, but with Unicode by default.


Dimtry should realy update MVD with a internal update process for database update in a running system.



I will take your and domebils  advise for  RAD Studio. I have already Lazarus installed in my PC, but the forms are making my life difficult.

  • k245
  • k245
  • Member
  • Offline
  • From: Deep
  • Registered: 2017-10-12
  • Posts: 1,536

Re: Solution for transferring records to new database

Switching to RAD studio will not solve the issues with import-export when changing the database structure. My work experience shows that there is no 100% solution to automate this process, you always have to make a structure change script manually. Although, as I wrote earlier, if you log changes to the structure, then this protocol can be used to correct. As for the integrity control, this is solved by temporarily disabling this control while the data is being loaded. ( PRAGMA foreign_keys = off )

Визуальное программирование: блог и телеграм-канал.

k245's Website

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 894

Re: Solution for transferring records to new database

Yes, this will help you - ( PRAGMA foreign_keys = ... ).
But that's only half of your problem.
The second half problem is a check when importing duplicate rows.
And it will be associated with the same fields and the value NULL.

  • Registered: 2014-09-06
  • Posts: 805

Re: Solution for transferring records to new database

( PRAGMA foreign_keys = ... ).

I don't understand what  I should do with that command.

  • k245
  • k245
  • Member
  • Offline
  • From: Deep
  • Registered: 2017-10-12
  • Posts: 1,536

Re: Solution for transferring records to new database

v_pozidis wrote:

( PRAGMA foreign_keys = ... ).

I don't understand what  I should do with that command.

Disable foreign key control before starting the import. This will allow you to add links to other entries that do not yet exist. That is, the order in which the tables are loaded will not matter.

SQLExecute('PRAGMA foreign_keys = off ');

And after all the records are loaded, the control must be turned on.

SQLExecute('PRAGMA foreign_keys = on ');
Визуальное программирование: блог и телеграм-канал.

k245's Website

  • Registered: 2014-09-06
  • Posts: 805

Re: Solution for transferring records to new database

Hi all. According to the example import export project which does not include the foreign keys of the database,is there a way to include the foreign keys in the project?, so the while database could be export and import in and from a csv file

  • Registered: 2014-09-06
  • Posts: 805

Re: Solution for transferring records to new database

k245 wrote:
v_pozidis wrote:

( PRAGMA foreign_keys = ... ).

I don't understand what  I should do with that command.

Disable foreign key control before starting the import. This will allow you to add links to other entries that do not yet exist. That is, the order in which the tables are loaded will not matter.

SQLExecute('PRAGMA foreign_keys = off ');

And after all the records are loaded, the control must be turned on.

SQLExecute('PRAGMA foreign_keys = on ');

I did include it in my project but I get the same error

  • Registered: 2017-02-01
  • Posts: 106

Re: Solution for transferring records to new database

SQLExecute('PRAGMA foreign_keys = off ');

Try to place it here:


procedure Form1_bImport_OnClick (Sender: TObject; var Cancel: boolean);
begin
    OpenDialog := TOpenDialog.Create(Form1);   // Setup dialog to select the csv file to import. This should be one that was created by the export procedure
  OpenDialog.DefaultExt := 'csv';
  OpenDialog.Filter := 'CSV|*.csv';   // Only allow csv files
  OpenDialog.Options := ofOverwritePrompt+ofHideReadOnly+ofEnableSizing;
  Dups := 0;
  if OpenDialog.Execute then
   begin


         SQLExecute('PRAGMA foreign_keys = off ');


     ProgressBar := TProgressBar.Create(Form1);
     ProgressBar.Parent := Form1.pnProgressBar;
     ProgressBar.Width := Form1.pnProgressBar.Width;
     ProgressBar.Height := Form1.pnProgressBar.Height;
     ProgressBar.Position := 0;
     ProgressBar.Max := 10;
     Form1.pnProgressBar.Visible := True;
     Form1.lblImpExp.Caption := 'Importing Database';
     application.processmessages;
     ImportToSl(OpenDialog.FileName);  // Run Import procedure - Pass the csv filename to the procedure
     Form1.TableGrid1.dbUpdate;
     Form1.tgCompanies.dbUpdate;
     Form1.pnProgressBar.Visible := False;
     Form1.lblImpExp.Caption := '';
     ProgressBar.Free;
     ShowMessage('Import Finished.'+ '      Duplicates Not Imported: ' + IntToStr(Dups));


SQLExecute('PRAGMA foreign_keys = on ');


   end;
  OpenDialog.Free;
end;

Lines are added in bold



Hi all. According to the example import export project which does not include the foreign keys of the database,is there a way to include the foreign keys in the project?, so the while database could be export and import in and from a csv file

Foreign keys are generated by the database engine when you add/edit the table strucuture to keep the internal data structure intact and with integrity. You can not export them. They exist "virutal" as part of the internal structure information and internal check processing of SQL commands. They are enforced by the database engine. They are a kind of ruleset and not any kind of records.

  • Registered: 2014-09-06
  • Posts: 805

Re: Solution for transferring records to new database

Unfortunately it crashed with the same error.






teco049 wrote:
SQLExecute('PRAGMA foreign_keys = off ');

Try to place it here:


procedure Form1_bImport_OnClick (Sender: TObject; var Cancel: boolean);
begin
    OpenDialog := TOpenDialog.Create(Form1);   // Setup dialog to select the csv file to import. This should be one that was created by the export procedure
  OpenDialog.DefaultExt := 'csv';
  OpenDialog.Filter := 'CSV|*.csv';   // Only allow csv files
  OpenDialog.Options := ofOverwritePrompt+ofHideReadOnly+ofEnableSizing;
  Dups := 0;
  if OpenDialog.Execute then
   begin


         SQLExecute('PRAGMA foreign_keys = off ');


     ProgressBar := TProgressBar.Create(Form1);
     ProgressBar.Parent := Form1.pnProgressBar;
     ProgressBar.Width := Form1.pnProgressBar.Width;
     ProgressBar.Height := Form1.pnProgressBar.Height;
     ProgressBar.Position := 0;
     ProgressBar.Max := 10;
     Form1.pnProgressBar.Visible := True;
     Form1.lblImpExp.Caption := 'Importing Database';
     application.processmessages;
     ImportToSl(OpenDialog.FileName);  // Run Import procedure - Pass the csv filename to the procedure
     Form1.TableGrid1.dbUpdate;
     Form1.tgCompanies.dbUpdate;
     Form1.pnProgressBar.Visible := False;
     Form1.lblImpExp.Caption := '';
     ProgressBar.Free;
     ShowMessage('Import Finished.'+ '      Duplicates Not Imported: ' + IntToStr(Dups));


SQLExecute('PRAGMA foreign_keys = on ');


   end;
  OpenDialog.Free;
end;

Lines are added in bold



Hi all. According to the example import export project which does not include the foreign keys of the database,is there a way to include the foreign keys in the project?, so the while database could be export and import in and from a csv file

Foreign keys are generated by the database engine when you add/edit the table strucuture to keep the internal data structure intact and with integrity. You can not export them. They exist "virutal" as part of the internal structure information and internal check processing of SQL commands. They are enforced by the database engine. They are a kind of ruleset and not any kind of records.