cancel
Showing results for 
Search instead for 
Did you mean: 

Procedure weird *SET OPTION* error

Former Member
3,005

Hi

I've been receving an error with a store procedure which i can't solve on my own.

Please take a look.

ALTER PROCEDURE "DBA"."AjusteFuso"( IN hora INTEGER ) BEGIN

SET OPTION PUBLIC.AjusteFuso = :hora;

END

Executing this procedure gives me this error:

call DBA.AjusteFuso(1)

Cannot delete PUBLIC option 'AjusteFuso' since user settings exist

if i change the line code to:

SET OPTION PUBLIC.AjusteFuso = hora

A row is update with "hora" string instead "1" integer. The behavior is like i had made:

SET OPTION PUBLIC.AjusteFuso = 'hora' // string here!

I have no clue what's happening.

Someone gave me this work around, which work:

DECLARE setOption VARCHAR(100);

SET setOption = 'SET OPTION PUBLIC.AjusteFuso = ' || hora;

EXECUTE IMMEDIATE setOption;

Is this a solution? I'm no expert but it looks like the path to the hell doing this.

EDIT: I'm connecting in the DB using SYBASE CENTRAL 11 in a HOST SQL ANYWHERE 12.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member

In a quick test, I was able to get this to work using:

ALTER PROCEDURE "DBA"."AjusteFuso"( IN @hora INTEGER )
BEGIN
    SET OPTION PUBLIC.AjusteFuso = @hora;
END

I believe the difference in behavior is noted in the documentation that any values for an option can be passed without using a quoted string. Because of this hora is being parsed as a string and not correctly evaluated as the value of the IN parameter.

Adding the @ symbol forced it to be parsed as the parameter, and seems to have the correct behavior in my tests.

Former Member
0 Kudos

That's it! I chose your explanation because you did it first.

Answers (2)

Answers (2)

Former Member

The SET OPTION statement permits the use of host variables for the value, which may exist in other contexts (such as an embedded SQL program). That's why you don't get a syntax error. However, since in your context there are no host variables, the hostvar is treated as NULL, which would delete the PUBLIC setting.

The statement

SET OPTION <name> = <identifier>

(ie, the the value to set the option to is a series of characters matching the syntax for a valid identifier without single or double quotes)

has been permitted since at least version 5. That syntax is now deprecated in version 12, but still works. Hence you can say

SET OPTION PUBLIC.AjusteFuso = hola

or

SET OPTION PUBLIC.AjusteFuso = 'hola'

and achieve the same result.

Because of this "feature" of treating identifiers as literal strings with the SET OPTION statement, you can only use a SQL variable if it is prefixed with the @ sign (ie it is treated as a Transact-SQL variable). This avoids the need to use EXECUTE IMMEDIATE.

This is all documented in the help for the SET OPTION statement.

Former Member
0 Kudos

Perfect, it was what you told. Unfortunately i did not read this issue in the documentation of SET OPTION. I was missing enlightment. Anyway +1 to even my choice of @Tyson Lewis answer because your both did it good.

MarkCulp
Participant

When you used:

SET OPTION PUBLIC.AjusteFuso = :hora;

the ":hora" would have looked like a host variable and been eliminated when parsed (since I presume the client said that no host value was specified so it defaulted to null - perhaps this is a bug?), hence the statement would have been changed to:

SET OPTION PUBLIC.AjusteFuso = ;

which is the statement that would be used to delete the option. Since there are user settings for the option deleting the PUBLIC option is not allowed, hence the error.

I am unsure why your

SET OPTION PUBLIC.AjusteFuso = hora;

statement evaluated the "hora" as a string rather than consider it a parameter value?

What client were you using when defining the procedure?

Former Member
0 Kudos

I knew i was forgetting something! I'm so sorry!

I'm using SYBASE CENTRAL 11 connected in SQL ANYWHERE 12.

Indeed it is evaluating as string instead parameter value, and this only happen in this specific procedure.