Global variables and constants

[Former Member]

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

[Former Member]

Thank you for your answers. I have read a little about Create variables before, but I have some values that should not be changed, they need to be constants. Now I am playing with the idea to create a procedure in which my "wanted constant" should be set and then always when I need them I could do a select from this procedure, to fetch them, but I have not yet reached a conclusion if it is a good idea, but it could in theory solve my problem ...

[VolkerBarth]

Wouldn't a "config table" (possibly filled at database start-up) do as well, as long as users are not allowed to modify the values?

---

Apparently you can also use stored functions to implement individual constants - that's even handy for "lazy evaluation", i.e. if you may want to delay the calculation of these variables/constants until they are really needed. And the values (or their calculation) can easily be SET HIDDEN, in case that is desired.

[VolkerBarth]

FWIW, as CREATE VARIABLE allows for connection-specific variables but not for constants, general support for constants has been asked for, too:

Add the possibility to declare constants within blocks

Possibly one could enhance that to some kind of CREATE CONSTANT statement...

[MarkCulp · Accepted solution]

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.

[Former Member · Accepted solution]

Curious as I am: Does this mean it will be a feature in a SQL Anywhere version in the near future?

[MarkCulp · Accepted solution]

At this time I cannot say when the feature will be added to SQLAnywhere since that would be a "future looking statement". We have many features on our futures list and we appreciate input such as this one since it helps us prioritize the list. Thanks

[VolkerBarth · Accepted solution]

Mark, may you tell us whether full packages or "just" global variables/constants are on the wish list?

[former_SQLA_member1694886]

We store quite a few global vars and constants in user-defined (public) database options.

[VolkerBarth]

Note that even when users are not permitted to change the value of public options (which requires DBA authority in v12 and below), they could still overwrite them with a user-specific value so that might not make for real constants...

[Breck_Carter]

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.

[Former Member]

The difference is in oracle you can define is as a constant (the value can not change is the same always), what I understood in Create Variable is that you can SET it to another value in your session, so i do not see it as a constant, or am I wrong?

[Breck_Carter]

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.

[Former Member]

Well I actually like oracle packages. I think it a very good way to 'sort' procedures that 'belong' together...

[VolkerBarth]

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

[Former Member]

well some kind of work space where you could group object together would be nice 🙂 .....perhaps even the functionality for constants could be applied within this group 😛

[Breck_Carter]

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 🙂

[VolkerBarth]

@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

• global variables/constants/packages and
• how to group database objects

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...

[Breck_Carter]

The connection is this: If the source code for database objects is maintained outside the database, there is no need for fancy groupings inside the database.

[VolkerBarth]

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.

[VolkerBarth]

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.