Skip to Content

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”

/wp-content/uploads/2014/01/step0_360940.png

3. In “JAR selection” window click on “Add External…” and choose the downloaded ojdbc7.jar.

/wp-content/uploads/2014/01/step02_360950.png

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.

/wp-content/uploads/2014/01/step03_360951.png

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.

/wp-content/uploads/2014/01/step04_01_360952.png

So finally it should look like on the picture below:

/wp-content/uploads/2014/01/step04_02_360953.png

6. Right click on the project and choose “Export”. Expand “Plug-in Development” and select “Deployable plug-ins and fragments”.

/wp-content/uploads/2014/01/step05_360954.png

7. Choose the corresponding Plugin Project

/wp-content/uploads/2014/01/step06_360955.png

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.

/wp-content/uploads/2014/01/step08_360956.png.

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.

/wp-content/uploads/2014/01/step09_360957.png


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.

/wp-content/uploads/2014/01/step10_360958.png

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)

/wp-content/uploads/2014/01/step12_360960.png

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…

OSGI_Deploy_Error.png

( This article has been also published here: How to connect an Oracle DB with Integration Gateway in SMP3)

To report this post you need to login first.

14 Comments

You must be Logged on to comment or reply to a post.

  1. Jitendra Kansal

    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

    2.PNG

    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:

    1.PNG

    Please help me on this.

    Rgrds,

    Jitendra

    (0) 
    1. Marvin Hoffmann Post author

      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

      (0) 
      1. Jitendra Kansal

        Hi Marvin Hoffmann,

        I am getting an error at step 5.

        by default, in the MAINFEST.MF file i get this coding:

        Manifest-Version: 1.0

        Bundle-ManifestVersion: 2

        Bundle-Name: OracleJDBCDriver

        Bundle-SymbolicName: com.oracle.jdbc

        Bundle-Version: 1.0.0

        Export-Package: oracle.core.lmx,

        oracle.core.lvf,

        oracle.jdbc,

        oracle.jdbc.aq,

        oracle.jdbc.babelfish,

        oracle.jdbc.connector,

        oracle.jdbc.dcn,

        oracle.jdbc.diagnostics,

        oracle.jdbc.driver,

        oracle.jdbc.internal,

        oracle.jdbc.oci,

        oracle.jdbc.oracore,

        oracle.jdbc.pool,

        oracle.jdbc.proxy,

        oracle.jdbc.proxy.annotation,

        oracle.jdbc.replay,

        oracle.jdbc.replay.driver,

        oracle.jdbc.replay.internal,

        oracle.jdbc.rowset,

        oracle.jdbc.util,

        oracle.jdbc.xa,

        oracle.jdbc.xa.client,

        oracle.jpub.runtime,

        oracle.net.ano,

        oracle.net.aso,

        oracle.net.jdbc.TNSAddress,

        oracle.net.jdbc.nl,

        oracle.net.jdbc.nl.mesg,

        oracle.net.jndi,

        oracle.net.ns,

        oracle.net.nt,

        oracle.net.resolver,

        oracle.security.o3logon,

        oracle.security.o5logon,

        oracle.sql,

        oracle.sql.converter

        Import-Package: javax.sql.rowset.spi,

        javax.sql.rowset,

        javax.naming,

        org.w3c.dom,

        javax.crypto.spec,

        javax.transaction.xa,

        javax.crypto,

        org.objectweb.asm; version=”3.3.1″,

        javax.sql,

        javax.naming.directory,

        javax.security.auth.kerberos,

        javax.security.auth,

        org.ietf.jgss,

        javax.net,

        org.xml.sax.helpers,

        javax.security.auth.callback,

        javax.crypto.interfaces,

        javax.net.ssl,

        javax.security.cert,

        org.xml.sax,

        javax.xml.parsers,

        javax.naming.spi,

        javax.xml.transform,

        javax.management

        Bundle-RequiredExecutionEnvironment: JavaSE-1.7

        and i replaced this with what you have suggested above:

        Manifest-Version: 1.0

        Bundle-ManifestVersion: 2

        Bundle-Name: OracleJDBCDriver

        Bundle-SymbolicName: com.oracle.jdbc

        Bundle-Version: 1.0.0

        Import-Package: javax.crypto.spec,

        javax.xml.transform,

        org.objectweb.asm,

        javax.naming,

        javax.xml.parsers,

        javax.sql.rowset.spi,

        javax.naming.spi,

        javax.security.auth.kerberos,

        org.ietf.jgss,

        org.xml.sax,

        javax.crypto,

        javax.sql.rowset,

        org.xml.sax.helpers,

        javax.sql,

        javax.crypto.interfaces,

        javax.security.cert,

        javax.net,

        javax.management,

        javax.security.auth,

        org.w3c.dom,

        javax.naming.directory,

        javax.security.auth.callback,

        javax.transactioin.xa,

        javax.net.ssl

        Bundle-RequiredExecutionEnvironment: JavaSE-1.7

        I am getting error in the last line of code, says

        “Header must be terminated by a line break”

        3.PNG

        Am i missing something in between?

        Please help.

        Rgrds,

        Jitendra

        (0) 
        1. Marvin Hoffmann Post author

          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

          (0) 
  2. Chaitanya Bilaware

    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

    (0) 
    1. Marvin Hoffmann Post author

      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…

      (0) 
    2. Jitendra Kansal

      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

      (0) 
  3. Chaitanya Bilaware

    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.

    (0) 
    1. Jitendra Kansal

      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

      (0) 

Leave a Reply