How to connect an Oracle DB with Integration Gateway in SMP3

[marvin_hoffmann]

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

1. SMP3 Backend Configuration
2. OData service development includes: Modelling, Implementing and Deploying of the service
3. 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)