Skip to Content
0

Multiple SQL Statements in JDBC Sender Communication Channel in SAP PI/PO

Jul 31, 2017 at 07:14 PM

264

avatar image

Hi All,

I want to execute below SQL statements in JDBC Sender communication channel:

1) Delete Temp Table.

2) Select top 1000 records from Main Table and insert into Temp Table.

3) Select all entries from Temp Table.

4) Update Main Table status field for all entries in Temp table.

5) Delete Temp Table.

We are facing issues while updating records by using top 1000 records command, So we came up with above five steps.

Is it possible to do above five steps in SAP PI JDBC Sender Communication Channel?

If yes can you please provide steps. If it is possible using Stored procedure can you please help me on the same.

Thanks.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Evgeniy Kolmakov Aug 01, 2017 at 09:40 PM
0

Hi Anvesh!

As Inaki has already said, you can use stored procedure for your requirement.

You use SQL statement like:

SELECT fld1, fld2,... FROM StoredProcName().

Inside that stored procedure select with update is performed and result recordset is returned.

In this case you don't need to use update statement in sender adapter's settings.

Regards, Evgeniy.

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

Thanks Evgeniy. I will try with Stored Procedure. Appreciate your help.

0

Hi Evgeniy,

JDBC sender adapter is communicating with Microsoft SQL Server DB.

DB team has given two stored procedures (One for Select and another for update) and they are asking to execute.

I have made below configuration in JDBC sender channel.

- Query SQL statement : EXECUTE [dbo].[Extract Stored Procedure]

- Update SQL statement : EXECUTE [dbo].[Update Stored Procedure]

Will this changes work or am I doing any thing wrong here. Please help me.

Thanks.

0

Hi Anvesh!

I think you could try with this config.

Regards, Evgeniy.

0
Iñaki Vila Aug 01, 2017 at 06:33 AM
1

Hi Anvesh,

First of all you should think if you really need a TEMP table in your scenario.

If your DB vendor is Oracle i usually go with a stored procedure, doing it like i wrote in this thread https://archive.sap.com/discussions/thread/3706865

Regards.

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

Hi Iaki,

Thanks for your quick response. We are using SAP PO 7.4 and scenario is JDBC -> SAP PO -> PROXY. Using JDBC sender adapter I have to extract data from third party DB and update the status back to DB. I am using below statements for extracting and updating the status.

- select top 10000 * from TableName where RowStatusID=1

- update top(10000) TableName set RowStatusID=7 where RowStatusID=1

Update statement is not working as expected and it is updating the status field other than the records selected.

DB team is ready to write two stored procedures and in SAP PO how to use them. Can you please help me the steps in SAP PO to use stored procedures and eliminate the above two statements. Appreciate your help.

Thanks.

0

Thanks Ianki. I will try with Stored Procedure. Appreciate your help.

0