SAP PI : JDBC receiver scenario to support multiple DB( Oracle, DB2, MS SQL....)

[Former Member]

Hi,

I have a scenario (SNC-->PI-->DB), initially the DB was Oracle and we had developed the scenario and it is working fine with Oracle. Recently we had a customer who has a MS SQL Server and the scenarios failed. The reason,

We are inserting a timestamp into the one of the column of the DB table, for Oracle we are using TO_DATE() function which converts VARCHAR to DATE and inserts the date&time into the timestamp field. Since TO_DATE() function is specific to Oracle DB ,it doesnt work with MS SQL or DB2. Is there a generic way of handling this, if someone has faced similar issue.

INSERT INTO TABLEXXX (SNAPSHOT_DATE) VALUES (to_date('27-08-2012 09:33:27', 'DD-MM-YYYY HH24:MI:SS'))

Thanks,

Venu

[baskar_gopalakrishnan2]

>Is there a generic way of handling this, if someone has faced similar issue.

PI or even other middleware cannot handle generic.  The main reason is you install jdbc drivers for each individual database.   And the same software classes you reference in the communication channel.

If you want your code to be very generic, you need to have two seperate communication channels & interface design plus two drivers installation in pi specific to each database seperate. You need to activate the respective channels based upon the target system support.

[MichalKrawczyk]

Hi,

>>>PI or even other middleware cannot handle generic.

actually this is not true

PI has by all means generic approach (the same JDBC XML in most cases can be used with all databases and drivers as there is a seperation of mapping to adapter) it's more like there are only a few cases in which PI is not generic - same with JMS drivers, email servers, this is how I see it

Regards,

Michal Krawczyk

[baskar_gopalakrishnan2]

Thanks Michal. Could you please elaborate more on  "as there is a seperation of mapping to adapter" text.?

[MichalKrawczyk]

Hi,

>>>as there is a seperation of mapping to adapter

you can use the same XML for all DBs (expect date fields sometimes) right ?

the same for XML for e-mail adapter - works with all mail servers right?

also file adapter does it matter in the XML if you use NFS or FTP?

do you get the idea ?

Regards,

Michal Krawczyk

[baskar_gopalakrishnan2]

Yes I agree. Thanks.

[MichalKrawczyk]

Hi,

the general approach is: create one mapping with general data (mapping of all necessary fields)

then create a few new mappings (each for each new specific  requirement - like new DB)

and use both (the standard one and specific one) in the operation mapping

this way each time you add a new mapping you only need to create the scenario specific fields

and if you update the first one - all are updated at once

Regards,

Michal Krawczyk

[Former Member]

Hi Michal,

Thanks for the info! Yeah, I could go with your approach. But I also wanted to check if there is a standard way in PI which can handel such scenario.

For Oracle I need to use to_date() and MS SQL I need to use CAST() , so is  there a generic/standard way to handle this in PI, (or) does PI support such scenario's?

Thanks,

Venu

[rajasekhar_reddy14]

Hi Venu,

I dont think any standard way is avaible in PI to handle your requirement, you need to change interface development.

Check SQL server team to find any easy approach to handle this.

Regards,

Raj