I have update my database with more tables and new fields in old tables. I understand I can use this SQL command to compare the two database and it will create a SQL file I can use to update the database.

COMPARE DATABASE NewDatabase
TO OldDatabase

The problem I can't find the SQL script that should be created.

I'm understanding this right, can any one help me ou?
Thanks.

On 1/27/2017 7:12 PM, KimHJ wrote:
> I have update my database with more tables and new fields in old tables. I understand I can use this SQL command to compare the two database and it will create a SQL file I can use to update the database.
>
> COMPARE DATABASE NewDatabase
> TO OldDatabase
>
> The problem I can't find the SQL script that should be created.
>
> I'm understanding this right, can any one help me ou?

See

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=COMPARE_DATABASE

Raul

Raul wrote:
>See

>http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=COMPARE_DATABASE

Thanks Raul,

I looked at that before I posted and I still can't find the created files.

Here is what I read:
These statements are generated into the SchemaDifference table in the system Information schema of the source database.

I looked in the source folder and I only see my own tables. I don't see any tables called: SchemaDifference and I have no idea what this is: system Information schema

Kim

KimHJ


>I looked in the source folder and I only see my own tables. I don't see any tables called: SchemaDifference and I have no idea what this is: system Information schema

The easy way is to go into EDBManager, open your database, do the comparison then type the following

select * from information.schemadifference

you#ll see the comparison listed there and you can copy the AlterSQL and run it

You can also do the same in an application.

Roy Lambert

Also Kim,

If you are working in EDBManager, remember that you can generate the script by using built in tools in the utility:

"Reverse Engineer Database"
"Upgrade"
and in "Upgrade Options" pick the name of the comparison database.

This process includes a tab for "Output" that give a few extra options.

... apologies if you know this already?

Roy Lambert wrote:

>The easy way is to go into EDBManager, open your database, do the comparison then type the following

select * from information.schemadifference

you#ll see the comparison listed there and you can copy the AlterSQL and run it.

Thanks Roy I will try that.
Kim

Adam Brett wrote:

>"Reverse Engineer Database"
"Upgrade"
and in "Upgrade Options" pick the name of the comparison database.

This process includes a tab for "Output" that give a few extra options.

... apologies if you know this already?

Thanks, I didn't know.
I have been working with ADS for the last 15 years, so there is a learning curve to EDB.
Kim

I found that it works this way.

COMPARE DATABASE OldDatabase
TO NewDatabase

and the select * from information.schemadifference have to be executed on the OldDatabase.

Thanks.
Kim

Kim,

<< I found that it works this way.

COMPARE DATABASE OldDatabase
TO NewDatabase

and the select * from information.schemadifference have to be executed on the OldDatabase. >>

Yep, that is correct.  The EDB Manager just smooths this out a bit for you.  However, please note that there is a TEDBReverseEngineer component available with the ElevateDB Additional Software and Utilities that will do everything that the EDB Manager does, but is something that you can call directly from your code.  You can find it in the:

\utilcomps

directory under the main installation directory, in the edbutilcomps.pas unit.

This is how the EDB Manager calls it:

procedure TDatabaseObject.Custom7;
var
  TempReverseEngineer: TEDBReverseEngineer;
  TempDatabase: TEDBDatabase;
begin
  if (not Opened) then
     Open;
  ReverseEngineerDialog:=TReverseEngineerDialog.Create(nil);
  try
     with ReverseEngineerDialog do
        begin
        CurObject:=Self;
        Caption:=Custom7Caption;
        {$WARN SYMBOL_PLATFORM OFF}
        FileNameEdit.Text:=IncludeTrailingBackslash(MainForm.SQLPath)+
                           FDatabase.DatabaseName+'.SQL';
        {$WARN SYMBOL_PLATFORM ON}
        Setup;
        if (ShowModal=mrOk) then
           begin
           with MainForm do
              begin
              TempReverseEngineer:=TEDBReverseEngineer.Create(nil);
              try
                 with TempReverseEngineer do
                    begin
                    if CreateRadioButton.Checked then
                       ReverseType:=rtCreate
                    else if UpgradeRadioButton.Checked then
                       ReverseType:=rtUpgrade
                    else if DropRadioButton.Checked then
                       ReverseType:=rtDrop;
                    if (ReverseType=rtUpgrade) then
                       TempDatabase:=TEDBDatabase.Create(nil);
                    try
                       if (ReverseType=rtUpgrade) then
                          begin
                          with TempDatabase do
                             begin
                             SessionName:=SessionObject.Name;
                             DatabaseName:=REVERSE_DB_NAME;
                             Database:=TargetDatabaseComboBox.Text;
                             end;
                          SourceDatabase:=FDatabase;
                          TargetDatabase:=TempDatabase;
                          end
                       else
                          SourceDatabase:=FDatabase;
                       if ScriptRadioButton.Checked then
                          Options:=[reCreateScript]
                       else if ProcedureRadioButton.Checked then
                          Options:=[reCreateProcedure];
                       if IncludeRowsCheckBox.Checked then
                          Options:=(Options+[reIncludeRows]);
                       if IncludeProgressCheckBox.Checked then
                          Options:=(Options+[reIncludeProgress]);
                       if (StatementTerminatorEdit.Text <> '') then
                          StatementTerminator:=String(StatementTerminatorEdit.Text)[1]
                       else
                          StatementTerminator:=Char(' ');
                       OnProgress:=MainForm.QueryProgress;
                       OnStatusMessage:=MainForm.QueryStatusMessage;
                       ShowProgressBar;
                       try
                          Execute;
                          if WindowRadioButton.Checked then
                             begin
                             if ScriptRadioButton.Checked then
                                SetupNewScriptWindow(ResultSQL.Text)
                             else if GenericScriptRadioButton.Checked or
                                     ProcedureRadioButton.Checked then
                                SetupNewSQLWindow(ResultSQL.Text);
                             end
                          else
                             SaveStringsToFile(FileNameEdit.Text,ResultSQL,
                                               (SaveSQLDialog.FilterIndex-1));
                       finally
                          HideProgressBar;
                       end;
                    finally
                       if (ReverseType=rtUpgrade) then
                          FreeAndNil(TempDatabase);
                    end;
                    end;
              finally
                 FreeAndNil(TempReverseEngineer);
              end;
              end;
           end;
        end;
  finally
     ReverseEngineerDialog.Release;
  end;
end;

The source for this is provided in the:

\utils\edbmgr\win32\source

directory under the main installation directory, in the main.pas unit for the edbmgr.dpr project.

In addition, the TEDBReverseEngineer component contains a lot of nice little public methods for generating SQL for various database objects without needing to directly perform the querying of the system information tables.

Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

>Yep, that is correct.  The EDB Manager just smooths this out a bit for you.  However, please note that there is a TEDBReverseEngineer component available with the ElevateDB Additional Software and Utilities that will do everything that the EDB Manager does, but is something that you can call directly from your code.  You can find it in the:

Thanks,
Tim