How do I send receiver data to query in Oracle thru PI

[Former Member]

Hello All,

This is extension of following thread, but still looking for some solution to complete my scenario.

The scenario follows like below:

File (dummy for invoke PI )--->Sender File/PI--->Javamap(at OM)-->call-->RESTful Service---->Javamap -->Response (at OM)--->PI/MM-->input to Oracle(select query)--->Oracle response-->PI-->File/Receiver(NFS)

We achieved until getting the Response from RESTful service and later we came to know that database was VIEW, otherwise experts suggestions I would have use the DBlookups to call the Oracle, if it is Table.

Now concerns were :

1. The database  was VIEW, so VIEW doesn't allow import the meta data. In this case I can't use JDBC receiver adapter.

2.  Since we are selecting the records from VIEW, it required DT/MT as of my knowledge. In this case the MT structure was entirely different than what WS response.

3.As per above thread, if we create Intermediate structure(JDBC_MT) similar like WS response structure, I could make WS output data as input to JDBC_MT using MM, but now the new DT/MT was should create based on Select query. Also WS response was not in my control which already  in production. So I can't change this output structure.

So please let me know how do I complete rest of the part in this scenario.

Thanks,

Gayatri.

[former_member184720 · Accepted solution]

Let me provide my views again to get experts comments..

1. The database  was VIEW, so VIEW doesn't allow import the meta data. In this case I can't use JDBC receiver adapter.

-> As i suggested you can place this view inside a stored procedure and execute that procedure from a UDF.

2.  Since we are selecting the records from VIEW, it required DT/MT as of my knowledge. In this case the MT structure was entirely different than what WS response.

-> No need to define any additional structure if you call from a UDF. something like explained in below blog

3.As per above thread, if we create Intermediate structure(JDBC_MT) similar like WS response structure, I could make WS output data as input to JDBC_MT using MM, but now the new DT/MT was should create based on Select query. Also WS response was not in my control which already  in production. So I can't change this output structure.

-> Second response address this issue i believe.

[Former Member]

Hello All,

Thanks for your inputs, finally my scenario working . I tried different options in PI but it is taking lot of time to check and test . So I have written one Java class for all , which can handle all inputs internally and give the outputs.

Now I am trying to test E2E, at this time JM working at OM level test tab and giving the correct output. No need for Directory objects.

Now we need to execute this JM  on daily one time thru PI? How do I handle?

Does it required directory objects of SA, ID, RD and RA for run this OM? With out creating directory objects/pipeline steps how we going to execute OM in runtime ?

I just want to Run this JM thru PI , then JM will takecare everything.

Thanks,

Gayatri

[former_member184720]

Hi Gayatri - You need to configure the ID objects inorder to run it in PI.

Operation mapping is just an object. Without configuration it has got nothing.

If you want to host it outside of PI then no need to java mapping n all. Just deploy the code on any server schedule some batch job

[Former Member]

Hello,

Good to hear that.

But, to invoke file request and send file response u need to create ID objects - how would u invoke ur JM, i mean u need file request which will invoke rest api + JDBC call and eventually send file response. So u have to do ID config.

And at the same time, if u want u can schedule file sender channel using ATP.

Thanks

Amit Srivastava

[Former Member]

Thanks for your inputs Hareesh and Amit.

I understand , configurations steps required in PI. Ok, lets configure directory objects.

Shell I exclude RA in this pipeline steps? because my JM will handle all steps ,call rest api+ JDBC and send the file to NFS.

SA with Sender Adapter (no input file)  --> just dummy path and file but schedule in daily ones. ---> Does it possible?

Apart from ID and RD compulsory to take my JM into Runtime.

Only I am thinking If I avoid the RA step,does it runs or not?

Hareesh,

As per our design concerns , we should use PI  to fulfill this requirement. So no chance to deploy this code in out side of the PI.

Thanks,

Gayatri.

[former_member184720]

Hi Gayatri - You need to configure the sender channel with a valid path and file should exists.

Place a dummy file and keep the channel in test mode.

If there are no files then your mapping won't be triggered/executed.

[Former Member]

Hello,

>> my JM will handle all steps ,call rest api+ JDBC and send the file to NFS

First of all let me say that u have taken a brave decision to do everything inside code...I am wondering about ur exception handling.

>>Only I am thinking If I avoid the RA step,does it runs or not?

Now coming to ur question, if u don't create RA then anyway ur JM will be executed but ur interface flow will go into error in PI - not a good option.

So what i would suggest u is to create a dummy RA and dump the response of JM to NFS location (PI file system) and ask ur BASIS team to write (2 lines of script) and schedule (it daily) to remove that unwanted file from NFS location.

Other option would be(not sure at this stage u want to check that option or not) to schedule ur Java code as a job in SAP PI. So in that case u don't need any interface or JM.

Thanks

Amit Srivastava

[Former Member]

Hi  Gayatri

Since your scenario is complex and you are trying to do it in a single scenario, I would suggest you to use BPM otherwise the troubleshooting will be very difficult in future.

Use BPM sync/async concept and complete your scenario.

This can be done in three steps

1. File sender to BPM (asynchronous ).

Here the receiver structure of BPM will be the response structure of web service. Call the java mapping in OM in this step.

2. BPM to JDBC sync call. Map the web service response structure to JDBC select query in mapping and get the response beck in BPM.

3. BPM to NFS file receiver (asynchronous ).  Map the JDBC response to file structure using message mapping.

[Former Member]

Thanks for your responses!

Lacking of my java skills, looking for simple approach , but seems to I need to extend some more java skills to complete this scenario. Right now I am working based on following link which looks like good doc for me to query VIEW.

Implementing Lookups Using DataBaseAccessor - Managing Services in the Enterprise Services Repositor...

Do you think this is good link for me to complete my scenario?  later I can embed this code to JM which I am using for WS call, so that I can use one JM for all steps.

Also can you explain me detail steps based on that link if that is feasible solution for me.

Thanks,

Gayatri.

[Former Member]

Hi,

You cannot combine both the mappings in one JM...as you need the WS Response to call the DBLookup and has to pass the output of WS as a input to the Lookup...

So better option is go for lookup in mapping step only...

gud luck

HTH

Rajesh

[Former Member]

Hello,

The link which you are referring is OK, but IMO u are complicating ur code.

The idea behind suggesting u intermediate structure is to simplify ur mapping and segregate rest WS and DB call in two 2 different steps. U can easily handle DB calls using UDF and map the output to ur file structure instead of clubbing everything in one.

But certainly, choice is urs

Thanks

Amit Srivastava

[Former Member]

Hello,

U can directly make a select query on view and get the results back from DB using a UDF.

Search SDN with "DB Lookups", u will find tons of code for the same. BTW, for lookups u don't need to create any structures in PI, there are APIs available which u directly use and call DB (using JDBC receiver adapter).

Thanks

Amit Srivastava