DataType definition for when using JDBC sender adapter

[Former Member]

I would like to use JDBC adapter as sender and I have to get the data from TWO tabels from Oracle database. One table order header and other is order detail. Can anyone explain how can I define the datatype in this case and also the query to used in sender communication channel.

Thanks

[former_member91687 · Accepted solution]

Hi Venkat,

Is it that you want both your tables to be queried for information using a single jdbc adapter?, because multiple Selection queries (different select queries) are not possible using a single JDBC sender adapter.

The SQL statement field will contain the SELECT statement and once your SQL statement is executed, UPDATE statement is executed. This is done so that records that have been selected by the SQL statement should not be selected again as the JDBC sender adapter will poll over the database for every poll interval.

The datatype definition would depend on the fields in your table.

Another point to be noted is that the QoS should be set to EO or EOIO since the message by definition is asynchronous. You must also have the appropriate jdbc driver deployed in your system.

In case you have any other doubts on how to configure the sender jdbc adapter, this link will help you out.

http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/frameset.htm

Regards,

Chandra

[Former Member · Accepted solution]

Thank you Chandra,

But I want both tables(header and deatil) to be queried for information using a single jdbc adapter. In this case , how I have to define the Datatype..? and also how can I write my SQL query in communication channel?

I will appreicate you help,

Thanks

[moorthy · Accepted solution]

Hi,

Pls go thru this help.. It talks about Data Type format etc

http://help.sap.com/saphelp_nw2004s/helpdata/en/22/b4d13b633f7748b4d34f3191529946/frameset.htm

Regd. Query : SELECT <field1>,<field2>......from table where <condition>

Hope this helps,

Regards,

Moorthy

[Former Member]

Hi Venkat,

I had also got the same requirement using the JDBC Sender adapter with header and line items. Kindly let me know how u had acheived this particular requirement.

ur suggestion would be highly appreciated.

regards,

kittu

[Former Member]

Hi,

You could use a Join statement.Both these tables must have a common field on which you could perform the join.

You can now create a data type, depending on the fields that you are going to retrieve using the select query.

regards,

Smitha

Message was edited by: Smitha Rao

[bhavesh_kantilal]

Hi Venkat,

<i>I have to get the data from TWO tabels from Oracle database</i>

ASFAIK this is not possible using a single JDBC sender adapter.

There are 2 options you get when trying to select rows from a Database table.

Use a SELECT query directly or call a Stored Proceudre.

But, <b>both of them allow only one select statement to be executed and take the results of only one select statement.</b>

What you want here is that you want data to be selected from different database tables and combined in XI.

Guess BPM is to be used in this case.

Regards,

Bhavesh

[bhavesh_kantilal]

Hi Venkat,

Think the option suggested by Smitha about using JOIN might do thr trick.

You can have your select statement as,

select emp.name,dept.deptno from emp,dept

and so on.

Regards,

Bhavesh

[former_member91687]

Hi Venkat,

You can get information using <b>joins</b> in this case then, the syntax should be something like this:

SELECT field1, field2

FROM first_table

INNER JOIN second_table

ON first_table.keyfield = second_table.foreign_keyfield

I've not tried using joins, but the help library says you can use it, so try it out and see if it works.

Have a look at these blogs:

https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/1725 [original link is broken] [original link is broken] [original link is broken] [original link is broken] [original link is broken] [original link is broken] [original link is broken] [original link is broken]

/people/sriram.vasudevan3/blog/2005/02/14/calling-stored-procs-in-maxdb-using-sap-xi

Regards,

Chandra

[moorthy]

Hi Venkat-

If your JDBC Adapter as a Sender, then you can create a data Type of Flat structure or Header and Substructure way as you required.

And in the Communication Channel, you can query like ordinary SQL statements.

http://help.sap.com/saphelp_nw2004s/helpdata/en/22/b4d13b633f7748b4d34f3191529946/frameset.htm

/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

Just to get more-

/people/saravanakumar.kuppusamy2/blog/2005/01/19/rdbms-system-integration-using-xi-30-jdbc-senderreceiver-adapter

Hope this helps,

Regards,

Moorthy

[Former Member]

Hi Venkat,

Please follow this weblog:

/people/alessandro.berta/blog/2005/10/04/save-time-with-generalized-jdbc-datatypes