search

ibmdb / node-ibm_db

IBM DB2 and IBM Informix bindings for node
MIT License
188 stars 151 forks source link

SP call failing with error #955

Closed girish-jha closed 8 months ago

girish-jha commented 8 months ago

SP call failing with error

While invoking the SP we are getting the below error. The programming language for the same is COBOL. We are not getting this error with other store procs where the language is RPGLE

Error : SQL30020N  Execution of the command or SQL statement failed because of a syntax error in the communication data stream that will affect the successful execution of subsequent commands and SQL statements:  Reason Code "0x220A"("0100")"".  SQLSTATE=58009

Our parameter mapping looks like this

[
            { ParamType: "INPUT", Data: "ABCD", Length: 4, DataType: "CHAR" },
            { ParamType: "INPUT", Data: "1234567890", Length: 10, DataType: "CHAR" },
            { ParamType: "INPUT", Data: "AB", Length: 2, DataType: "CHAR" },
            { ParamType: "INPUT", Data: "A", Length: 1, DataType: "CHAR" },
            { ParamType: "INPUT", Data: "A", Length: 1, DataType: "CHAR" },
            { ParamType: "INPUT", Data: currentDate, Length: 8, DataType: "CHAR" },
            { ParamType: "INPUT", Data: currentDate, Length: 8, DataType: "CHAR" },
            { ParamType: "INPUT", Data: "ABC", Length: 3, DataType: "CHAR" },
            { ParamType: "INPUT", Data: "12345678901234", Length: 14, DataType: "CHAR" },
            { ParamType: "OUTPUT", Data: 0, DataType: "NUMERIC (14, 3)" },
            { ParamType: "OUTPUT", Data: 0, DataType: "NUMERIC (14, 3)" },
            { ParamType: "OUTPUT", Length: 14, Data: "", DataType: "CHAR" },
            { ParamType: "OUTPUT", Length: 14, Data: "", DataType: "CHAR" },
            { ParamType: "OUTPUT", Length: 3, Data: "", DataType: "CHAR" },
            { ParamType: "OUTPUT", Length: 1, Data: "", DataType: "CHAR" },
            { ParamType: "OUTPUT", Length: 1, Data: "", DataType: "CHAR" },
            { ParamType: "OUTPUT", Length: 1, Data: "", DataType: "CHAR" },
            { ParamType: "OUTPUT", Length: 200, Data: "", DataType: "CHAR" }

        ]

below is our SP head

CREATE PROCEDURE SP_NAME(IN Param1 CHAR(4) , 
                         IN Param2 CHAR(10) , 
                         IN Param3 CHAR(2) , 
                         IN Param4 CHAR(1) , 
                         IN Param5 CHAR(1) , 
                         IN Param6 CHAR(8) , 
                         IN Param7 CHAR(8) , 
                         IN Param8 CHAR(3) , 
                         IN Param9 CHAR(14) , 
                         OUT Param10 NUMERIC (14, 3), 
                         OUT Param11 NUMERIC (14, 3) , 
                         OUT Param12 CHAR(14) , 
                         OUT Param13 CHAR(14) , 
                         OUT Param14 CHAR(3), 
                         OUT Param15 CHAR(1) , 
                         OUT Param16 CHAR(1) , 
                         OUT Param17 CHAR(1), 
                         OUT Param18 CHAR(200))

Could you please suggest what is wrong? The closest we found is this https://www.ibm.com/support/pages/errorcode-4499-sqlstate58009-may-occur-against-db2-zos-v10-cm8

But we are already using the latest node-ibm_db.

bimalkjha commented 8 months ago

@girish-jha Datatype: "NUMERIC (14,3)" may be the issue as it is unknown for ibm_db. Use DECIMAL or CHAR instead. Only datatypes coded under if block here is known for ibm_db. You can try using below parameters:

[
            { ParamType: "INPUT", Data: "ABCD", Length: 4, DataType: "CHAR" },
            { ParamType: "INPUT", Data: "1234567890", Length: 10, DataType: "CHAR" },
            { ParamType: "INPUT", Data: "AB", Length: 2, DataType: "CHAR" },
            { ParamType: "INPUT", Data: "A", Length: 1, DataType: "CHAR" },
            { ParamType: "INPUT", Data: "A", Length: 1, DataType: "CHAR" },
            { ParamType: "INPUT", Data: currentDate, Length: 8, DataType: "CHAR" },
            { ParamType: "INPUT", Data: currentDate, Length: 8, DataType: "CHAR" },
            { ParamType: "INPUT", Data: "ABC", Length: 3, DataType: "CHAR" },
            { ParamType: "INPUT", Data: "12345678901234", Length: 14, DataType: "CHAR" },
            { ParamType: "OUTPUT", Data: 0, DataType: "DECIMAL" },
            { ParamType: "OUTPUT", Data: 0, DataType: "DECIMAL" },
            { ParamType: "OUTPUT", Length: 14, Data: "", DataType: "CHAR" },
            { ParamType: "OUTPUT", Length: 14, Data: "", DataType: "CHAR" },
            { ParamType: "OUTPUT", Length: 3, Data: "", DataType: "CHAR" },
            { ParamType: "OUTPUT", Length: 1, Data: "a", DataType: "CHAR" },
            { ParamType: "OUTPUT", Length: 1, Data: "a", DataType: "CHAR" },
            { ParamType: "OUTPUT", Length: 1, Data: "a", DataType: "CHAR" },
            { ParamType: "OUTPUT", Length: 200, Data: "", DataType: "CHAR" }
        ]

Thanks.

bimalkjha commented 8 months ago

I hope updating the code as suggested above have solved the issue. Closing it now. Thanks.