Consolidating Databases
Posted: 15.02.2016, 18:03
I'm using Delphi XE3 and the latest Zeoslib. Here's my problem : -
I retired 6 years ago and haven't used Delphi in anger since then, so I'm a bit rusty (this year's understatement!)
I have three MySQL Databases that have been written by various members of the family over the years. One holds TV-Series one holds Movies and the other is the oldest and it's a real mess and it has both. They are all using different string types and lengths integers, bytes, longint, longtext, you name it.
I have created a new database which caters for all the items. so what I need to do is to open one of the old tables, read each record in turn, process the Fields and the Datatypes to match my new database and Append them to the new database with the same DVD numbers (DVD is NOT a Key or even unique as a single DVD can contain 5 or 6 movies) Each Database has a Unique Record ID but they are all different data types, so every field needs to be converted to my new 'standard format and then appended with the same DVD number to the new database.
I've written the Code to open all three databases. I've written the code to do an append to the new database. Where I'm stuck, is how to read each record one by one, process it, write it back to the new database. It's only the sequential READ that I'm stuck with. I'm not looking to normalize or do anything fancy, just consolidate everything into ONE database instead of three.
I've got this for updating : -
The other thing I'm not 100% sure of is the Comment. In one db it's a longtext the other a varchar(255) and the third text. Will ANSIString cover them all?
I retired 6 years ago and haven't used Delphi in anger since then, so I'm a bit rusty (this year's understatement!)
I have three MySQL Databases that have been written by various members of the family over the years. One holds TV-Series one holds Movies and the other is the oldest and it's a real mess and it has both. They are all using different string types and lengths integers, bytes, longint, longtext, you name it.
I have created a new database which caters for all the items. so what I need to do is to open one of the old tables, read each record in turn, process the Fields and the Datatypes to match my new database and Append them to the new database with the same DVD numbers (DVD is NOT a Key or even unique as a single DVD can contain 5 or 6 movies) Each Database has a Unique Record ID but they are all different data types, so every field needs to be converted to my new 'standard format and then appended with the same DVD number to the new database.
I've written the Code to open all three databases. I've written the code to do an append to the new database. Where I'm stuck, is how to read each record one by one, process it, write it back to the new database. It's only the sequential READ that I'm stuck with. I'm not looking to normalize or do anything fancy, just consolidate everything into ONE database instead of three.
I've got this for updating : -
Code: Select all
procedure updatedb;
var
Csql : string;
begin
Form1.ZQuery1.Active;
Form1.ZQuery1.SQL.Text := 'select * from Movies';
Form1.ZQuery1.Open;
Form1.ZQuery1.Append;
Form1.ZQuery1.FieldByName('ID').AsInteger := OldID;
Form1.ZQuery1.FieldByName('DVDNo').AsInteger := OldDVD;
Form1.ZQuery1.FieldByName('Title').Value := OldTitle;
Form1.ZQuery1.FieldByName('Actor1').Value := OldActor;
Form1.ZQuery1.FieldByName('Actor2').Value := '';
Form1.ZQuery1.FieldByName('Director').Value := OldDirector;
Form1.ZQuery1.FieldByName('Genre').Value := OldGenre;
Form1.ZQuery1.FieldByName('Comment').Value := OldComment;
Form1.ZQuery1.FieldByName('ID').AsInteger := Season;
Form1.ZQuery1.Post;
end;