How to connect an Oracle DB with Integration Gateway in SMP3
In this blog post I want to show how you can develop and deploy an OData service to the SMP3 which relies on an Oracle database. That means that the Integration Gateway component in SMP3 will convert on the fly data coming from the Oracle DB into OData.
I assume that you have already the basic knowledge about Developing OData services with Integration Gateway (using JDBC as backend source). I described the detailed process here:
Part 1: How to use Integration Gateway with SMP 3.0 (Part 1)
– Describes how to set up the HSQLDB and how to deploy the JDBC driver to SMP3
Part 2: How to use Integration Gateway with SMP 3.0 (Part 2)
– Describes how to model, implement and deploy the OData service with Integration Gateway
Part 3: How to use Integration Gateway with SMP 3.0 (Part 3)
– Describes how to configure the Integration Gateway on SMP3 to bind the OData service to the backend and how to display the OData service
The process of getting OData out of an Oracle DB is described in this blog post and consists of 3 main steps
- SMP3 Backend Configuration
- OData service development includes: Modelling, Implementing and Deploying of the service
- SMP3 OData Configuration
SMP3 Backend Configuration
The SMP3 server needs the Oracle JDBC driver. Unfortunately, the Oracle JDBC driver is not archived as OSGi bundle. Because of that you need to create an OSGi enabled bundle which includes the Oracle JDBC driver. This bundle can be read and understood by SMP3.
1. Download the Oracle JDBC driver (e.g. ojdbc7.jar)
Oracle Database 12c Release 1 JDBC Driver Downloads
2. Open eclipse (Java EE Edition) and choose New > Other. Expand “Plug-in Development” and select “Plug-in from Existing JAR Archives”
3. In “JAR selection” window click on “Add External…” and choose the downloaded ojdbc7.jar.
4. Define the project properties like below. Don’t forget to select “Analyze library contents and add dependencies” and select as target platform “OSGi framework” with value “Equinox”. Also select “Unzip the JAR archives into the project” and finish.
5. Because of a resolution problem of one specific package, we need to make a small change to the MANIFEST.MF. Therefore switch to the MANIFEST.MF tab and delete the version information below package org.objectweb.asm.
So finally it should look like on the picture below:
6. Right click on the project and choose “Export”. Expand “Plug-in Development” and select “Deployable plug-ins and fragments”.
7. Choose the corresponding Plugin Project
8. The jar archive “com.oracle.jdbc_1.0.0.jar” will be created. Copy this bundle into folder C:\SAP\MobilePlatform3\Server\pickup. The file should be automatically deployed. You can verify this by checking if in the .state folder is a newly created file with ending .deploy.ok and also if the jar bundle got copied into the Server\work\org.eclipse.virgo.nano.deployer\staging folder.
.
There is no restart required, this is a hot deployment.
OData Service Development using toolkit for Integration Gateway (GWPA)
In this step the OData service has to be modelled, implemented and deployed. This is done in the same way for each (JDBC) backend connection. You can use the Toolkit for Integration Gateway, which I described already in this post: How to use Integration Gateway with SMP 3.0 (Part 2).
Think about that the entity set must match the table name in the OracleDB and that the properties must match the columns inside this table.
SMP3 OData Configuration
After the OData service has been deployed you need to configure this OData service to use the Oracle database as backend connection. Therefore you need to create a new destination and link this destination to the deployed OData service.
1. Open the Gateway Management Cockpit ( https://smp3server:8083/gateway/cockpit ).
2. Switch to “DESTINATIONS” and click on “Create a New Destination…”. Choose the following information for a connection to HSQLDB database:
| Property | Value |
|---|---|
| Destination Name | DestOracleDB |
| Destination Type | DATABASE |
| Destination URL | jdbc:oracle:thin:@DEWDFWSSP2903.dhcp.wdf.sap.corp:1521:oracle2903 |
| Database Driver | oracle.jdbc.driver.OracleDriver |
| Authentication Type | Basic Authentication |
| User Name | smpUser |
| Password | yourPassword |
Of course you might have to adapt the destination url, username or password dependent on your environment.
INFO: Check carefully if the saved Database driver input field is showing the full string “oracle.jdbc.driver.OracleDriver”. In earlier SP versions of SMP3.0 there was a character length restriction on that field which required setting the driver name on file system level (C:\SAP\MobilePlatform3\Server\config_master\service.destinations\destinations). With SMP 3.0 SP03 or higher this limitation has been removed.
4. After that edit the OData service in the Gateway Management Cockpit and add the Destination (DestOracleDB). Then activate the OData service and try to open it. (Be aware that you need a security configuration which matches the namespace of this OData service)
Now you are finished and you can see, that the data coming from the OracleDB is represented as OData.
Troubleshooting
If you get a deployment error (file .deploy.error is created in .state folder) check your server log. The component org.eclipse.virgo.medic.eventlog will log an error which might give you more information.
In this case the version of the package import org.objectweb.asm cannot be resolved…
( This article has been also published here: How to connect an Oracle DB with Integration Gateway in SMP3)
Hi Marvin Hoffmann ,
Appreciate your effort for sharing this document.
I tried this approach:
Let me explain what i did for SMP3 backend configuration process:
Till step 4, everything was fine.
In step 5, i didnt make any change in MANIFEST.MF file and exported using step 6 and 7.
In step 8, i copied the exported jar file to the E:\SAP\MobilePlatform3\Server\pickup location but in the .state folder, i see "Error file type" for com.oracle.jdbc_1.0.0
Fyi: SMP server is installed on other machine and eclipse on different machine but both on the same network.
What could be the reason for this?
----------------------------
2nd approach:
In step 5, i replaced the whole code with the code you shared (in the snapshot)
but here i am getting error says:
Please help me on this.
Rgrds,
Jitendra
hi Jitendra,
I edited my blog post... You might ran into the resolution problem related to org.objectweb.asm package (Check step 5 again, I changed it just now).
So try to delete the version information from MANIFEST file before exporting the jar file. Also check the server log of SMP3 if a deployment failed, because there a detailed error description is given (see new troubleshooting section in blog post)
Regards
Marvin
Hi Marvin Hoffmann,
I am getting an error at step 5.
by default, in the MAINFEST.MF file i get this coding:
and i replaced this with what you have suggested above:
I am getting error in the last line of code, says
"Header must be terminated by a line break"
Am i missing something in between?
Please help.
Rgrds,
Jitendra
Hi Jitendra Kansal,
did you try it before with your default manifest file (that one with the Export and Import Packages)? It doesn't have to look exactly like that one I showed above. Delete only the version information for org.objectweb.asm; version="3.3.1" and try to export the JAR bundle. It might work already.
If you want to use the replaced Manifest, you have to fix some problems here...
1) Make sure that there is a space in front of the packages when importing (or exporting them), like this:
Import-Package: javax.crypto.spec,
[space here] javax.xml.transform,
[space here] org.objectweb.asm,
[space here] javax.naming, ...
2) Header must be terminated by a line break
Simply give him a line break, so enter an empty line behind "Bundle-RequiredExecution...."
3) Package javax.transactioin.xa is misspelled. Should be javax.transaction.xa
Regards
Marvin
Thanks Marvin Hoffmann
I am able to deploy it now by removing version info in step 5.
Rgrds,
Jitendra
DestOracleDB" file is not getting created at this location in my server - C:\SAP\MobilePlatform3\Server\config_master\service.destinations\destinations.
I have edited the manidfest file. plz help
Hi Chaitanya,
this should not be needed anylonger. With current release of SMP3 SP03 this length restriction of Database Driver input field got resolved, so there is no need anymore to edit destinations on file system or db level...
I also updated the guide with this info...
Chaitanya Bilaware
Which SMP 3.0 service pack version you are working on?
Above document has been written for smp 3.0. And that character restriction issue has been resolved in SP03. So you wont see that file under "destinations".
Please raise a new thread for your query.
Rgrds,
JK
thnx Marvin and Jitendra
I am having problem with the destination url oracle sql developer. if you could plz correct me with correct one
Chaitanya Bilaware
Try with jdbc:oracle:thin:@ip:port:sid
in your case: jdbc:oracle:thin:@10.10.10.10:1521:ORA2 (replace ip)
Rgrds
JK
Hi Jitendra Kansal, I tried with the url u gave but still i am getting same error, "An Exception occurred". I successfully did the same project with HSQLDDB. though I followed all the steps, with oracle its not working.I think some problem is with oracle driver in my project.So, if you could help me in confirming the problem and fixing it.I first i used the same ojdbc jar file specified in this document. later i tried with ojdbc14.jar. Is there any path that we have to set like we set the path for jdk.
Chaitanya Bilaware
odjdbc7.jar is quite sufficient.
I doubt if you have missed something while creating OSGi enabled jar file.
Please check all steps as mentioned above.
Step #4 is most important.
Deploy OSGi enabled jar file as per step 8
Make sure EntitySet name should be as TABLE name
All Parameters name should be as TABLE's columns name (make sure to remove/rename auto generated key parameter while creating an Entity Type)
If you still face issue, please raise a new thread and share all the steps (with screenshot) what all you have tried.
Regards,
JK