Consolidating Databases

[Baldur2630]

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]

@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]

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]

@Baldur2630,

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

Michal

[Baldur2630]

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]

OK, I found the problem. Worked perfectly.

Thanks a stack for all the help.