on 2014 Dec 08 9:19 AM
Hi, I have just assumed that you can not declare global variables in SQL Anywhere, but is this a right assumption? I have not read anything about it in the manual for 12 or 16 (but I have not read every word). In Oracle you can declare global variables and constants in package specifications and then you can use/access these variables in all store procedures. Is there some type of same functionality in SQL Anywhere?
Thanks in advance
M G
Request clarification before answering.
SQL Anywhere 12 and 16 (and prior versions) do not have the concept of database (or 'package') global variables.
This capability is on the future features list.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Curious as I am: Does this mean it will be a feature in a SQL Anywhere version in the near future?
We store quite a few global vars and constants in user-defined (public) database options.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Oracle "global variables" are described thusly: "Such packages let you define global variables--usable by subprograms and database triggers--that persist throughout a session."
That seems to imply they are connection-level global variables, EXACTLY like CREATE VARIABLE in SQL Anywhere, and not global across all sessions or connections.
AFAIK the only true "global variable" in any RDBMS, with changes instantly visible to all connections, is called a "table"... you can also use tables to create global constants by simply disallowing updates.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Well, that would be a "constant" in Oracle-speak rather than a "variable" 🙂
Nitpicking aside, SQL Anywhere doesn't have named constants... it would be very nice to have, especially if it came without the mind-boggling complexity of Oracle packages.
The ability to "group" objects that are logically connected is certainly a reasonable goal.
IMHO that's not really well supported in SQL Anywhere: You either have to use different owners/schemata (which has security considerations and may be somewhat "overkill"), or to use some kind of homebrewed naming scheme, say a common prefix.
Others have requested a better support here:
Please implement a "Work Space" type grouping for database objects
IMO develpment work should be performed separately from the production database, with changes coded in text files outside the database altogether. The changed scripts can then be loaded via ISQL into the development database and tested there, then loaded into production. Separate folders and/or file naming conventions can be easily used to "group" modules however one wants.
On the other hand, using Sybase Central to directly edit procedure code in an active database is like using a binary editor to patch executable programs... too dangerous for me 🙂
@Breck: Just to understand: While I surely share your point of view w.r.t. the different handling of scripts in development and production (and I would add "put your scripts in a source code control system"), I do not see the connection to the topics
we have discussed here so far. Both would apply to development and production, methinks. What am I missing?
FWIW: I can't comment on the pros and cons of Oracle packages, have not used them...
FWIW, v17 has introduced "database-scope variables" with the CREATE DATABASE VARIABLE statement.
Besides the different scope compared to connection-level variables, database-scope variables can also be offered in a "read-only" mode, i.e. the new UPDATE PUBLIC DATABASE VARIABLE system privilege can be used to prevent users (other than the owner) from modifying the values and thereby making them "database constants". - In contrast, connection-level variables can always be modified from the according connection.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You can use connection-specific variables (aka CREATE VARIABLE) - they are accessible for all code running in the according connection (and may be set up automatically in a login procedure).
As stated, they are "global" w.r.t. to the according connection but are connection-specific so each connection has its own instance (or no at all). Note, that they can be altered by users so they are not constants. For the latter, you might use a global temp table (possibly with the "SHARE BY ALL" clause, if the values should not be connection-specific) and just give select permission to the users.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
76 | |
30 | |
10 | |
8 | |
8 | |
7 | |
7 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.