JDBC Scenario

[Former Member]

Hi All,

I new to PI, I am doing a scenario on JDBC -->PI --> IDOC. Please tell me what i need to be done to configure sender adapter. I gone though this Note 831162. It says we need to deploy JDBC driver in J2EE server

"The JDBC driver deployment is performed using a special SDA file named aii_af_jmsproviderlib.sda (releases 3.0/7.0) or com.sap.aii.adapter.lib.sda (releases 7.1) which is shipped as an empty stub with your XI installation. For Type 4 drivers, information how to add your JDBC drivers to this SDA is available in section 11.1 of the SAP XI 3.0 Configuration Guide, which is available on the SAP Service Marketplace under the URL: http://service.sap.com/instguidesNW04."; Please tell me what all i need to do to establish connection between PI and Database.

Thanks in Advance,

Nisha

[JoelTrinidade]

hi Nisha,

JDBC adapters need the drivers specific to the database that they are connecting. The drivers are freely available on the net for download . There is also a step by step guide for deploying these drivers.

Rest for connection to a database you would need to the connection string and jdbc driver string. These are also available with the db administrator to whose db you would be connecting.

regards

joel

[MichalKrawczyk]

hi,

did you manage to deploy the driver as per this guide?

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/f04ce027-934d-2a10-5a8f-fa0b1ed4...

Regards,

Michal Krawczyk

[Former Member]

Hi Michal,

I am doing this scenario on PI 7.1.

I have 3 questions

1.Do I need to go for Stored procedure concept or shall i use query statement in Data type? if I go for stored procedure how and where to use this?

2. I am doing mass upload of Material Master from JDBC to SAP. Database has around 50k records shall I pick all at once or do I need to break?

3.After mass upload I have to do delta upload i.e if there is any changes to existing material then I have to pick that material from DB. How can I achieve this?

Thanks In advance

Nisha

[MichalKrawczyk]

Hi,

>>1.Do I need to go for Stored procedure concept or shall i use query statement in Data type? if I go for stored procedure how and where to use this?

depends on what you need to fetch

>>>2. I am doing mass upload of Material Master from JDBC to SAP. Database has around 50k records shall I pick all at once or do I need to break?

I'd do it in a few rounds (you can specify different select statements in sender jdbc adapter for that)

>>3.After mass upload I have to do delta upload i.e if there is any changes to existing material then I have to pick that material from DB. How can I achieve this?

you need to create something in your app that will send a message with material number to XI - then from XI you can use jddc receiver adapter

to fetch the whole record from db

Regards,

Michal Krawczyk

[Former Member]

Hi Nisha,

To answer your questions

1. Go for Store procedure concept as they are pre-compiled and have an execution plan. If you use query every time it runs on DB it has to be checked for syntex errors and then need to be compiled so would take a bit. Store procedure are used almost in the same way as Query. You will find blogs on SDN for that.

2. Choose as per your requirements both approach has its advantages as well as limitations. Picking in different calls will have its own disadvantages as it would need multiple database roundtrips. If the data size is not huge you can go for a single call.

3. As Michal suggested, it is the best approach. If you can add a flag to database for it then it would be really clean job for you. For example have a field like AccessedByXI = False, whenever a record is created and then use select <your field names> From Table where AccessedByXI = False. And then update it with second query inside the same store procedure. you can use the same approach for your second question also for feching the records in multiple trips.

Regards,

Jitender

[Former Member]

Hi Jitender Chauhan ,

Do u have any document on how to write stored procedure and where to use that stored procedure. If I use stored procedure then how will be my source(JDBC) data type will look like, can I create data type which look like file data type or do i need to create like query statement? Please help out on this issue.

Thanks,

Nisha

[Former Member]

Hi Nisha,

Please check the link for store procedure help

[http://www.codeproject.com/KB/database/hkstoredproc.aspx]

For store procedure message type have a look on the following scenario

[http://www.sap-xi.com/sap-xi-scenario-soap-jdbc.html]

Regards,

Jitender

[Former Member]

Hi Jitender Chauhan,

I am doing JDBC to SAP ECC.

Can i use stored procedure in sender adapter? link what you gave for receiver adapter. if we can use please tell me the procedure where can I use? Usually we write direct query in sender jdbc adapter.

Please help me

Thanks,

Nisha