Unload statement differences

[Former Member]

I have two versions of the same database (separate servers one an older version) and the results of an unload are different only by the double quotes around to column names in column constraints,view definitions etc (see below ACCOUNT_ID) in the  I cannot find where this is coming from database settings,options server settings etc

One Version with quotes

CREATE TABLE "ACCOUNT" (

    "ACCOUNT_ID"                     integer NOT NULL DEFAULT autoincrement CONSTRAINT "CKC_ACCOUNT_ID" check("ACCOUNT_ID" >= 1)

   ,"ACCOUNT_NUMBER"                 varchar(20) NOT NULL

   ,"ACCOUNT_DESCRIPTION"            varchar(100) NOT NULL

   ,CONSTRAINT "PK_ACCOUNT" PRIMARY KEY ("ACCOUNT_ID" ASC)

)

Another version without quotes

CREATE TABLE "ACCOUNT" (

    "ACCOUNT_ID"                     integer NOT NULL DEFAULT autoincrement CONSTRAINT "CKC_ACCOUNT_ID" check(ACCOUNT_ID >= 1)

   ,"ACCOUNT_NUMBER"                 varchar(20) NOT NULL

   ,"ACCOUNT_DESCRIPTION"            varchar(100) NOT NULL

   ,CONSTRAINT "PK_ACCOUNT" PRIMARY KEY ("ACCOUNT_ID" ASC)

)

I have checked quoted_identifier documentation etc etc

Any suggestions greatly appreciated

Dieter

[jack_schueler · Accepted solution]

