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.

asked 04 Dec '09, 20:36

RobertDD's gravatar image

RobertDD
489161719
accept rate: 42%

edited 05 Jan '10, 08:05

Breck%20Carter's gravatar image

Breck Carter
27.4k424585838


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

permanent link

answered 04 Dec '09, 22:09

Volker%20Barth's gravatar image

Volker Barth
30.0k294448654
accept rate: 32%

1

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.

(07 Dec '09, 14:56) RobertDD

I found this this article from Sybase which references the error, but is for some older technology. I don't know Delphi so I don't know if it will help or not.

permanent link

answered 04 Dec '09, 21:22

Calvin%20Allen's gravatar image

Calvin Allen
1.5k232638
accept rate: 25%

Thank you for your reply. I found that article myself before I posted here, and I played around with different cursor type settings based on the info, but was unsuccessful in getting results.

(07 Dec '09, 14:56) RobertDD
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×14

question asked: 04 Dec '09, 20:36

question was seen: 1,306 times

last updated: 05 Jan '10, 08:05