on 2011 Oct 05 2:28 PM
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
68 | |
10 | |
10 | |
7 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.