Question Re: SQL error using sqlanywhere php API sasql_query() in Technology Q&A

SQL error using sqlanywhere php API sasql_query()

Former Member — Mon, 03 Oct 2011 16:19:26 GMT

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.

Re: SQL error using sqlanywhere php API sasql_query()

VolkerBarth — Mon, 03 Oct 2011 18:19:08 GMT

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?

Re: SQL error using sqlanywhere php API sasql_query()

Former Member — Tue, 04 Oct 2011 07:35:33 GMT

Yes, it is user defined.

Yes, does also appears in sasql_real_query too.

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

Re: SQL error using sqlanywhere php API sasql_query()

Former Member — Tue, 04 Oct 2011 10:08:47 GMT

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

Re: SQL error using sqlanywhere php API sasql_query()

jeff_albion — Tue, 04 Oct 2011 14:59:50 GMT

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.

Re: SQL error using sqlanywhere php API sasql_query()

VolkerBarth — Tue, 04 Oct 2011 15:43:50 GMT

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

Re: SQL error using sqlanywhere php API sasql_query()

Former Member — Tue, 04 Oct 2011 15:45:20 GMT

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.

Re: SQL error using sqlanywhere php API sasql_query()

VolkerBarth — Tue, 04 Oct 2011 15:55:26 GMT

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

Re: SQL error using sqlanywhere php API sasql_query()

Former Member — Tue, 04 Oct 2011 16:06:28 GMT

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.

Re: SQL error using sqlanywhere php API sasql_query()

jeff_albion — Tue, 04 Oct 2011 16:32:25 GMT

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.

Re: SQL error using sqlanywhere php API sasql_query()

VolkerBarth — Wed, 05 Oct 2011 03:18:06 GMT

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

Re: SQL error using sqlanywhere php API sasql_query()

Former Member — Wed, 05 Oct 2011 10:12:32 GMT

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.

Re: SQL error using sqlanywhere php API sasql_query()

Former Member — Wed, 05 Oct 2011 13:33:19 GMT

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

Re: SQL error using sqlanywhere php API sasql_query()

VolkerBarth — Wed, 05 Oct 2011 17:44:22 GMT

Well, obviously that weird problem seems solved:)