SQL Anywhere database - set option with a variable

Author

set option with a variable

Sharon

2005-06-22, 3:23 am

Hello all,

The following works:

set temporary option Blocking = 'Off'

The following doesn't work:

declare @opt char(255)
select @opt = 'Off'
set temporary option Blocking = @opt

And if you question the motivation for such a thing in the
first place, well, I want to be able to determine the
current option before I change it, so that I can revert back
to it when I'm finished, so as not to interfere with
assumptions taken by other stored procedures.

An example

declare @current_con
nection_number integ
er,
@current_blocking_op
tion char(4)

select @current_connection_
number = connection_property(

'number' )

select @current_bloc
king_option = "value"
from sa_conn_propert
ies()
where PropName ='Blo
cking'
and Number = @current_connection_
number

set temporary option Blocking = Off
--
-- Here I do some work that needs "Blocking" option to be
Off
--
-- And now I want to revert to the original Blocking option
value
--
set temporary option Blocking = @current_blocking_op
tion

Thanks a lot,

Sharon

p.s. Invoking the "set option" statement with the
"temporary" keyword affects the command only in the way that
the option is changed only for the current connection. So if
the current connection runs other stored procedures, it is
essential for me to revert back to the original option
setting.

Glenn Paulley

2005-06-22, 7:23 am

Build up the SET OPTION as a string, and use EXECUTE IMMEDIATE.

Glenn

Sharon wrote in news:42b91128.1aba.1681692777@sybase.com:

> Hello all,
>
> The following works:
>
> set temporary option Blocking = 'Off'
>
>
> The following doesn't work:
>
> declare @opt char(255)
> select @opt = 'Off'
> set temporary option Blocking = @opt
>
>
> And if you question the motivation for such a thing in the
> first place, well, I want to be able to determine the
> current option before I change it, so that I can revert back
> to it when I'm finished, so as not to interfere with
> assumptions taken by other stored procedures.
>
> An example
>
> declare @current_connection_
number integer,
> @current_blocking_op
tion char(4)
>
> select @current_connection_
number = connection_property(

> 'number' )
>
> select @current_blocking_op
tion = "value"
> from sa_conn_properties()

> where PropName ='Blocking'
> and Number = @current_connection_
number
>
> set temporary option Blocking = Off
> --
> -- Here I do some work that needs "Blocking" option to be
> Off
> --
> -- And now I want to revert to the original Blocking option
> value
> --
> set temporary option Blocking = @current_blocking_op
tion
>
>
>
> Thanks a lot,
>
> Sharon
>
> p.s. Invoking the "set option" statement with the
> "temporary" keyword affects the command only in the way that
> the option is changed only for the current connection. So if
> the current connection runs other stored procedures, it is
> essential for me to revert back to the original option
> setting.
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Jeff Albion \(iAnywhere Solutions\)

2005-06-22, 11:23 am

Just an FYI: this has been submitted as an enhancement request already so
that you won't need the EXECUTE IMMEDIATE. :)

--
Jeff Albion
Product Support Analyst
iAnywhere Solutions

"Glenn Paulley" <paulley@ianywhere.com> wrote in message
news:Xns967D4B81476E
1paulleyianywherecom
@10.22.241.106...
> Build up the SET OPTION as a string, and use EXECUTE IMMEDIATE.
>
> Glenn
>
> Sharon wrote in news:42b91128.1aba.1681692777@sybase.com:
>
>
>
>
> --
> Glenn Paulley
> Research and Development Manager, Query Processing
> iAnywhere Solutions Engineering
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288

Sharon

2005-06-23, 3:23 am

Jeff Albion (iAnywhere Solutions) wrote:
....

> "Glenn Paulley" <paulley@ianywhere.com> wrote in message
....

Glenn and Jeff,

Thanks a lot for your help.

Sharon

Sponsored Links