Friday, April 15, 2016

#487 ICS 16.2.1. DB adapter (INSERT)

Introduction

Not much change from the 15.x releases.

In this post I detail an example of leveraging the on-prem DB adapter
to INSERT a row into a DB table.

I also look at some troubleshooting, in this case PRIMARY KEY Violation.

Simple Integration

Here is my simple integration -









My source is based on an abstract customerService wsdl.
My target leverages the on-prem DB adapter.

It is configured as follows -




































The adapter config in the integration is as follows -











The Mapping -














The Test -















The Result -




















Troubleshooting

Now, what happens if we drop the customers table?










Firstly, let's look at the design time -













The query is still syntactically correct.
So no check is made against the DB to see if the table exists.

Now here is a second integration I am testing -











As you can see, CreateCustomer is expecting a response,
but none is returned from the Invoke on NC-DB.

So, currently, in order to get the status of the attempted DB insert,
one can embed the INSERT in a PLSQL Stored Procedure of Function.

Here is a simple example of such a Function -

   CREATE OR REPLACE FUNCTION f_ins_cust (
      in_custNr  IN  VARCHAR2,
      in_custName  IN  VARCHAR2,
      in_custSurname  IN  VARCHAR2)
RETURN VARCHAR2
IS
v_status varchar2(255);
BEGIN
      v_status := 'Success';
      INSERT INTO CUSTOMERS VALUES (in_custNr, in_custName, in_custSurname);
      Commit;
      RETURN v_status;
      exception
        when others
          then
            RAISE;
               
END;
  /

Here is the ICS integration that calls the above PLSQL function -







Here is the DB adapter Invoke configuration -








Request Mapping -








Response Mapping -








I activate and then test.





I now re-execute the test with the same payload.
I expect a Primary Key violation error - and that is what I get.













I can also download the ICS logs and see the error there -








































Just in case you experience intermittent connectivity issues with your on-prem DB,
just go to the DB connection menu and click Refresh Metadata -
thanks to Umesh, for pointing this out.





No comments: