SQLA always add double quotation marks (")

[Former Member]

I really have problem in my SQL Anywhere. I'm using SQL Anywhere 16.

when i write something like this example :

declare var_x int;
declare var_y int;
select dateformat ( current date, 'dd-mm-yyyy' ) as tgl , substr ('123456', 1, 3) as col2;

when it save and then i look again, it being like this

declare "var_x" int;
declare "var_y" int
select "dateformat" ( current date, 'dd-mm-yyyy' ) as tgl , "substr" ('123456', 1, 3) as col2;

SQL Anywhere always add double quotation marks (").

where is the setting which requires that marks or to remove the marks?

I hope from this forum i will find the answer.

[chris_keating]

V16 introduced this behavior change and impacts databases created or upgraded using v16 software. This behavior change did not make it into the documentation.

There is no mechanism to change that behavior.

[Breck_Carter]

Are you referring to the way code is mangled when stored in sysprocedure.proc_defn?

[Former Member]

thank you very much for the answer

My point is, there are two servers with the same settings but produces a different syntax.

Here's an example of what I do

server1:

dbms = sap sqlanywhere 16

PRESERVE_SOURCE_FORMAT = 'Off'

server2:

dbms = sap sqlanywhere 16

PRESERVE_SOURCE_FORMAT = 'Off'

source:

CREATE PROCEDURE p ()

BEGIN

var_x declare int;

var_y declare int;

select dateformat (current date, 'dd-mm-yyyy') as of date, substr ('123456', 1, 3) as col2;

END;

The results (fill in the fields proc_defn):

server1:

CREATE PROCEDURE p ()

BEGIN

var_x declare int;

var_y declare int;

select dateformat (current date, 'dd-mm-yyyy') as of date, substr ('123456', 1, 3) as col2;

END;

server2:

CREATE PROCEDURE p ()

BEGIN

declare "var_x" int;

declare "var_y" int;

select "dateformat" (current date, 'dd-mm-yyyy') as of date, "substr" ('123456', 1, 3) as col2;

END;

That is what I see when I save and reopen it. In this case I use is the central Sybase 6.

[VolkerBarth]

What versions of the database server and/or database file do both servers use?

(You can use the following to answer that:)

select @@version

select * from syshistory where operation in ('init', 'upgrade')

In this case I use is the central Sybase 6.

Isn't that the Sybase Central version fitting the SQL Anywhere 12.0 product?

[Former Member]

We use SQL Anywhere 16. The focus is we had 2 servers, with same version and same setting, but produce different results.

We want to know further whether Are there any settings that affect different results?

[VolkerBarth]

Could you supply the answers to the suggested queries?

According to Chris, it may make a difference whether the databases have been created with v16, too, or with an earlier version, even if both run on the same database server version...

[chris_keating]

This is a broader change i.e., you will also see it in the request level log . If I do a select * from sysprocedure, the request log will show

v12 server, v12 database:  <,1,PREPARE,select * from sysprocedure
v16 server, v12 database:  <,1,PREPARE,select * from sysprocedure
v16 server, v16 database:  <,1,PREPARE,select * from "sysprocedure"

[chris_keating]

Correct. Both the database server and file need be v16 (either created or upgraded). The syshistory table will log this information and Volker's query will report the relevant details.

[Breck_Carter]

Thanks, but it doesn't answer my question.

In the context of this conversation about the way Sybase Central displays stored procedure code, is it true that your comment about the "behavior change" refers ONLY to the way the code is stored in sysprocedure.proc_defn, and has nothing to do with the way it is stored in sysprocedure.source?

[VolkerBarth]

So what exactly is that broader change? - Even if it's missing in the original documentation, we'd surely like to learn about it here and now:)

[chris_keating]

The sysprocedure.source is not impacted by this change and will continue to store the source as provided, if PRESERVE_SOURCE_FORMAT is enabled. As you previously indicated, this is the column that should be used as sysprocedure.proc_defn will be have been parsed before being stored.

[Former Member]

I think I found little difference...

Mr. Barth, This is the result from your suggested queries:

Server 1

@@version;operation;object_id;sub_operation;version;platform;first_time;last_time;details
'16.0.0.1324';'INIT';0;'';'12.0.1.3152';'W-??? #9200  X86_64';'2014-12-02 23:08:01.000';'2014-12-02 23:08:01.000';

Server 2

@@version;operation;object_id;sub_operation;version;platform;first_time;last_time;details;first_time_utc;last_time_utc
'16.0.0.1324';'INIT';0;'';'16.0.0.1324';'W7 #7601 SP 1 X86';'2015-05-24 18:21:25.000';'2015-05-24 18:21:25.000';;'2015-05-24 11:21:25.000+00:00';'2015-05-24 11:21:25.000+00:00'

Initially, both create use of database version 12.

The Database on second server once we rebuild (unload then load to new file) using SQL Anywhere version 16, while the first server we rebuild using SQL Anywhere version 12, but both are now running on sql anywhere 16

[VolkerBarth]

So this slight difference does explain the different behaviour according to Chris's statements:)

That being said, I'd strongly second Breck's suggestions to use the PRESERVE_SOURCE_FORMAT option and/or store (and version) the SQL scripts as files in a source code control system. That will make you truly independent of any (helpful or not) changes w.r.t. the DBISQL code formatting...

[chris_keating]

Sorry for the delay in responding as I needed to research the background.

There were several changes made where SQL Anywhere now quotes all non-keyword identifiers. The first change relates to stored SQL text including but not limited to procedures, views, column defaults, triggers, and events. This is the reason for the changes reported in this thread. The second change is documented in the behavior changes as "Sensitive information is obfuscated in output".The documentation does not include that the quoting of non-keyword identifiers, similar to that with stored SQL text, and semantic transforms such as view flattening will also be applied to statements that are output.

[VolkerBarth]

Thanks for that information - and may I suggest that you add that to the docs? - Isn't that what DCX is for? 🙂

[Breck_Carter]

SQL Anywhere saves stored procedure source code in one or two formats: it always saves a slightly mangled version in SYSPROCEDURE.proc_defn (which is what you are seeing), and optionally saves the original version in SYSPROCEDURE.source.

Here is a demonstration...

CREATE PROCEDURE p() BEGIN
declare var_x int;

declare var_y int;

select dateformat ( current date, 'dd-mm-yyyy' ) as tgl , substr ('123456', 1, 3) as col2;
END;

SELECT proc_defn, source FROM SYSPROCEDURE WHERE proc_name = 'p';

create procedure "DBA"."p"() begin
  declare "var_x" integer;
  declare "var_y" integer;
  select "dateformat"(current date,'dd-mm-yyyy') as "tgl","substr"('123456',1,3) as "col2"
end

create PROCEDURE p() BEGIN
declare var_x int;

declare var_y int;

select dateformat ( current date, 'dd-mm-yyyy' ) as tgl , substr ('123456', 1, 3) as col2;
END

This PRESERVE_SOURCE_FORMAT controls whether or not SYSPROCEDURE.source will be filled. To see your current setting:

SELECT CONNECTION_PROPERTY ( 'PRESERVE_SOURCE_FORMAT' );

CONNECTION_PROPERTY('PRESERVE_SOURCE_FORMAT')
'On'

To set the option...

SET OPTION PUBLIC.PRESERVE_SOURCE_FORMAT = 'ON';

[Former Member]

Thank you Mr. Carter for the advice, but we still have problems. we have 2 servers and we use the same settings. The PRESERVE_SOURCE_FORMAT we set "OFF". Then results in the first server on "proc_defn" give the same results without the double quotes. while the second server with the same settings give results with the addition of double quotation. hope you can help further. Thank you.

[Breck_Carter]

I don't know (and I don't care) what proc_defn contains... it is not what you want.

