JDBC->XI->File Scenario

[Former Member]

Hi ,

I have this scenario where i need to pick the records from an Oracle staging table and i should insert the same header and item records in to a Flatfile which will be used to create records in SAP. The records in the oracle table would be of header and item records with an identifier for each of these header and items.

can any one throw some light on how to progress on this scenario, like what wud be the structure my source data type to hold the item and data records & the select statement to be written in the JDBC adapter communication channel etc....

Thanks in Advance,

Kittu.

[bhavesh_kantilal · Accepted solution]

Hi,

To access any Database from XI, you will have to install the corresponding Driver on your XI server.

To install oracle driver, just check this link,

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/3867a582-0401-0010-6cbf-964...

The details that have to be entered while adapter configuration,

jdbc:oracle:thin:@<IP adress>:<listener port>:<instance name (database name)>

Driver oracle.jdbc.driver.OracleDriver

Meannwhile, you have to give a Select statement for your Sender JDBC adapter. This is used to seelct the rows from the table.

You also have an update statement. This is used to update the rows that have been selected. Just check this link for the info,

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

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi Bhavesh,

Thanks for the reply.

Once i give the select statement like "select * from <table name>." in the JDBC Communication channel configuration will it pick all the records irrespective of the header and the items?

coz my requirement is such that it shud pick all the records in the staging table at that instance ; when the interface is run very two hours.

Please respond...

Thanks ,

Kittu.

[bhavesh_kantilal · Accepted solution]

Hi kittu,

The JJDBC adapter will select all the rows from your database that satisfies the where clause of your select query.

Eg:

Select ename , empno from emp where age>20

this will return all rows that satisfy the select query.

<i>coz my requirement is such that it shud pick all the records in the staging table at that instance ; when the interface is run very two hours.</i>

just give select col1,col2 from table.

Set the polling interval to 2 hours and it will work just like you want,

Regards,

Bhavesh

[former_member91687]

Hi kittu,

It depends on you select query and the parameters involved. The system converts the table resulting from the query SQL statement into a valid XML document and sends it to the Integration Engine. For instance, the doc would look something like this.

<resultset>

<row>

<column-name1>column-value</ column-name1>

<column-name2>column-value</ column-name2>

<column-name3>column-value</ column-name3>

</row>

<row>

<column-name1>column-value</ column-name1>

<column-name2>column-value</ column-name2>

<column-name3>column-value</ column-name3>

</row>

</resultset>

So you have to create the datatype, message type and message interface to match this structure.

Regards,

Chandra

[Former Member]

Hi Kittu,

Assumptions:

If your data from staging table contains data for both header and detail.

There is identifier in the row which says the data is header or trailer:

Create a structure

<data>

<Header>

<Records>

</Records>

</Header>

<detail>

<Records>

</Records>

</detail>

now check where the data is for header and put it in header and the data for detail put in the details.

Regards

Vijaya

<Data>

[bhavesh_kantilal]

hi kittu,

Just make sure that the name of your column of your Database and theat of the data type match and are also of the same type.

Regards,

Bhavesh

[Former Member]

Hi Vijaya,

Thanks for responding.

Let us think that Iam taking some fields from the Oracle table.

Let them be

Header : material , desc , Payment Terms.

Item Detail : Item No, Material Grp, Desc, Storage Location.

Now i Will Create The Data Type in XI Like

Header

Material

Desc

Payment Terms.

Detail

Item No

Material Grp

Desc

Storage Location

So the data which iam picking from the Oracle Table will Directly come and gets populated in the Source data type as the fileds iam picking and the elements in the Data Type are the same.

Is that what u mean to say like creating <Statement-Element> and <Elements>.

Kindly Clarify.

Thanks,

Kittu.

[Former Member]

Hi Kittu,

The data structure I described is your receiver side data type.

The XML data send from your JDBC adapter looks like this:

<resultset>

<row>

<column-name1>column-value</ column-name1>

<column-name2>column-value</ column-name2>

<column-name3>column-value</ column-name3>

</row>

<row>

<column-name1>column-value</ column-name1>

<column-name2>column-value</ column-name2>

<column-name3>column-value</ column-name3>

</row>

</resultset>

Now the data will be contained in your rows tag. So if u can identify from the first column as to whether the data is header or detail, u can map it into the receiver structure as described by you.

Hope this helps.

Regards

Vijaya

[former_member91687]

Hi Kittu,

Yes you can give that, make sure the receiver elements of the information match that of the database.

The polling of the database for information will take place depending on the poll interval you set in the JDBC adapter, you do not have to call it explicitly.

Regards,

Chandra

[Former Member]

Hi Chandra,

I got that, But in My repository should i construct the data type in such a way that it matches the records that were selected from the oracle table or else is there any way in which we need to map these values from the table to be populated into the message type which we will be used in our mapping..

Kindly Advice....

Thanks a lot,

Kittu.

[Former Member]

Hi,

You would need to construct the values in your data type, under the header <statementname_response> for each statement element.

Make sure that the call is synchronous, else the IS would not be able to access the response document.

Regards,

Smitha.

[former_member91687]

Hi Kittu,

A similar scenario has been discussed in this recent thread, you would also find references to the blogs that can help you model your scenario, select queries, required drivers etc, in this thread, please go through it.

Regards,

Chandra