Skip to Content
0

SAP PO - Dynamic SQL query for JDBC adapter

Oct 14, 2016 at 07:08 AM

348

avatar image

Hello All,

Currently i am working on SAP PO 7.5 version (single stack) implementation project.

Where business have below mentioned requirement.

Requirement:

Currently they are using concept of 'DBCon' and 'DBLink' of ABAP to get info at runtime from third party database. Here User feeds data in SAP Form, where near textbox there is one button which provide data at runtime to user by fetching data from third party database.

Button Screenshot for example:

Now they need to migrate this scenario to SAP PO system.

Scenario :

1) For example user needs data of 'company site', than company site info will be passed as request from SAP PI using (proxy/rfc) to third party database (JDBC), and as a response message 'company site address' will be passed to ECC system. This scenario will be synchrounous.

Problems/Issues:

1) Here user can pass 'A*' value than PI system should give All site info which starts with 'A'.

2) Here user can pass no value than PI system should give all site info from database.

Concerns:

1) For first problem, sql query at runtime will be like as below:

Select Sitemapaddr from Table where sitemap like 'A*'

2) For second problem, I need to read whole table data for sitemap, so query like below:

Select Sitemapaddr from Table.

So at runtime how i can manage this scenario?

Can you please help me as i am new to JDBC scenario.

Regards,

Karan Kholakiya

4.jpg (11.0 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Evgeniy Kolmakov Oct 14, 2016 at 07:36 AM
3

Hi Karan!

In your case you can use "SQL_QUERY" action for receiver JDBC message format:

<StatementName6>

<anyName action=” SQL_QUERY” | “SQL_DML”>

<access>SQL-String with optional placeholder(s)</access>

<key>

<placeholder1>value1</placeholder1>

<placeholder2>value2<placeholder2>

</key>

</anyName >

</StatementName6>

In <access> tag you put your dynamic SQL query, which can be sent within your request message.

If you want to use any additional parameters for your query, you put it in your query as placeholders and set values for it in <placeholder> tags:

<StatementSelect>

<TestQuery action=”SQL_QUERY”>

<access>Select * from Table1 where ID = $pl_ID$</access>

<key>

<pl_ID>00023</pl_ID>

</key>

</TestQuery >

</StatementSelect>

http://help.sap.com/saphelp_srm40/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.html

Regards, Evgeniy.

Show 7 Share
10 |10000 characters needed characters left characters exceeded

Hello Evgeniy,

Thanks for info....!!!

As you said 'SQL Query/DML' will be helpful for reading data.

But i have following doubts:-

1) This scenario will be Synchronous?

2) If synchronous what will be response message structure?

Regards,

Karan Kholakiya

0

Hi Karan!

1. Yes, since you use receiver JDBC adapter for performing the query, it acts in synchronous mode.

2. For request message structure:

<MT_GetData>
<StatementSelect>
<TestQuery action=”SQL_QUERY”>
<access>Select * from Table1 where ID = $pl_ID$</access>
<key>
<pl_ID>00023</pl_ID>
</key>
</TestQuery >
</StatementSelect>
</MT_GetData>

response message structure should look like:

<MT_GetData_response>
<StatementSelect_response>
<row>
<DB_Field_Name_1>Value_1</DB_Field_Name_1>
...
<DB_Field_Name_n>Value_n</DB_Field_Name_n>
</row>
<row>
...
</row>
</StatementSelect_response>
</MT_GetData_response>

Regards, Evgeniy.

0

Hi Evgeniy,

i have a similar requirement but I need to fetch data from multiple tables using the JDBC adapter. I have tried to put the query in the access structure but i am getting SQL error .

Please check the below question which i have raised and if you can help.

https://answers.sap.com/questions/120995/sql-query-in-receiver-jdbc-adapter-error-ora-00911.html

Thanks

Husain

0

Hello Evgeniy,

I need one more help with the same scenario with action 'SQL_Query'.

Now i want to perform error handling on the similar scenario.

Below requirements need to fulfill:-

1) If Database Connectivity issue arise than in response message error should pass.

2) As per request message, if no records found than message should pass as 'No records found'.

In below blog Mr. Maheswarareddy Konda stated Fault message structure, is it fine to use?

Jdbc fault message handling

Please provide your inputs how to perform error handling for JDBC synch scenarios.

Regards,

Karan Kholakiya

0

Karan,

1.) If Database Connectivity issue arise than in response message error should pass.

Connectivity issue are System ack and i dont think you can send system ack back to source system.

2.)Why do you want to use fault data here ? just simple have a logic in your response message to see if there are records or not if not pass the required message in message mapping only.

Br,

Manoj

0

Hello Manoj,

1) Currently there is one Automated job is running in SAP ECC system, which executes 9 different programs in sequence, here in programs currently data is fetched from other databases by means of DBLINK or DBCON .

2) Now these scenario we need to replace with SAP PI- JDBC adapter instead of DBLINK or DBCON.

3) So here by means of program data get fetched, if connectivity issue is arise then it will retry for some time and job get failed and it goes to other step.

4) Here SAP ECC directly calling other database but now it will be done by SAP PI, so here how to pass failure status in JDBC synch scenario to SAP ECC system when connectivity fails to other database?

So kindly help for this requirement.

Regards,

Karan Kholakiya

0

Karan,

The connectivity issue are system ack and i seriously doubt if this can be passed to the source or not , but as this is sync i guess the message remains in system error in ECC system in sxmb_moni(i hope it is proxy) and check if the ECC can read that error message.

Try this workaround:

1.Stop the reciever channel .

2.Trigger a message from ECC and check the status/message in sxmb_moni.

And ask abaper is they can read that error message.

Br,

Manoj

0