SQL Anywhere database - Scope of user variables created in a procedure

Author

Scope of user variables created in a procedure

David DeRam

2005-04-20, 8:23 pm

I'm having problems creating SQL variables (using the CREATE VARIABLE
statement) inside of a procedure. The variable is immediately going out of
scope. It can not be used by any procedure outside the one it is created
in.

The procedure is being called by the "Connect" Event. Maybe there is some
timing issue and this is the problem?

I'm just running something like this.

CREATE VARIABLE gi_id integer;
SET gi_id = 152;

Within the procedure I made sure it was outside anything nested. It is
right after the BEGIN.

The "Disconnect" event calls the same procedure and it knows nothing about
gi_id. (The IF VAREXISTS() returns 0). I can not access gi_id from
anywhere else either.

Can anybody provide insight on what the scope of the user defined variable
should be? Thank you for any help.

Bruce Hay

2005-04-20, 8:23 pm

Event handlers run on their own internal connection, so variables that you
create in an event handler will only persist for the duration of the
handler.

If you want to create a variable that persists for the life of a connection,
create it in a login procedure; however, you won't be able to query the
value of that variable in an event handler.

If you want to communicate between a CONNECT event handler and a DISCONNECT
event handler, you'll need to use a table.

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

"David DeRam" < ns_xxxdderam@progeny
ns_xxx2000.com> wrote in message
news:4266bc2f@forums
-1-dub...
> I'm having problems creating SQL variables (using the CREATE VARIABLE
> statement) inside of a procedure. The variable is immediately going out
of
> scope. It can not be used by any procedure outside the one it is created
> in.
>
> The procedure is being called by the "Connect" Event. Maybe there is some
> timing issue and this is the problem?
>
> I'm just running something like this.
>
> CREATE VARIABLE gi_id integer;
> SET gi_id = 152;
>
> Within the procedure I made sure it was outside anything nested. It is
> right after the BEGIN.
>
> The "Disconnect" event calls the same procedure and it knows nothing about
> gi_id. (The IF VAREXISTS() returns 0). I can not access gi_id from
> anywhere else either.
>
> Can anybody provide insight on what the scope of the user defined variable
> should be? Thank you for any help.
>
>
>
>
>
>

Breck Carter

2005-04-20, 8:23 pm

Events run on their own connections, so a connection-level
variable created in an event will be dropped when the
connection ends.

FWIW the same behavior affects local temporary tables and
the data in GLOBAL TEMPORARY tables, and also applies to
code invoked from a web service.

Breck

> I'm having problems creating SQL variables (using the
> CREATE VARIABLE statement) inside of a procedure. The
> variable is immediately going out of scope. It can not be
> used by any procedure outside the one it is created in.
>
> The procedure is being called by the "Connect" Event.
> Maybe there is some timing issue and this is the problem?
>
> I'm just running something like this.
>
> CREATE VARIABLE gi_id integer;
> SET gi_id = 152;
>
> Within the procedure I made sure it was outside anything
> nested. It is right after the BEGIN.
>
> The "Disconnect" event calls the same procedure and it
> knows nothing about gi_id. (The IF VAREXISTS() returns
> 0). I can not access gi_id from anywhere else either.
>
> Can anybody provide insight on what the scope of the user
> defined variable should be? Thank you for any help.
>
>
>
>
>
>

Sponsored Links