The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

I'm having problem executing a specific query using php API from sqlanywhere 12.

It only happens with SET OPTION query.

<?php
    // setup omitted for clarity here
    $result = sasql_query( $conn, "SET OPTION PUBLIC.AjusteFuso = '0'" );
    // clean up omitted for clarity here
?>

I receive the following exception:

'AjusteFuso' is an unknown option SET OPTION PUBLIC.AjusteFuso = '0'

But this is not true, it is known option in my database!

1 - others select/update/delete/insert works normal as expected.

2 - SET OPTION works ok if i test it using sybase central.

This same query was working normal using sql anywhere 9 with sybase_query() old API.

EDIT:

I did the same SQL command using .net API and it does not work too.

SACommand sql = new SACommand("SET OPTION AjusteFuso = 10", conn); conn.Open(); Console.WriteLine(sql.ExecuteNonQuery());

EDIT 2 Correction: in .net API DOES WORKS. I was doing wrong test.

asked 03 Oct '11, 16:19

Ismael's gravatar image

Ismael
2026612
accept rate: 50%

edited 06 Oct '11, 02:47

Volker%20Barth's gravatar image

Volker Barth
29.3k287438645

Is this user-defined option already existing (i.e. shown when selecing from SYSOPTION)?

And does the error also appear when using sasql_real_query?

(03 Oct '11, 18:19) Volker Barth
Replies hidden

Yes, it is user defined.

Yes, does also appears in sasql_real_query too.

I'm still looking for a solution. No clues yet.

(04 Oct '11, 07:35) Ismael

Can you change a known, public option (for example, optimization_level) with the same code? Or does this too give an error?

(04 Oct '11, 10:08) Glenn Paulley
Replies hidden

Yes, i can try to change and the query works with no errors. But, behinds the scenes a new temporary record is created for my current php user. At least, no errors.

(04 Oct '11, 15:45) Ismael

That would imply the setting as PUBLIC does not take effect. Have you used the "SET OPTION PUBLIC.MyOption" syntax (as your .Net sample has not)?

(04 Oct '11, 15:55) Volker Barth
1

I said a wrong positive error. .NET is working. Now i think this is a php API issue.

I'll try to solve this wraping the SET OPTION inside some procedure.

(05 Oct '11, 10:12) Ismael
showing 2 of 6 show all flat view

I could not solve the problem.

I did a work around wrapping SET OPTION inside a procedure, it works, but it leaded me to another big frustration i could not understand.

If you're curious, i opened a question here since it is another issue.

http://sqlanywhere-forum.sap.com/questions/7856/procedure-weird-set-option-error

permanent link

answered 05 Oct '11, 13:33

Ismael's gravatar image

Ismael
2026612
accept rate: 50%

Well, obviously that weird problem seems solved:)

(05 Oct '11, 17:44) Volker Barth

Have you reviewed the following doc section regarding user-defined database options? The SQL Anywhere error is suggesting that the user-defined option cannot be found.


[From: http://dcx.sybase.com/index.html#1201/en/dbreference/set-option-statement.html ]

User-defined options Any option, whether user-defined or not, must have a public setting before a user-specific value can be assigned. The database server does not support setting TEMPORARY values for user-defined options. For example, to create a user-defined option named ApplicationControl, you first issue the statement:

SET OPTION PUBLIC.ApplicationControl = 'Default';

This statement sets the ApplicationControl option to Default for all users, and takes effect with each new connection to the server. Subsequently, an individual user may establish their own setting for this option by issuing a separate SET OPTION statement.


Have you tried connecting with a 'known' application (e.g. DBISQL) to define the public option first, then trying to change the public option from your application? Also remember that "PUBLIC" settings are only set when first connecting to the database server, and both PHP and .NET offer features regarding persistent connections or connection pooling, so you should check the PUBLIC option setting after making sure your application has disconnected and reconnected.

permanent link

answered 04 Oct '11, 14:59

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

edited 04 Oct '11, 16:30

1

Well, in the question he does use the "SET OPTION PUBLIC.MyOption" syntax and has verified the user-defined option does exist in SYSOPTION, so that should be fine (particularly as it seems to have been fine with v9 as well).

And the docs to sasql_set_option just list three possible options to set, and these are not the typical SET OPTION options...

Just my observations:)

(04 Oct '11, 15:43) Volker Barth
Replies hidden

Yes, i have review with no success.

User-defined options DO HAVE PUBLIC settings. Either via known application or PHP API.

I have created a non-existent option and it returns me "'xxx' is an unknown option" BUT it is created in the database, which i can see by: SELECT * FROM SYSOPTIONS WHERE "option" like '%xxx%'

I thought sasql_set_option would solve my problem too, but in documentation says: auto_commit, row_counts or verbose_errors are only available as options.

(04 Oct '11, 16:06) Ismael

And the docs to sasql_set_option just list three possible options to set, and these are not the typical SET OPTION options...

Yes, I meant to edit my answer prior to people noticing I included this note by accident - I noticed when reading the docs while creating the HREF link that 'values' only conspicuously has three options. sasql_set_option should not be relevant to this discussion.

(04 Oct '11, 16:32) Jeff Albion

Well, yes, sometimes we're fast readers:)

(05 Oct '11, 03:18) Volker Barth
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:

×404
×47
×14

question asked: 03 Oct '11, 16:19

question was seen: 1,420 times

last updated: 06 Oct '11, 02:47