Skip to Content
Author's profile photo Jitendra Kansal

How to connect an Derby DB with Integration Gateway in SMP3

We got a requirement where we have to fetch all registered device details from SMP 3.0 Admin cockpit those have been registered for push notifications (GCM, APNS, BES,WNS, MPNS). We have got access to Derby DB tables where required information is available.

 

This database stores information about the data that passes through the SAP Mobile platform system like execution requests between client and server, information about native and hybrid applications, back-end notification requests, and usage statistics.

 

Note: This write-up is only for SMP 3.0 development edition where Derby Database is get installed by default. For SMP 3.0 Production, you can find SMP related information in other database (either Oracle DB, ASE, DB2) that has been chosen while SMP installation.

 

There is a table name SMP_NOTIFICATION_TARGET that has all required details.

table.PNG

 

Tools & Platform Used: SQuirreL SQL Client v.3.5.3, Eclipse Kepler 4.3, SMP 3.0 SP03, Windows

 

Pre-requisite:

 

Note: If you are trying to install “Integration Gateway plugins” in eclipse juno, you may not find it. This tool will not be available for Eclipse juno, as SAP has official ended the support of Eclipse Juno in the end of June 2014. See this announcement.

 

Steps:

 

1. In Eclipse Kepler, Go to File>New>SAP Mobile Platform OData Implementation Project

 

newProj.png

 

2. Give any project name. Make sure to select proper server as Target Runtime. (I have installed SMP 3.0 SP03 on my machine so i have chosen respective option).

 

/wp-content/uploads/2014/09/name_530514.png

 

3. Give a Model name and click>Finish.

 

4. Create a new Entity>Give some name.

 

5. Make sure EntitySet should have same name as Table name > SMP_NOTIFICATION_TARGET

And its properties name should match as above table’s column names. (as per first screenshot)

 

ODatamodel.PNG

 

Note: There is no need of implement the OData service (as in Eclipse JUNO) since odatasrv has already been generated at the time of project creation.

 

6. Right click DerbyModel.odatasrv> Select Data source> select JDBC>Finish

 

jdbc.PNG

 

7.  Right Click project>Generate and deploy Integration content

 

Note: There is no need to deploy the JDBC driver for apache derby, because it is already on the build path (by SMP system). So do not deploy again the jdbc driver inside the pickup folder..

 

8. Logon to Gateway cockpit: https://smpserverip:8083/gateway/cockpit

    • Create a new destination under DESTINATIONS tab>Create a new Destination
    • Provide below details:

/wp-content/uploads/2014/09/uploadedimage_530525.png

 

 

Property Value
Destination Name DerbyDB
Destination Type DATABASE
Destination URL jdbc:derby://localhost:1527/F:\SAP\MobilePlatform3\Server\db\derby\smp3
Database Driver org.apache.derby.jdbc.ClientDriver
Authentication Type Basic Authentication
User Name gomobile
Password secret

 

9. Switch to “SERVICES” tab, Click the deployed OData service and then Click on “Add Destinations

 

addDestination.PNG

 

10. Click on “Open service document”

 

http://localhost:8080/gateway/odata/SAP/DERBYDBODATA;v=1

 

serviceDoc.PNG

 

11. To get EntitySet details, add SMP_NOTIFICATION_TARGET at the end of above URL.

 

http://localhost:8080/gateway/odata/SAP/DERBYDBODATA;v=1/SMP_NOTIFICATION_TARGET

 

To get the list of device id and Device notification key against an application id

 

http://localhost:8080/gateway/odata/SAP/DERBYDBODATA;v=1/SMP_NOTIFICATION_TARGET?$filter=CONFIGID%20eq%20%27com.mycompan…

 

details.PNG

 

ℹ To get list of all Application Connection ids against all Application IDs registered in admin cockpit then look for SMP_APPLN_CONNECTION_INFO table. It will also show if Application connection status is ACTIVE or DELETED.

 

Tips: When an entity type is created in an OData implementation project, there is one property key parameter gets created by default. Make sure to remove/rename it. Otherwise you may encounter with below error while opening EntitySet URL. As this key parameter is not present in database table.

 

“An exception occurred”.

 

exception.PNG

 

 

Big thanks to Marvin Hoffmann for his valuable inputs.

Assigned Tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hello Jitendra. I'm trying to connect to Derby, installed on a SMP on our local server. After configuration, I tested the URL of the service that contains the destination:

      After that I am prompted for an user/password. Do you know what user that may be? Since there's no configuration for an user on the cockpit, that's not clear to me.

      Thank you.

      Author's profile photo Jitendra Kansal
      Jitendra Kansal
      Blog Post Author

      Luciano Sclovsky

      Which SMP server version you have installed? Are you trying to access already available service in GW management cockpit? If yes, you have to

      1. create a new destination (Activating Sample Data for ESPM)
      2. Add the same destination to the existing service

      I hope it should work. If problem still persists, raise a new thread as your problem is not at all related to DerbyDB installation.

      Regards,

      JK

      Author's profile photo Former Member
      Former Member

      Hello Jitendra.

      Thanks for your answer. I was not able to fix my issue with connecting to Derby DB. But decided to try using HSQLDB, and now I have a different issue, but as you said, it is not related to this thread.

      Regards,

      Luciano

      Author's profile photo Jitendra Kansal
      Jitendra Kansal
      Blog Post Author

      Hi Luciano,

      Can you raise a new thread with your latest issue so that experts can look into.

      Please provide steps followed with screenshots.

      Regards,

      JK

      Author's profile photo Former Member
      Former Member

      Hi Jitendra. Just to let you know that I have just created a new thread. Thank you very much. Regards, Luciano.

      Author's profile photo Jitendra Kansal
      Jitendra Kansal
      Blog Post Author

      I have responded over there.