Procedure weird *SET OPTION* error

[#1]

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.

[MarkCulp]

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]

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.