JDBC Adapter with variables

[Former Member]

Hi,

I have a scenario where I have 3 databases lying on a server. The databases have the same structure but with different names.

I now need to extract the same information from the different databases. So the extraction information and interfaces are exactly the same. The only difference being the database name.

Is there a way that you can send through the database name through to the adapter in a variable for the connection string, so that I can use the same scenario for all 3 databases without having to create 3 different JDBC sender adapters?

Thanks,

Miguel

[bhavesh_kantilal · Accepted solution]

Hi Miguel,

One option would be to a Db lookup in your mapping to select the data from your DB.

In this case, you will have 3 communincation channels again , but the on the basis of what you want, you can choose the communciation channel during the message mapping.

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

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi,

If you have three different tables and you need to access either one of them based on the incoming data then you can use conditional determination and route your message to the right communication channel. Here your complete interface will be the same as in Your MM, MT etc if the structure is same . Only difference being you still have three communication channel.

In the MM you can update the right table in the outbound structe depending on incoming Data.

Regards

Vijaya

[Former Member · Accepted solution]

Hi Bhavesh,

In my case, I was using a JDBC Sender Adapter.

Using the solution below, I will not be able to use the sender adapter?

I was using the sender service because I am polling the database every 1 hour, and using the returned values from the database to update BW.

So if I am using the sender adapter, I do not have a message mapping at that moment to use the DB Lookup in...

Am I correct in my thinking?

Thanks,

Miguel

[Former Member · Accepted solution]

Hi Vijaya,

My problem is in fact not different tables...

I actually have 3 different databases with the same tables which I would like to read. The queries for each of the databases are exactly the same. It is the actual databases that are different...

Thanks,

Miguel

[bhavesh_kantilal · Accepted solution]

Hi Miguel,

Yes..That is an issue . Multiple workaround's can be looked at like using a dummy file to start your interfaces , etc , but I would suggest using the Sender JDBC adapter itself with 3 different JDBC adapter polling over the 3 different Databases.

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi Bhavesh,

Yip, this is what I thought that I may need to do....but I encountered another problem....

All 3 databases are lying on the same server...so I have a business system, which has 3 communication channels as sender JDBC adapters linking to each of the databases...

All the databases have the same interface as they are exactly the same structure, but just different information. Now, when creating the sender agreement, I have the same "key" i.e. service and interface and therefore I cannot create multiple instances of the same interface...

This means that I would need to create 3 communication channels as well as 3 interfaces in order for this to work...

Any other ideas perhaps/guidelines?

Thanks,

Miguel

[bhavesh_kantilal · Accepted solution]

Hi Miguel,

unfortuately, we need to have 3 interfaces for each of the commuincation channels..!!

The other option would be to use the DB look up from mapping but, again, the problem would be to have a dummy file to start the interface. the choice has to between either of the 2 options.

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi Bhavesh,

I have decided to go with the dummy file to start the interface using the LookUp API.

I have been able to successfully get a value.

I now have a question, as this is the first time that I am using this lookup...

The ResultSet that I get back should contain 5 lines, but how to I map each of those values to a new element?

eg. ResultSet =

1

2

3

4

5

and I would like to map it to a message that looks like this:-

<Line>

<Number>1</Number>

<Line>

<Line>

<Number>2</Number>

<Line>

<Line>

<Number>3</Number>

<Line>

<Line>

<Number>4</Number>

<Line>

<Line>

<Number>5</Number>

<Line>

If I place the Lookup UDF on the Number element, I only get the last section...

<Line>

<Number>5</Number>

<Line>

If I place the Lookup UDF on the Line element, I get an error...

Do you perhaps have any guidance for me?

Thanks,

Miguel

[bhavesh_kantilal · Accepted solution]

Hi Miguel,

In the blog written by Siva , he has used an Advanced User Defined Function which will return a resultlist object.

Take a look at this link for adavnced UDF's,

http://help.sap.com/saphelp_nw04/helpdata/en/f8/2857cbc374da48993c8eb7d3c8c87a/content.htm

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi Bhavesh,

Thanks for your help...

Got it sorted...

Thanks,

Miguel