#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.