Compare DB schemas problem - v16

[former_member329524]

Hello, all

The otherwise excellent feature of "Compare DB schemas" seems to have an issue (checked on the latest build as well):

Procedures, functions and triggers, which have a nested "if .. end if" condition are falsly flagged as different.

for example, the following code is always falsely compared, even if ran in ISQL exactly as it is:

//------------------

if x = 1 then

     if y = 1 then

          set z = 0;

     end if;

end if;

//-------------

The problem appears to be that when being run in ISQL the resulting code almost always becomes:

//-----------------

if x = 1 then

     if y = 1 then

          set z = 0;

     end if end if;

//---------------

The only workaround I could find so far is to add a comment between the 2 "end if"s.

I hope there can be something better

Thank you

Arcady Abramov

[richard_jones5]

Thanks for reporting this problem.

The Compare Database Schema window compares both the unparsed procedure definition and its preserved source when determining whether two procedures are identical.

Normally, the CREATE OR REPLACE PROCEDURE statement that it generates to make the procedures match results in the identical text being inserted into the catalog for the unparsed procedure definition. However, in rare cases such as this one, the unparsed definition differs from what was specified. The procedure definitions are semantically identical, but differ slightly in semi-colon placement and white space.

While this is rare, I agree that it is not ideal. I think the solution here would be to add an option to ignore the unparsed procedure definitions if the preserved sources are identical.

[jeff_albion]

Hi Arcady,

Which build / SP of 16.0 are you trying? I cannot seem to reproduce this situation from just your description (16.0.2052, SP 29):

-- nestedif.sql
create function foo()
returns integer
begin
    declare x integer;
    declare y integer;
    declare z integer;
    if x = 1 then
         if y = 1 then
              set z = 0;
         end if;
    end if;
    return z;
end;

dbinit db1.db
dbinit db2.db
dbisql -c "uid=dba;pwd=sql;dbf=db1.db" nestedif.sql
dbisql -c "uid=dba;pwd=sql;dbf=db2.db" nestedif.sql
scjview


Tools > SQL Anywhere 16 > Compare Database Schemas...

Can you perhaps elaborate on what you're trying exactly and what you're seeing?

Regards,

Jeff Albion

SAP Active Global Support

[former_member329524]

Hi, Jeff

This is how you reproduce it.

1. Create a new function in DB 1 with similar code, like you wrote. Create and save the function in Sybase central, not in ISQL.

2. Do a compare schema with DB2. An sql script will be created with the new function.

3. Run the script on DB2. The DBs now supposed to be the same.

4. Do compare schemas again. The function will still appear as if it has different code.

BTW, I have the latest build of SQL 16 - 2052

Regards,

Arcady

[jeff_albion]

Hi Arcady,

Thanks for the hints on the reproducible steps - I can also see the problem now:

Note that the script you had run from the schema wizard in Interactive SQL isn't the exact same definition as the original as the wizard has dropped the semi-colon on the embedded if:

Text version:

create function "DBA"."bar"()
returns integer
begin
  declare "x" integer;
  declare "y" integer;
  declare "z" integer;
  if "x" = 1 then
    if "y" = 1 then
      set "z" = 0
    end if
  end if;
  return "z"
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "DBA"."bar" IS
{create FUNCTION DBA."bar"()
returns integer
begin
    declare x integer;
    declare y integer;
    declare z integer;
    if x = 1 then
         if y = 1 then
              set z = 0;
         end if;
    end if;
    return z;
end
}
go

If I replace the semi-colon in the definition in Interactive SQL, I go back to the functions being equivalent:

create or replace function "DBA"."bar"()
returns integer
begin
  declare "x" integer;
  declare "y" integer;
  declare "z" integer;
  if "x" = 1 then
    if "y" = 1 then
      set "z" = 0
    end if;
  end if;
  return "z"
end
go

I have opened CR #779145 to investigate further - thank you for the bug report.

Jeff Albion

SAP Active Global Support