Search code examples
cdb2db2-luw

Update on table error with db2 CLI/ODBC Connection from C Application?

Getting error while updating a remote database from C application connected through DB2 CLI/ODBC Driver.

The error occurs with the function SQLExecute of the UPDATE-statement (returns -1).

SQLSTATE: 42828, Native Error Code: 4294966786, [IBM][CLI Driver][DB2/AIX64] SQL0510N UPDATE or DELETE is not allowed against the specified cursor. SQLSTATE=42828

Thanks, Mohammad Shamshad

Solution

  • Programming error. More information at the link below.
    db2 "? SQL0510N"

    SQL0510N UPDATE or DELETE is not allowed against the specified cursor.

    Explanation:

    The program attempted to execute an UPDATE or DELETE WHERE CURRENT OF cursor statement against a table or view definition that does not permit the requested update or delete operation. For example, this error can occur in a delete from a read-only view or in an update where the cursor was not defined with the FOR UPDATE clause.

    From the log file provided:

    SQLPrepare(65537,SELECT * FROM SPAUTH WHERE TRMCAU=? AND PRFCAU=? AND LVL2AU=? ORDER BY TRMCAU ASC, PRFCAU ASC, LVL2AU ASC OPTIMIZE FOR 40 ROWS,SQL_NTS);
    SQLSetCursorName(65537, SPAUTHU);
    ...
    SQLPrepare(65539,UPDATE SPAUTH set TRMCAU=?,PRFCAU=?,LVL2AU=?,AUTHAU=? where current of SPAUTHU, SQL_NTS);
    SQLExecute(65539);

    [ERROR] 0315-152416 Process 31244 File isp406000o/qcsrc/TestUpd1.c Line 1653 : SQLSTATE: 42828, Native Error Code: 4294966786, [IBM][CLI Driver][DB2/AIX64] SQL0510N UPDATE or DELETE is not allowed against the specified cursor. SQLSTATE=42828

    Cursor SPAUTHU is not updatable, but you try to issue a positioned update against it.
    DECLARE CURSOR statement:

    A column in the select list of the outer fullselect associated with a cursor is updatable if each of the following conditions is true:

    • The cursor is deletable
      ...

    A cursor is deletable if each of the following conditions is true:
    ...

    • The outer fullselect does not include an ORDER BY clause (even if the ORDER BY clause is nested in a view), and the FOR UPDATE clause has not been specified

    ...

    Add FOR UPDATE clause at the end of your SELECT statement.