Quotes from heck and other format anomalies

[Former Member]

How come if I type in code for a procedure, use 4-space indents and blank lines, that upon viewing the code in SQL Central the code is 2-space indented, the blank line is stripped out, and ALL identifiers are quoted (even though I told the editor to not quote identifiers)? There seems to be no way to create nice-looking, readable code. The code below was NOT the way it was typed in. Yes, the snippet was taken from a Toad screenshot, but SQL Central is similar.

SQL Anywhere 16

Message was edited by: Larry Molter

[Former Member · Accepted solution]

According to Sybase: SQL Anywhere by default conforms to SQL/92 standards that says, "Quote all database identifiers". MSSQL T-SQL says don't. I'm just used to the lack of quotes. I guess the last company I was at had the quoting turned off. Makes for less busy-looking code. Oh, well...

Oh, but why are the leading spaces and blank lines being deleted? I goes against my grain to write sloppy-looking, unformatted code.

[JasonHinsperger · Accepted solution]

When the stored procedure is processed by the server it is parsed and then 'unparsed' into a format that is more 'correct' to the server.  This removes most formatting and explains what you are seeing.

This typically isn't an issue because many (most?) development teams store their SQL as script files in their source control system and edit from there as opposed to from editing from the database definitions directly.

One final note. If you use Sybase Central to edit your stored procedures, it will preserve the formatting of the procedures for you.

--Jason

[Former Member · Accepted solution]

Will SQL Central still preserve the formatting if PRESERVE_SOURCE_FORMAT is off? When I look for the source of my new procedure, it's not in SYSPROCEDURE.source, it's in the proc_defn column with full quotes. Other procedures, probably created on SQL Anywhere 12 (and the preserve flag was set) before being migrated to 16 have source in both columns; the proc_defn is fully-quoted and missing its formatting, while the source in the source column is properly formatted and lacking extraneous quotes.

[Former Member · Accepted solution]

Nope. Saved proc to a file then dropped procedure. I formatted the code without quotes, added 4-space indents and a blank line for good luck. Went into interactive SQL and loaded the file and ran it (it was a CREATE procedure).

I refreshed the procedures and functions list and clicked on the procedure. Fully quoted with all format gone. Source is not in sysprocedures.source; only proc_defn.

[JasonHinsperger · Accepted solution]

Your findings are correct.  The PRESERVE_SOURCE_FORMAT option must be on for the formatting to be stored in the source column of sysprocedure.

--Jason

[Former Member · Accepted solution]

No source control here for database objects. DBA squashed my request to turn the flag on. I guess no one gives a hoot about readability and maintainability. I'm a bit of a perfectionist in this regard. It's hard to come down off of the cloud I'm on 😉

[former_member188493]

If you store and maintain all your database source code in separate text files, just like folks do with other source code like *.c and *.cpp files, you can have complete control over the formatting regardless of other people's [cough] misguided behavior.

The dbisql utility together with READ and PARAMETERS statements are very powerful tools for maintaining SQL objects, much more powerful than Sybase Central.