SQL error using sqlanywhere php API sasql_query()

[Former Member]

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.

[VolkerBarth]

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?

[Former Member]

Yes, it is user defined.

Yes, does also appears in sasql_real_query too.

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

[Former Member]

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

[Former Member]

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.

[VolkerBarth]

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)?

[Former Member]

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.

[Former Member · Accepted solution]

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

[VolkerBarth · Accepted solution]

Well, obviously that weird problem seems solved:)

[jeff_albion]

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.

[VolkerBarth]

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:)

[Former Member]

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.

[jeff_albion]

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.

[VolkerBarth]

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