Topic: Solution for transferring records to new database

I have a project that from time to time I upgrade it, not only the software but also the database with new columns and tables, (with new relations between them). So my question is how can I transfer the existing records from the database to the new, without making the life of my customers difficult.
The only solution that I have is to create a small program all the time I make a change in the database that makes the job. The progress is simply I copy the "old" database in the folder of the program,  that add the new tables and columns and transferring the data's and run the program using Sqlite script (my sqlite script is very poor) . After that I copy and paste the "new" database to the new upgraded software folder.
To avoid all this does anyone know a simply way. Keep in mind that it will be for my customers which they do not have an experience of programming

Re: Solution for transferring records to new database

переносятся по тем же самым параметрам?

Re: Solution for transferring records to new database

Please in english...thank you

Re: Solution for transferring records to new database

Generally speaking, you could export the current database in CSV format and then import into the new database. If the new fields are to the right of the old fields (so to speak), you should be able to just import the records so long as the old field names have not been changed.

The Export and Import function provided in MVD under options should do the trick.

On a clear disk you can seek forever

5 (edited by v_pozidis 2022-04-26 12:34:19)

Re: Solution for transferring records to new database

It's not a good idea, because it export separately all the tables and when importing you must do it again one by one for all the tables and giving the headers manually the name of the columns.
Except if there is another way.





CDB wrote:

Generally speaking, you could export the current database in CSV format and then import into the new database. If the new fields are to the right of the old fields (so to speak), you should be able to just import the records so long as the old field names have not been changed.

The Export and Import function provided in MVD under options should do the trick.

Re: Solution for transferring records to new database

v_pozidis wrote:

I have a project that from time to time I upgrade it, not only the software but also the database with new columns and tables, (with new relations between them). So my question is how can I transfer the existing records from the database to the new, without making the life of my customers difficult.
The only solution that I have is to create a small program all the time I make a change in the database that makes the job. The progress is simply I copy the "old" database in the folder of the program,  that add the new tables and columns and transferring the data's and run the program using Sqlite script (my sqlite script is very poor) . After that I copy and paste the "new" database to the new upgraded software folder.
To avoid all this does anyone know a simply way. Keep in mind that it will be for my customers which they do not have an experience of programming

Unfortunately, in MVDB there is no way to record structure changes and save them as a SQL-script, so they can be used to automatically update the database structure. Therefore, your method is the only true one, I also use it.


Perhaps there are third-party utilities that compare two databases and generate a script that matches the first structure with the second. If you find it, please let me know smile

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

7 (edited by domebil 2022-04-26 13:59:13)

Re: Solution for transferring records to new database

try this

Post's attachments

Attachment icon Import-Export All Tables.zip 368.71 kb, 272 downloads since 2022-04-26 

Domebil

Re: Solution for transferring records to new database

domebil wrote:

try this

Works perfect!!!!! thank you.
So I must convert it for my project.

Re: Solution for transferring records to new database

Unfortunately, in MVDB there is no way to record structure changes and save them as a SQL-script, so they can be used to automatically update the database structure. Therefore, your method is the only true one, I also use it.


If you can generate the sql statements for database alternation by yourself, you can add this in the scriptfile at the very end of the file. After all procedures/functions inside the final    begin    end    area.


This area is handled before the database is structural checked by MVD. After upgrading/changing you must restart your application to use the updated sql database.


This is the way I am doing it since many years without problem. Some additional infractructure in the dabase is required to catch the table information before altering.


Also I use this way to use the replaced sqlite.dll. My version has encryption included. Search the forum for more details on this and an example from Dimtry.   smile

Re: Solution for transferring records to new database

teco049 wrote:

Unfortunately, in MVDB there is no way to record structure changes and save them as a SQL-script, so they can be used to automatically update the database structure. Therefore, your method is the only true one, I also use it.


If you can generate the sql statements for database alternation by yourself, you can add this in the scriptfile at the very end of the file. After all procedures/functions inside the final    begin    end    area.


This area is handled before the database is structural checked by MVD. After upgrading/changing you must restart your application to use the updated sql database.


This is the way I am doing it since many years without problem. Some additional infractructure in the dabase is required to catch the table information before altering.


Also I use this way to use the replaced sqlite.dll. My version has encryption included. Search the forum for more details on this and an example from Dimtry.   smile

All this is true, but you have to do double work: first change the structure of the database using the MVDB visual tools, and then write a SQL-script that will perform the same actions on the old version of the database for the end user. As a result, annoying mistakes happen. But if structure changes were logged in the format of SQL-commands, then this log could be used without fear that something was forgotten or missed.

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