If you want to preserve the source code formatting in the future, you MUST set PRESERVE_SOURCE_FORMAT to ON.

Then, if you want to fix the procedures that already exist, you will have to re-CREATE or ALTER them to store the properly-formatted source code. There is no magic way to recreate the original format.

I strongly suggest to keep the original source code for all procedures and triggers in text files outside the database, just like you do for all other forms of source code. Then you can exercise control over your source code.

[Former Member]

Thank you very much for the answer

My point is, there are two servers with the same settings but produces a different syntax.

Here's an example of what I do

server1:

dbms = sap sqlanywhere 16

PRESERVE_SOURCE_FORMAT = 'Off'

server2:

dbms = sap sqlanywhere 16

PRESERVE_SOURCE_FORMAT = 'Off'

source:

CREATE PROCEDURE p ()

BEGIN

var_x declare int;

var_y declare int;

select dateformat (current date, 'dd-mm-yyyy') as of date, substr ('123456', 1, 3) as col2;

END;

The results (fill in the fields proc_defn):

server1:

CREATE PROCEDURE p ()

BEGIN

var_x declare int;

var_y declare int;

select dateformat (current date, 'dd-mm-yyyy') as of date, substr ('123456', 1, 3) as col2;

END;

server2:

CREATE PROCEDURE p ()

BEGIN

declare "var_x" int;

declare "var_y" int;

select "dateformat" (current date, 'dd-mm-yyyy') as of date, "substr" ('123456', 1, 3) as col2;

END;

That is what I see when I save and reopen it. In this case I use is the central Sybase 6.