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.
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.
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.
answered 05 Oct '11, 13:33
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.
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.