Re: Solution for transferring records to new database

k245 wrote:

All this is true, but you have to do double work: first change the structure of the database using the MVDB visual tools, and then write a SQL-script that will perform the same actions on the old version of the database for the end user. As a result, annoying mistakes happen. But if structure changes were logged in the format of SQL-commands, then this log could be used without fear that something was forgotten or missed.


That's true. But MVD was never anything else for me as a cheaper and stripped tool instead of Embarcadero RAD Studio.


But now Lazarus-IDE and Pilotlogic Code Typhoon have increased their functions and stability. Personal there is not much left to use MVD. Only sometimes to create fast prototypes for discussing a new project.


The company will not pay for a Enterprise License and was looking for something similar but cheaper. Now both Free Pascal IDE's have make the run.


For me personal Dimtry was to long away to keep MVD up to date and competitive with the other IDE's for Delphi/Free Pascal. Maybe there are some users left who like to create a application with less coding, but for people like me, the others are more futureproof and more flexible as MVD.


I am not so often here as in the past. It is for me a kind of slow farewell to MVD and journey to new adventures.

Re: Solution for transferring records to new database

teco049 wrote:

That's true. But MVD was never anything else for me as a cheaper and stripped tool instead of Embarcadero RAD Studio.

Cutting is sometimes beneficial: it increases the ease of use and the efficiency of solving highly specialized tasks. This is how I position the MVDB. I have been developing programs in Delphi for many years, I may come back to it again, but so far my best tool is MVDB.

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

Re: Solution for transferring records to new database

k245 wrote:

so far my best tool is MVDB.

Thanks for this comment cool


Also to v_pozidis for asking this question, because I have often wondered how others do this too.

"Energy and persistence conquer all things."

14 (edited by v_pozidis 2022-04-29 19:12:00)

Re: Solution for transferring records to new database

Hi there, my personal opinion is that my visual database is a great tool to create small projects and especially for someone who don't have the knowledge about SQL like me. Of course for better and more professional projects there are other languages with more tools. I still use my mvbd because I learned Pascal at school and I really love this language. So about the import export program which is a great project I found that the script crashes if I use it in my project and it shows me an error about  foreign key.the error message FOREIGN KEY CONSTREINT FAILD. Because I update my projects from time to time with new ideas so I need a script or a tool that it updates the database with the new columns it's transferring the records to the database. So I will ask again if there is a way to check the table if not exist to add it with the columns (also which include foreign keys) and if there is a table and check if not exists columns and add them (also if the columns are foreign keys).
This moment I use every time I update my project a small program to do that work adding the columns and transferring the records.

Re: Solution for transferring records to new database

I get en error when trying to import . The error message is the following FOREIGN KEY CONSTREINT FAILD. Please help how to resolve this to continue???

Re: Solution for transferring records to new database

upload your project to let you see what's wrong

Domebil

17 (edited by v_pozidis 2022-05-03 17:36:06)

Re: Solution for transferring records to new database

I have convert my program to your Import-Export example ,added only the database and not the forms, As you can see it export the database , but when I try to Import it crash and give the error message(FOREIGN KEY CONSTREINT FAILD) after some seconds after some seconds the progress bar finished
This is my software. It is a program that will transfer all the tables and records in another updated program using the Excel (csv) and not copy the database
So try to export the database and then import it.

Post's attachments

Attachment icon 1ImportExport.zip 384.22 kb, 179 downloads since 2022-05-03 

Re: Solution for transferring records to new database

The script works but there are too many errors in the database, you need to fix the database

Domebil

Re: Solution for transferring records to new database

Can you give me an example of what error should I looking for?

20 (edited by domebil 2022-05-04 05:19:53)

Re: Solution for transferring records to new database

See in the image the table is repeated

Post's attachments

Attachment icon database.png 14.63 kb, 65 downloads since 2022-05-04 

Domebil

Re: Solution for transferring records to new database

unfortunately the database and the tables you can only know how to organize them according to what you want to do

Domebil

Re: Solution for transferring records to new database

domebil wrote:

See in the image the table is repeated


But my database is correct with those two related records. (I need them because I call from one table two different Names for customers)
Any idea?

Re: Solution for transferring records to new database

Having such complex databases I recommend using Rad Studio

Domebil

Re: Solution for transferring records to new database

domebil wrote:

Having such complex databases I recommend using Rad Studio

I will give it a look.
Thank's

Re: Solution for transferring records to new database

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.