Updating and deleting rows through a cursor

The Microsoft ODBC Programmer's Reference suggests that you use SELECT ... FOR UPDATE to indicate that a query is updatable using positioned operations. You do not need to use the FOR UPDATE clause in SQL Anywhere: SELECT statements are automatically updatable as long as the following conditions are met:

  • The underlying query supports updates.

    That is to say, as long as a data modification statement on the columns in the result is meaningful, then positioned data modification statements can be carried out on the cursor.

    The ansi_update_constraints database option limits the type of queries that are updatable.

    For more information, see ansi_update_constraints option [compatibility].

  • The cursor type supports updates.

    If you are using a read-only cursor, you cannot update the result set.

ODBC provides two alternatives for carrying out positioned updates and deletes:

  • Use the SQLSetPos function.

    Depending on the parameters supplied (SQL_POSITION, SQL_REFRESH, SQL_UPDATE, SQL_DELETE) SQLSetPos sets the cursor position and allows an application to refresh data, or update, or delete data in the result set.

    This is the method to use with SQL Anywhere.

  • Send positioned UPDATE and DELETE statements using SQLExecute. This method should not be used with SQL Anywhere.
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0