on 2010 Feb 17 4:19 PM
I'm running SQL Anywhere 9.0.2.3249. 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'
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.
Request clarification before answering.
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:
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
fixed.
There are several other similar fixes.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
79 | |
29 | |
9 | |
9 | |
9 | |
7 | |
6 | |
6 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.