Option Value Out Of Range

[Former Member]

I am updating an old database from SQL Anywhere 5.5 to 11. I use Delphi applications with ODBC98 components to talk to a SQL Anywhere server.

In SQL 5.5 I would open a "live query" and update records in the result set (this method uses the SQLSetPos ODBC function with parameters SQL_UPDATE and SQL_LOCK_NO_CHANGE, but this actual implementation is hidden by the ODBC98 components). This was not a problem in 5.5, even when the query I was updating was a view or a join involving multiple tables. In SQL Anywhere 11 I get the error "Option Value Out Of Range" when the updated query involves multiple tables, which (I think) indicates that the dataset I am trying to update is read only.

I have two questions: 1. Is there a way around this? 2. What was the last version of SQL Anywhere in which these kinds of updates used to work?

I understand that these live queries are not the way to do this, but I am dealing with legacy code and this technique is too widely used throughout the code and too integrated into its foundation to easily replace it.

[VolkerBarth · Accepted solution]

Without knowing Delphi and its facilities, the option ansi_update_constraints comes to my mind, cf. the online docs.

That option can influence whether joins are updateable or not, and it may have changed its value between different versions (or may not be available in SA 5.5).

[Former Member · Accepted solution]

Your answer hit the mark. After setting the ANSI_UPDATE_CONSTRAINTS to off we are able to run all of our existing software (as far as we can tell) same as we ran it under 5.5. Genious! We are back on track with our update. Your help was invaluable! Thank you so much.