JDBC Adapter Reciever

[Former Member]

Hello,

When I creating datatype for JDBC Receiver adapter, I need to have SQL statement as follows.

Select * from employee_tbl where employee_id <b>in</b> (1, 8, 176, 49285, 935, 35478)

Now my question is how do I define datatype to generate IN as part of XML structure.

I appreciate you help.

Thank you,

Balaji

[Former Member · Accepted solution]

Raj,

I looked at Bhavesh's posting before posted my question. Let me rephrase my question.

If I define data structure as per Bhavesh's positng and add UDF for keys (as per your earlier response), UDF last line of code looks like as follows.

<u>result.addValue("EMPNO IN("insert.toString()")");</u>

Does JDBC request message structure looks as follows?

JDBC request message,

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_JDBC_SELECT_JDBC_REQ xmlns:ns0="http://JDBC_SELECT">

<STATEMENT>

<TABLENAME ACTION="SELECT">

<TABLE>EMPTEST</TABLE>

<ACCESS>

<EMPNO></EMPNO>

<NAME></NAME>

</ACCESS>

<KEY>

<u>EMPNO IN(28347,898,176,894)</u>

</KEY>

</TABLENAME>

</STATEMENT>

</ns0:MT_JDBC_SELECT_JDBC_REQ>

Please calrify if this is correct?

Thank you,

Balaji

[justin_santhanam · Accepted solution]

Balaji,

I really don't know whether u've seen my URL or not. You don't need to create strutcture like what u've given.

your Struture must look like

<root>

<stmt>

<Emp_tbl action="SQL_DML">

<access><b> Your SQL Query</b></access>

</Emp_tbl>

</stmt>

</root>

Your SQL Query is generated by UDF which u used. Since its synchronous, you will get the response as in Bhavesh's blog

<stmt_response>

<rows>

....

...

...

</rows>

</stmt_response>

I hope it clears ur doubt now, if not reply back.

Best regards,

raj.

[Former Member · Accepted solution]

thank you for the help

[Former Member]

Raj,

Thank you for your respose, I am not clear about how to define Datatype for the JDBC request message. When I create messge, if I return whole SELECT statement from UDF, Select * from employee_tbl where employee_id in (1, 8, 176, 49285, 935, 35478), Why do I need to have elements like statment, table, action, tablename and Keys as part of Datatype for the JDBC request message.

Please let me know.

Thank you,

Balaji

[justin_santhanam]

Balaji,

Please have a look at Bhavesh's blog /people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

Take the structure from his blog.

Best regards,

raj.

[Former Member]

I have RFC passing an array which will have 1...n values of employee_id's. Please let me know if you need more informaiton.

Thank you,

Balaji

[justin_santhanam]

Balaji,

Have u seen my previous reply, the URL which I gave , it exactly suits ur reqmt. You need to make slight modifications in the UDF. Please let me know if you want the exact code as per ur reqmt.

Best regards,

raj.

[Former Member]

Thank you all for the responses, There is an issue that I would not know how many employee_id's I am going to passing in at the time wtriing query. All know is it could be 1 or many as follows.

Select * from employee_tbl where employee_id in (1, 8, 176, 49285, 935, 35478)

or

Select * from employee_tbl where employee_id in (1, 8, 176, 49285)

or

Select * from employee_tbl where employee_id in (1, 935, 35478)

How I would do that in the receiver adapter?.

Thank you,

Balaji

[Former Member]

How do you determing the employee_id's to pass for the JDBC adapter?

[justin_santhanam]

Balaji,

Can u give us the source structure with occurrences?

Best regards,

raj.

[justin_santhanam]

Balaji,

Create structure like

http://flickr.com/photo_zoom.gne?id=549186611&size=o

In the UDF similarly:

http://flickr.com/photo_zoom.gne?id=549186651&size=o

If you have doubt in UDF then let me know.

Best regards,

raj.

[Former Member]

Hi Balaji uppalapati

create a message mapping with action "SQL_QUERY".

Check the link http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

The following is an example.

[code]<?xml version="1.0" encoding="UTF-8"?>

<ns1:Find xmlns:ns1="urn:find">

<smtp>

<dbTable action="SQL_QUERY">

<table>TABLE_NAME</table>

<access>Select * from employee_tbl where employee_id in ( '$C1$', '$C2$')</access>

<key>

<C1>1</C1>

<C2>8</C2>

</key>

</dbTable>

</smtp>

</ns1:Find>[/code]

[Former Member]

Balaji,

If you want to select data from SQL database, then you shd configure Sender Adapter not receiver adater.

In sender adapter In Query SQL Statement you can write query diretly.

Hope this will ans your query.

Nilesh