on 02-08-2015 8:38 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
User | Count |
---|---|
91 | |
10 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.