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

asked 08 Dec '14, 09:19

M%20G's gravatar image

M G
629253044
accept rate: 42%

1

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

(09 Dec '14, 02:26) M G
Replies hidden
1

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.

(09 Dec '14, 04:01) Volker Barth

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

(09 Dec '14, 10:21) Volker Barth

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.

permanent link

answered 08 Dec '14, 09:44

Mark%20Culp's gravatar image

Mark Culp
24.9k10139297
accept rate: 41%

1

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

(09 Dec '14, 02:31) M G
Replies hidden

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

(16 Dec '14, 09:21) Mark Culp

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

(16 Dec '14, 09:23) Volker Barth

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

permanent link

answered 08 Dec '14, 09:49

Vincent%20Buck's gravatar image

Vincent Buck
70681520
accept rate: 15%

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

(09 Dec '14, 03:56) Volker Barth

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.

permanent link

answered 09 Dec '14, 07:46

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

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?

(09 Dec '14, 08:31) M G
Replies hidden
1

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.

(13 Dec '14, 12:10) Breck Carter

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

(15 Dec '14, 11:22) M G
Replies hidden
2

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

(15 Dec '14, 11:59) Volker Barth

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 :p

(16 Dec '14, 03:52) M G

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 :)

(16 Dec '14, 07:36) Breck Carter

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

(16 Dec '14, 08:31) Volker Barth

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.

(16 Dec '14, 11:16) Breck Carter
showing 2 of 8 show all flat view

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.

permanent link

answered 08 Dec '14, 09:45

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 08 Dec '14, 11:26

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.

permanent link

answered 30 Dec '15, 04:22

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 30 Dec '15, 04:23

Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

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:

×438
×260
×14

question asked: 08 Dec '14, 09:19

question was seen: 5,129 times

last updated: 30 Dec '15, 04:23