This is a behavior change for 16.0 that appears to be missing from the documentation (i.e., I can't find it). Here is a description of the behavior change.

In 16.0 databases (newly initialized in 16.0 or upgraded to 16.0), the SQL text is stored in catalog tables unparsed with all identifiers quoted. Further, in these databases when the server parses a statement that is known to be unparsed, the non_keywords option is ignored. These changes ensure that identifiers are treated unambiguously in 16.0 and all future server versions independent of what new keywords are defined.

[JasonHinsperger]

DBUnload gets the text of the check constraint from the SYSCHECK table. If you check there in both databases, you should see a difference.

What likely happened is that the server was updated to quote identifiers in check constraints in the more recent version. Then, when your old database was rebuilt and the 'CREATE TABLE' statement was executed in the new database, the check constraint was parsed by the server and the double quotes would have been added to the column name in the check constraint before it was stored in the database.

[Former Member]

Thanks for the response.

I see the differences in the syscheck table as you predicted. However I am note sure how " the server was updated to quote identifiers in check constraints". If I add a new identical constraint, to both databases one gets the quotes and one doesn't and I cannot work out why. The database options that I thought would effect this quoted_identifier, quote_all_identifier options are the same in both databases.

The quotes are also around columns referred to in views, stored procedures etc.

The main reason for trying to work this out is because I am trying to compare schema changes between two different version of the same database and get a massive number of differences just because of the quotes.

[jeff_albion]

Hello Dieter,

Which version of SQL Anywhere are you using for these databases? What does "SELECT * FROM SYS.SYSHISTORY" show?

Regards,

Jeff Albion

SAP Active Global Support

[JasonHinsperger]

Sorry - when I said the "server was updated" I meant the actual SQL Anywhere server software.  It is the general policy that identifiers stored in the database be quoted and so the database server does this when it parses the statement for storage in the database.  This is to prevent future issues, like if an identifier becomes a SQL keyword in a future version.

There is no setting to turn this off as far as I know.

[Former Member]

Hi Jeff,

The server being used is 16.0.0.1915 for both databases.

The database with the quotes has the following results for your select statement

operation	object_id	sub_operation	version	platform	first_time	last_time	details	first_time_utc	last_time_utc
INIT	0		16.0.0.1691	W7 #7601 SP 1 X86	2013-12-13 16:00:42.000	2013-12-13 16:00:42.000		2013-12-13 06:30:42.000+00:00	2013-12-13 06:30:42.000+00:00
LAST_START	0		16.0.0.1915	W7 #7601 SP 1 X86_64	2014-06-11 08:45:04.000	2014-06-11 08:45:04.000	AT=0x20;DB=SATA;DM=ATA_____ST1000DM003-1CH1HP33;	2014-06-10 23:15:04.000+00:00	2014-06-10 23:15:04.000+00:00
START	0		16.0.0.1691	W7 #7601 SP 1 X86	2013-12-13 16:00:52.000	2014-01-08 17:00:15.000	AT=0x20;DB=ATA RAID;DM=ATA_____INTEL_SSDSA2BW160365 INTEL_SSDSA2BW160G3H____4PC1;	2013-12-13 06:30:52.000+00:00	2014-01-08 07:30:15.000+00:00
START	0		16.0.0.1324	W7 #7601 SP 1 X86	2014-01-08 17:00:15.000	2014-05-16 11:50:47.000	AT=0x20;DB=SATA Unknown;DM=ATA_____ST1000DM003-1CH1HP33;DT=REMOTE;	2014-01-08 07:30:15.000+00:00	2014-05-16 02:20:47.000+00:00
LAST_BACKUP	0		16.0.0.1324	W7 #7601 SP 1 X86	2014-03-07 13:05:15.000	2014-05-06 12:23:08.000	BU=Type:DBFO 'Imag';	2014-03-07 03:35:15.000+00:00	2014-05-06 02:53:08.000+00:00
START	0		16.0.0.1324	WXP #2600 SP 3 X86	2014-05-15 14:24:25.000	2014-05-16 10:23:16.000	AT=0x20;DB=Unknown;DM=;DT=REMOTE;	2014-05-15 04:54:25.000+00:00	2014-05-16 00:53:16.000+00:00
START	0		16.0.0.1324	W7 #7601 SP 1 X86_64	2014-05-16 11:50:47.000	2014-06-11 08:45:04.000	AT=0x20;DB=SATA;DM=ATA_____ST1000DM003-1CH1HP33;	2014-05-16 02:20:47.000+00:00	2014-06-10 23:15:04.000+00:00
START	0		16.0.0.1915	W7 #7601 SP 1 X86_64	2014-06-11 08:45:04.000	2014-06-11 08:45:04.000	AT=0x20;DB=SATA;DM=ATA_____ST1000DM003-1CH1HP33;	2014-06-10 23:15:04.000+00:00	2014-06-10 23:15:04.000+00:00

The database without the quotes has the followoing results

operation	object_id	sub_operation	version	platform	first_time	last_time	details
INIT	0		12.0.0.2483	W7 #7600  X86_64	2011-02-08 14:09:56.000	2011-02-08 14:09:56.000
LAST_START	0		16.0.0.1915	W7 #7601 SP 1 X86_64	2014-06-24 13:38:28.000	2014-06-24 13:38:28.000	AT=0x820;DB=Unknown;DM=;DT=REMOTE;
START	0		12.0.0.2483	W7 #7600  X86_64	2011-02-08 14:09:56.000	2011-02-08 14:14:32.000	AT=0x20;DB=ATA SCSI;DM=ST31000528AS____________CC38 VMware__Virtual_disk____1.0_;
START	0		12.0.0.2483	W2008R2 #7600  X86_64	2011-02-08 14:14:32.000	2014-01-06 16:34:23.000	AT=0x20;DB=SCSI;DM=VMware__Virtual_disk____1.0_;
LAST_BACKUP	0		12.0.0.2483	W2008R2 #7600  X86_64	2011-02-18 10:52:43.000	2014-01-06 16:32:12.000	BU=Type:Full 'Imag';
UPGRADE	0		12.0.0.2483	W2008R2 #7600  X86_64	2014-01-06 16:32:14.000	2014-01-06 16:32:14.000	CA=C3000D000FEDFF7FDD;
START	0		16.0.0.1691	W2008R2 #7600  X86_64	2014-01-06 16:34:23.000	2014-06-24 13:38:28.000	AT=0x20 0x820;DB=SCSI Unknown;DM=VMware__Virtual_disk____1.0_;DT=REMOTE;
LAST_BACKUP	0		16.0.0.1691	W2008R2 #7600  X86_64	2014-01-06 19:00:17.000	2014-02-28 19:00:12.000	BU=Type:Full 'Imag';
START	0		16.0.0.1915	W7 #7601 SP 1 X86_64	2014-06-24 13:38:28.000	2014-06-24 13:38:28.000	AT=0x820;DB=Unknown;DM=;DT=REMOTE;

[Former Member]

Thanks Jason

[jeff_albion]

It seems that the major difference is that one database was upgraded from version 12, and the other was always defined on SQL Anywhere 16.

If you rebuild the database on version 16, does the behaviour go away?

Regards,

Jeff Albion

SAP Active Global Support