IBM i Global

 View Only
  • 1.  IBM i REST API using a SQL Procedure

    Posted Wed March 23, 2022 10:06 AM
    Edited by Jeremy Bowling Thu March 24, 2022 10:18 AM
    *** SOLVED ***
    SEE BELOW



    I am trying to call a fairly simple SQL store procedure using a REST API. I have tested the procedure using Run SQL Scripts and it works without any issues. A new row gets added to my table when I call the procedure. 

    call web18.shield_addquote('email info', 'quote detail', 'someemail@gmail.com')

    When I setup the REST API as a POST, the IBM i web interface recognizes the procedure and automatically adds the 3 parameters I need. I am making sure that when I call the API using Postman that the names of the parameters match exactly what the IBM i provided me. Even after making sure everything matches I get the following error stating that the parameters don't match. I have also checked and made sure the permissions were correct for the proc and the table. 

    { "SQLStateInfo": { "rowsAffectedCounts": null, "SQLError": { "SQLState": "42884", "SQLCode": -440, "message": "[SQL0440] Routine SHIELD_ADDQUOTE in WEB18 not found with specified parameters." }, "SQLWarnings": null } }

    ** UPDATE **
    So I thought I would approach the problem a different way. I put just a simple insert on the API call and then I created a trigger that would perform the rest of the functionality (send an email). I tested and again adding data to the table from outside the API works and the trigger fires. When I perform the same using the API I get the following:
    { "SQLStateInfo": { "rowsAffectedCounts": null, "SQLError": { "SQLState": "09000", "SQLCode": -723, "message": "[SQL0723] SQL trigger SHIELD_NEW_QUOTE in WEB18 failed with SQLCODE -443 SQLSTATE 38501." }, "SQLWarnings": null } }


    ------------------------------
    Jeremy Bowling
    ------------------------------


  • 2.  RE: IBM i REST API using a SQL Procedure

    IBM Champion
    Posted Wed March 23, 2022 09:03 PM
    Edited by Satid Singkorapoom Wed March 23, 2022 09:08 PM
    Are you running IBM i 7.3 or 7.4 with a sufficient level of HTTP Group PTF as indicated in this article : Tutorials - Creating REST APIs Based on SQL Statements ?   If so, reading this article may give you an idea on how to do this successfully.

    You will also find more articles on using SQL from IBM i REST by Googling with "ibm i rest sql". 

    ------------------------------
    Satid Singkorapoom
    ------------------------------



  • 3.  RE: IBM i REST API using a SQL Procedure

    Posted Thu March 24, 2022 09:53 AM
    Thanks Satid. 

    I found the issue. It was a security issue even though I gave permissions to QWSERVICE. Even with full access to everything QWSERVICE would not work. 

    Using a different service user resolved the problem. It's crazy to me that the system could see the procedure and pull in the correct parameters without any issues is the security wasn't correct. The SQL Errors that were thrown just completely confused the situation.

    ------------------------------
    Jeremy Bowling
    ------------------------------



  • 4.  RE: IBM i REST API using a SQL Procedure

    Posted Thu March 24, 2022 10:07 AM
    The errors being returned on the failed Stored Procedure call indicate that either the wrong number of parameters are being passed or the parameter data types don't exactly match the values being passed.  

    If you start an SQL Performance Monitor, the Monitor will capture the SQL CALL statement that is generated and executed along with the parameter values.  As shown below, make sure you specify the Error filter on the SQL monitor to minimize the amount of data collected by the Monitor.



    ------------------------------
    Kent Milligan
    ------------------------------



  • 5.  RE: IBM i REST API using a SQL Procedure

    Posted Thu March 24, 2022 10:20 AM
    Thanks Kent,

    Those errors were actually what threw me off for so long. I was chasing the wrong thing. It was actually a permission issue. I created a new user account and assigned that account to my API and everything is working now.

    ------------------------------
    Jeremy Bowling
    ------------------------------