Consolidating Databases

Forum related to MySQL

Moderators: gto, cipto_kh, EgonHugeist

Baldur2630
Fresh Boarder
Fresh Boarder
Posts: 16
Joined: 03.04.2013, 11:45
Location: Belgium

Consolidating Databases

Postby Baldur2630 » 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 : -

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;

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?

miab3
Zeos Test Team
Zeos Test Team
Posts: 922
Joined: 11.05.2012, 12:32
Location: Poland

Re: Consolidating Databases

Postby miab3 » 16.02.2016, 13:50

@Baldur2630,

You should do something like this:

Code: Select all

  ZQuery1.SQL.Text := 'select * from Movies';
  ZQueryOld.SQL.Text := 'select * from MoviesOld';
  ZQuery1.Open;
  ZQueryOld.Open;
  // ZQueryOld.First;
  while not ZQueryOld.Eof do
  begin
    OldId := ZQueryOld.FieldByName('IDOld').AsInteger;
    ...
    OldDVD := ZQueryOld.FieldByName('DVDNoOld').AsInteger;
    ...
 //   begin
      ZQuery1.Append;
      ZQuery1.FieldByName('ID').AsInteger := OldID;
      ZQuery1.FieldByName('DVDNo').AsInteger := OldDVD;
      ZQuery1.FieldByName('Title').Value := OldTitle;
      ZQuery1.FieldByName('Actor1').Value := OldActor;
      ZQuery1.FieldByName('Actor2').Value := '';
      ZQuery1.FieldByName('Director').Value := OldDirector;
      ZQuery1.FieldByName('Genre').Value := OldGenre;
      ZQuery1.FieldByName('Comment').Value := OldComment;
      ZQuery1.FieldByName('ID').AsInteger := Season;
      ZQuery1.Post;
 //   end;
    ZQueryOld.Next
  end;
 

Longtext, varchar(255) and text:
http://dev.mysql.com/doc/refman/5.7/en/ ... rview.html

Michal

Baldur2630
Fresh Boarder
Fresh Boarder
Posts: 16
Joined: 03.04.2013, 11:45
Location: Belgium

Re: Consolidating Databases

Postby Baldur2630 » 17.02.2016, 06:30

Fantastic! That was all that I wanted (I thought), but now the next problem rears its head.

I have two TDatasource one omovie the other nmovie
I have two TZConnection one oldmov the other newmov
I have two TZQuery one Omovquery the other Nmovquery

Omovie Dataset is Omovquery
Nmovie Dataset is Nmovquery

If I set Connected to True in the Object Inspector for oldmov, I get the login prompt and this works without error, BUT if i try to select Database, I am not connected to a database on my server. I just get a directory listing of a local folder. What am I missing?

miab3
Zeos Test Team
Zeos Test Team
Posts: 922
Joined: 11.05.2012, 12:32
Location: Poland

Re: Consolidating Databases

Postby miab3 » 17.02.2016, 11:01

@Baldur2630,

Enter the name (Database) manually.
You can find them in Catalog.

Michal

Baldur2630
Fresh Boarder
Fresh Boarder
Posts: 16
Joined: 03.04.2013, 11:45
Location: Belgium

Re: Consolidating Databases

Postby Baldur2630 » 17.02.2016, 15:24

OK, I managed to get all the connections working. I can login, if I drop a dbgrid onto the form, I can see the table contents, BUT . . .

I get an error message that the field ID doesn't exist when I try to execute the query.

I think I'm getting too old for this!

Baldur2630
Fresh Boarder
Fresh Boarder
Posts: 16
Joined: 03.04.2013, 11:45
Location: Belgium

Re: Consolidating Databases

Postby Baldur2630 » 17.02.2016, 18:14

OK, I found the problem. Worked perfectly.

Thanks a stack for all the help.


Return to “MySQL”

Who is online

Users browsing this forum: No registered users and 1 guest