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.
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).
answered 04 Dec '09, 22:09