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

Question

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

Mark Barinstein · Accepted Answer · 2019-03-18T08:16:22.080

0

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.

edited Mar 18 '19 at 08:16

answered Mar 15 '19 at 08:50

Mark Barinstein

11,456
2
8
16

Table has full permissions to update and delete.But still getting error.. No view defined for this table. – MOHAMMAD SHAMSHAD HUSSAIN Mar 18 '19 at 02:15
Is it necessary to bind db2cli.lst to unblock the update from cursor ar Client location(where CLI Driver Installed) strcpy (bindFileName, "//@db2cli.lst"); rc = SQLCreatePkg(hdbc, bindFileName, strlen(bindFileName), "GRANT='x';BLOCKING=no;",-3, // SQL_NTS); – MOHAMMAD SHAMSHAD HUSSAIN Mar 18 '19 at 02:27
Show your C code and point to the line where you get the error. – Mark Barinstein Mar 18 '19 at 05:09
Hi Mark, Please find below link for my C Source file and Debug report C Source: https://drive.google.com/open?id=124N_MBUwlsJBaBc2tpyVI3gnRmgGl6gp Debug Report: https://drive.google.com/open?id=13OeuZXpMqtYGYq0bq85YfERFaDo4IZL9 Thanks, Shamshad – MOHAMMAD SHAMSHAD HUSSAIN Mar 18 '19 at 06:16
@MOHAMMADSHAMSHADHUSSAIN I've updated the answer after your files analysis. – Mark Barinstein Mar 18 '19 at 08:17
Thanks it worked with FOR UPDATE clause...Now able to update the data on Remote database. – MOHAMMAD SHAMSHAD HUSSAIN Mar 19 '19 at 01:13

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

1 Answers1