0

I am getting this error when a program executing, when it is trying to update data in the table. Is this because of duplicate record ? what will be the condition to avoid sql error for duplicate record. below error:

Description = [IBM][CLI Driver][DB2/NT64] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "FH.Product" from having duplicate values for the index key. SQLSTATE=23505 Code here:

 request = [
    UPDATE FH.product
    SET numchassis='%3',mtoc='%4'
    WHERE numchassis='%1' AND mtoc='%2'
]      
query=Constructed String(query,sChassis,sMTOC,sVin,sNewMTOC)
SQLChangeConnection(nCnx_DWH)
bRes = SQLExec(query,"REQUPD3")
IF NOT bRes THEN
    SQLInfoGene("REQUPD3")
    gsError=SQL. MyError+RC+Request
    sSQLState is a string = SQL. MyError
    sNativeErrorCode is a string = Spaceless(ExtractString(sSQLState,2,"Native Error Code ="))
    sSQLState = WithoutSpace(ExtractString(ExtractString(sSQLState,2,"SQL State ="),1,RC))
    //If update error because duplicate
    IF sNativeErrorCode = "-803" AND sSQLState = "23505" THEN
        Delete
        ECRIRE_TRACE("calling If condition ")
        deleteDepot(sChassis,sMTOC)
    OTHERWISE
        FIN_ERREUR()
    END
END

Problem is in IF condition, it is not calling the deleteDepot()method.

nfgl
  • 2,812
  • 6
  • 16
Rohini Kumari
  • 41
  • 2
  • 6
  • I used this condition: if (sNativeErrorCode = "-803" && sSQLState = "23505") { deleteData(vin,mtoc); } else {System.out.println("sql error"); after matching the if condition also the control not going to the deleteData() method. what to put in the if condition so it will call the deleteData() method. – Rohini Kumari Feb 18 '22 at 05:25
  • Not sure what you are asking: Are you asking if this means there would be duplicates in the table or how to avoid it based on SQL statements or how to handle it in what seems Javascript / Node.js? Add more details to the question by editing it. – data_henrik Feb 18 '22 at 06:38
  • I am asking. how to handle it, If we have duplicate record while executing the sql query. Through the program . basically I tried this way but it is not entering into the if condition example: if (sNativeErrorCode = "-803" && sSQLState = "23505"). – Rohini Kumari Feb 18 '22 at 07:22
  • [SQL0803N](https://www.ibm.com/docs/en/db2/11.5?topic=messages-sql0750-sql0999#sql0803n). What's the programming language and data types of `sNativeErrorCode` & `sSQLState`? – Mark Barinstein Feb 18 '22 at 07:53
  • Please add every detail to the question itself by editing it. And format the code and messages for readability – data_henrik Feb 18 '22 at 07:55
  • Programming language we used as windev which is internally used H -language. sNativeErrorCode & sSQLState are string. – Rohini Kumari Feb 18 '22 at 10:28
  • You don't have to test both sqlcode and sqlstate, just test sqlstate, I think the code should be `IF SQL.Error = "23505" THEN` – nfgl Feb 18 '22 at 17:06
  • Hi, I used sSQLState = "23505". and it is working. Thank you very much for help. – Rohini Kumari Feb 21 '22 at 10:47

0 Answers0