I'm running SQL Anywhere When saving a stored procedure or function to the database, SQL Anywhere applies it's own formatting to the code before storing it. The issue I'm having is that some databases convert the case of domains and some do not. For example, if I have code like this:

DECLARE @my_variable INTEGER;

It will be saved like so, on all databases I've tried it on:

declare @my_variable integer;

This is fine. However, I'm running into an issue with a few domains. It appears to be just system-supplied domains (money, datetime, image, etc) and not user domains or the base data types (integer, double, decimal, etc). Of course, I verified that they were indeed all lowercase in each database ;)

For example, the "money" domain is being treated differently by different databases which I thought were configured the same way. Here is the input:

DECLARE @my_money MONEY;

The result on one database is all lowercase. The result on another database has "MONEY" still uppercase. My question is- what determines this behavior? I tried searching the manual for database options which could have something to do with this, but I haven't found any.

These databases are involved in SQL Remote replication, so I have tried issuing the updates via PASSTHROUGH as well as manually saving the procedures on the databases using iSQL and Sybase Central.

I should provide a little background on why a silly little thing like this is an issue for me- I am creating a database schema validation tool for use in our Continuous Integration environment. The idea is that I take any two databases, create full schema DDL of all the objects, then compare them and create a report of the differences.

Despite the fact everyone knows schema changes, which include 700 procedures in our case, need to be changed via PASSTHROUGH, you'd be surprised at how many times people forget.

I considered using dbunload, but it was going to be more of a hassle to parse everything out of the resulting reload.sql reliably than it would be to simply create the DDL by hand.

PRE-POST LAST SECOND REVELATION: So, I thought to myself right before I posted this- "Hey, wait, maybe I should check to see if Foxhound lists something under 'Curiosities'..." And I saw this:

SET OPTION PUBLIC.Preserve_source_format = 'OFF'; -- different from default 'On'

Manual Page

I was told 2 and a half years ago that such a thing didn't exist. OK, so maybe I should turn this on and pull the procedure definitions from the system "source" column :) But out of curiosity, my original question still stands.

asked 17 Feb '10, 16:19

Ralph%20Wissing's gravatar image

Ralph Wissing
accept rate: 0%

PRESERVE_SOURCE_FORMAT has been available at least since ASA 8:) So you say, the values in sysusertype.type_name are all lower-case (particularly, as "money" is a vendor-supplied type), whereas the case used in STPs varies? (No, I don't have an answer as to why...)

(17 Feb '10, 16:35) Volker Barth

Doncha love Foxhound? ...new Beta starting soon! :)

(17 Feb '10, 16:53) Breck Carter

@Volker: Correct, sysusertype values are lowercase. @Breck: Yes, yes I do :)

(17 Feb '10, 17:00) Ralph Wissing

@Breck: New "tag" suggestion: Foxhound-Saved-My-Day:)

(17 Feb '10, 17:27) Volker Barth

The rules over the years have changed (for various reasons... but mainly to properly handle issues like the Turkish 'i' vs 'I' - two different characters: one has a dot, the other does not) but for the last few releases the unparsed stored definition has been to:

  • output keywords in lower case
  • output identifiers in the same case as they were entered

Note that all SQL keywords and identifiers are case insensitive so the case that is used is purely aesthetics (except for aforementioned Turkish i/I issue).

The best method to use to get the identical source that was entered back out is to use the PRESERVE_ SOURCE_ FORMAT option and then select from the source column instead of the proc_defn column of sysprocedure.

The issue that you are seeing with money vs MONEY (and other domains) is most likely that the databases were created using difference versions/builds and that the unparse rules had changed between those versions.

For example, the following QTS issue from October 2003 is typical of the type of issues that were being fixed around that time:

QTS 334492: DESCRIBE USER TYPES returned user type name in upper case

Versions fixed: Jasper, 9.0, 9.0.0(1222), 8.0, 8.0.2(4325), 8.0.1(3133)

The name of a user-defined type (or domain) would not be returned in its 
original case by a DESCRIBE USER TYPES Embedded-SQL statement. This has been 

There are several other similar fixes.

permanent link

answered 18 Feb '10, 03:06

Mark%20Culp's gravatar image

Mark Culp
accept rate: 41%

So the final answer is: "The strange ways of some collations"?

(18 Feb '10, 14:59) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 17 Feb '10, 16:19

question was seen: 2,146 times

last updated: 18 Feb '10, 03:06