Sender JDBC Adapter

[Former Member]

Hi all,

I am new to JDBC adapter and have several doubts and concerns about it. So I hope you experts can help me.

My scenarios are JDBC to IDOC.

XI needs to:

1) access the table in the DB,

2) collect some data from it,

3) update one field in the table (afterwards, this will mean that the registry has been previously used -> this is because we want to update a field in which we can show that the record has been processed before)

4) map the collected data to an IDOC

5) send the IDOC to SAP

Is it possible to achieve the 3) step only with one SENDER JDBC comm channel?

Can we ellaborate this without using BPM?

What about the data integrity if some tables are relationed?

Is there any blog that may show this kind of scenario?

The database used is ORACLE. Could you please let me know which is the propper driver i have to install?

Could you please guide me through this? Any help will be rewarded.

Thanks in advance and best regards,

David

[justin_santhanam]

Domingo,

Also go thru this,

/people/yining.mao/blog/2006/09/13/tips-and-tutorial-for-sender-jdbc-adapter

https://www.sdn.sap.com/irj/sdn/wiki?path=/display/xi/jdbcTOJDBC&

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

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/70ffd890-0201-0010-708f-d5dad2df...

raj.

[VijayKonam]

Step 3 is possible by the sender adapte. You can specify 2 queries. Check the JDBC Sender adapter documentation on Help portal. you have to deploy Oracle JDBC drivers using the SDM tool in XI. You can get detailed info in this forum.. search.

As per integrity etc, since oracle does not support stored procedures, You have to carefully desing the table that XI accesses.

VJ

[Former Member]

Hi,

a, yes, use update statement for this in sender adapter (CC) field

b, yes

c, think about this in DB design (usually there is a "interface" table created for this purpose), XI has nothing to do with this

d, sure, use search field in SDN portal

rgds,

Olian

[justin_santhanam]

Domingo,

Yes , the first three steps will be achieved by Sender Communication Channel of JDBC adapter.

The following paramters of Sender Comm.channel will answer your question

>1) access the table in the DB,

Connection *

>2) collect some data from it,

Query SQL Statement*

> update one field in the table

Update SQL Statement*

You can use OJDBC14.jar to connect to Oracle.

raj.

[Former Member]

Hi there,

First of all, thanks a lot for your valuable input.

Right now, i am sure i can collect data and update fields all at one transaction using sender JDBC.

1) But,... what if i need to collect the data and update TWO COLUMNS? Is it possible?

I mean, i need to gather (SELECT) the info and set (UPDATE) the date and time at which the info was taken from the table.

If the record had not been processed the date and time columns would remain blank.

It should be sthg like:

SELECT * FROM t_provider WHERE DATE = ' ' AND TIME = ' '

UPDATE t_provider set DATE= sy-datum WHERE DATE=' '.

UPDATE t_provider set TIME= sy-uzeit WHERE TIME=' '.

2) sy-datum and sy-uzeit are SAP variables. Do you know if ORACLE may provide us with same values?

3) Besides, what did you mean with "oracle does not support stored procedures"???

4) In addition, what kind of transaction isolation level may i use? May i use serializable? i think this works fine for linked tables DB.

Thanks a lot in advance,

Best regards,

David

[VijayKonam]

> 1) But,... what if i need to collect the data and update TWO COLUMNS? Is it possible?

>

> I mean, i need to gather (SELECT) the info and set (UPDATE) the date and time at which the info was taken from the table.

> If the record had not been processed the date and time columns would remain blank.

> It should be sthg like:

>

> SELECT * FROM t_provider WHERE DATE = ' ' AND TIME = ' '

>

> UPDATE t_provider set DATE= sy-datum WHERE DATE=' '.

>

> UPDATE t_provider set TIME= sy-uzeit WHERE TIME=' '.

U have choice to write only 2 queries. One select and one update. You can not write 2 update statements. How ever, if you can club these 2 in to one query, it might be possible.

> 2) sy-datum and sy-uzeit are SAP variables. Do you know if ORACLE may provide us with same values?

Execute these queries in Toad or some where by connecting to the Oracle database. If they execute there, then there should not be any problem. One thing to note here is, the queries mentioned in the channel are not going to be executed by XI but the Oracle it self.

> 3) Besides, what did you mean with "oracle does not support stored procedures"???

SQL serve supports stored procedure, where in you can write your business logic and execute as many queries inside as you want. I do not think ORACLE has this concept. I never worked on Oracle.

> 4) In addition, what kind of transaction isolation level may i use? May i use serializable? i think this works fine for linked tables DB.

Think so. Not exactly sure. The documentation should help you.

http://help.sap.com/saphelp_nw04/helpdata/en/start.htm - Search for jdbc adapter.

VJ

[Former Member]

Hello mates,

Ok i will try doing as you suggested.

BTW, i have another doubt. I want to extract data that is stored in some columns within a DB. So i have to create a Data Type that contains an structure with some fields. This fields have to have equal name (case sensitive) that the columns in the DB. i think that up to this point i am right....i hope

BUT, the thing is that i only want to extract some 'columns', not all of them, may i have to declare all the colums in my Data Type or only the ones (columns) from which i will retrieve?

I suppose i only have to declare the ones i will need but will wait for your confirmation

Thanks a lot in advance and best regards,

david

[Former Member]

>> suppose i only have to declare the ones i will need but will wait for your confirmation

Confirmed David

Regards,

Jaishankar