cancel
Showing results for 
Search instead for 
Did you mean: 

Global variables and constants

Former Member
5,928

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

Accepted Solutions (1)

Accepted Solutions (1)

MarkCulp
Participant

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

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

MarkCulp
Participant
0 Kudos

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
Contributor
0 Kudos

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

Answers (4)

Answers (4)

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

VolkerBarth
Contributor
0 Kudos

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
Participant

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
0 Kudos

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
Participant

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
0 Kudos

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

VolkerBarth
Contributor

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
0 Kudos

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
Participant
0 Kudos

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
Contributor
0 Kudos

@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
Participant
0 Kudos

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
Contributor
0 Kudos

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
Contributor
0 Kudos

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.