|
Hi I've been receving an error with a store procedure which i can't solve on my own. Please take a look.
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:
A row is update with "hora" string instead "1" integer. The behavior is like i had made:
I have no clue what's happening. Someone gave me this work around, which work:
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. That's it! I chose your explanation because you did it first.
(05 Oct '11, 16:03)
Ismael
|
|
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. |
|
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? 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.
(05 Oct '11, 14:03)
Ismael
|
