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.

asked 05 Oct '11, 13:28

Ismael's gravatar image

Ismael
2026612
accept rate: 50%

edited 06 Oct '11, 02:47

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665


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.

permanent link

answered 05 Oct '11, 14:24

Tyson%20Lewis's gravatar image

Tyson Lewis
2.2k1641
accept rate: 22%

edited 05 Oct '11, 14:26

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.

permanent link

answered 05 Oct '11, 14:46

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

edited 05 Oct '11, 17:38

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665

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.

(05 Oct '11, 16:04) Ismael

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?

permanent link

answered 05 Oct '11, 13:44

Mark%20Culp's gravatar image

Mark Culp
23.2k9132272
accept rate: 40%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×412
×106
×14

question asked: 05 Oct '11, 13:28

question was seen: 1,254 times

last updated: 06 Oct '11, 02:47