How to pass values at runtime in JDBC - XI - File scenario

[Former Member]

Hi friends,

In my scenario, data is coming from R/3 and i need to filter records from oracle database based on this data. There are 4 database tables that need to be queried using 2 select statements. The resultset after the execution of query will be mapped to the target flat file structure.

here are my queries:

1) Can I avoid BPM as data needs to be collected from the two database calls which involves two sender JDBC adapter instances with only one target structure?

2) Can I use stored procedure in this scenario? If yes, than how to pass values to stored procedure at runtime via sender JDBC adapter.

Thanks and regards,

Nitin aggarwal.

[Former Member]

Hi,

Please look at this thread which says stored procedure is not supported for oracle.

Regards

Vijaya

[bhavesh_kantilal]

Hi Nitin,

<i>In my scenario, data is coming from R/3 and i need to filter records from oracle database based on this data</i>

Am not sure.. Can you please elaborate on this?? How is data from R3 related to your Oracle Database?

Next, ASFAIK, you cannot use a Sender JDBC adapter to call an oracle Stored Procedure. Reason being that Oracle Stored Procedures return a Cursor and not a resultset.

So, either you will have to use a Sender JDBC adapter with a single Select query ( can't you make a single Select Statement instead of multiple ones)

Or, you can use the receiver JDBC adapter to make the Select to the database using a BPM.

Regards,

Bhavesh

[Former Member]

Hi Bhavesh,

Thanks for replying back...

Let me elaborate it more.. I am fetching data from SAP and later based on that data i am fetching records from the underlying database. Since there are 4 tables involved say A,B,C,D. A and B can be joined and similarly C and D can be joined. That's the reason 2 select queries are required. Now the data from all the 4 tables need to pulled and mapped to the target structure. So i was thinking if i can avoid BPM in this scenario. Let me know if u can suggest some other alternative.

Regards,

Nitin.

[bhavesh_kantilal]

Hi Nitin,

Are you fetching data from your SAP tables or an extranl Database?

if you are going to fetch data from an SAP table, you need not use a JDBC adapter , you can always use an RFC Lookup in your mapping to get the data from your SAP tables table.

Unfortunately, i still do not get the entire scenario.

Regards,

bhavesh

[Former Member]

Hi Bhavesh,

Thanks for the reply..

I am fetching data from SAP as well as from external database. Data extracted from external database has to be filtered out based on the data fetched from SAP. data can be pulled from SAP using client proxy or RFC adapter but i am not bothering about it... Problem is in fetching data from external database as there are multiple tables involved and i cant have multiple select statements in a single JDBC adapter instance. So i want to know if i can write multiple select statements in the stored procedure and later executing the stored procedure from the sender JDBC adapter instance and if this is possible than how to pass on values at runtime to the stored procedure.

Thanks and Regards,

Nitin Aggarwal.

[Former Member]

Hi Nitin,

"..So i want to know if i can write multiple select statements in the stored procedure.."

Read the below line that is mentioned in the SAP help documentation fro Sender JDBC adapter:-

<i>Specify an SQL EXECUTE statement to execute a stored procedure, which contains exactly one SELECT statement.</i>

I dont think it can be achieved...but there must be some workaround for this. You can probably use a join statement.

Read this, again from the documentation:-

<i>The expression must correspond to the SQL variant supported by the relevant JDBC driver. It can also contain table JOINs.</i>

Regards,

Sushumna

[bhavesh_kantilal]

Hi Nitinn,

<i>am fetching data from SAP as well as from external database. Data extracted from external database has to be filtered out based on the data fetched from SAP.</i>

Wouldn't you be using a BPM in this case?

If you are using a BPM, then you can also Select Data from your DB using a Receiver JDBC adapter. Just check my blog on how you can use a Receiver JDBC adapter to collect Data using a Receiver JDBC adapter,

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

Also, I dont think this will be possible using a Single Sender JDBC adapter . So, you will have to go for a BPM.

Whether you use a Sender or receiver JDBC adapter depends on the complete functionality of what you are trying to achieve.

Regards,

Bhavesh

[Former Member]

Hi Bhavesh,

Thanks for the blog... Its indeed informative and will help me in problem resolution.

Thanks and Regards,

Nitin.