Skip to Content

Update (August 2015)

If you are using SMP SP08 with MS SQL Server as a backend DB, there is no need to deploy an osgi enabled Microsoft JDBC driver to SMP, this means that you can skip steps 1 to 9.

If you did it already and you are finding the following error, then please check the following KBA http://service.sap.com/sap/support/notes/2202778

#ERROR#com.sap.gateway.core.odata4sap.ODataErrorCallbackImpl##C1228276#ht tp-bio-8003-exec-16##094a57f4-f170-40fd-bedc-51afe2139f48##e42881bc-67c0- 43c8-af13-cba3a4c39b62####handleError(): failed to serve request for URI ************, message = Driver class com.microsoft.sqlserver.jdbc.SQLServerDriver not found: Transaction ID :3807f92a-a455-4510-8387-c48f5f87a0b5

——————————-

In this blog post I will explain how to use the Integration Gateway in SMP 3.0 to expose data coming from a Microsoft SQL Server. I won’t describe the basics here. If you are new to Integration Gateway I recommend reading the three parts tutorial here at first: How to use Integration Gateway with SMP 3.0 (Part 1) .

Basically, you need to enable Microsoft’s JDBC driver for OSGI, then deploy it to SMP3, create a destination and deploy a matching OData service.

I tested it with SMP 3.0 SP02 and Microsoft SQL Server 2008 R2

Steps

  1. Download and unzip Microsoft JDBC Driver for SQLServer 4.0
    Microsoft JDBC Driver for SQL Server
  2. Open eclipse (Version: Java EE Developer) and click on New > Other… > Plug-in Development > Plug-in from Existing JAR Archives
  3. Add the MSSQL JDBC driver as external jar file. You can find the file sqljdbc4.jar in the unzipped folder: Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu
  4. Choose a project name “com.microsoft.sqlserver.jdbc”. Choose the target platform OSGI framework “Equinox” and make sure that the option “Unzip the JAR archives into the project” is not selected (Otherwise you will get a signature error on deployment later on)

    /wp-content/uploads/2014/02/01_eclipse_plugin_project_388304.png

  5. The sqljdbc4.jar should be included inside your project
    /wp-content/uploads/2014/02/02_eclipse_plugin_project_388305.png
  6. Now choose (right click on the project) Export > Plug-in Development > Deployable plug-ins and fragments
  7. Select (if not already pre-selected) your project choose a destination directory and click on “Finish”
  8. Copy the created jar file to the pickup folder of your SMP3 installation (C:\SAP\MobilePlatform3\Server\pickup). Verify that the deployment was successful by checking the folder .state (and also the server log)
    /wp-content/uploads/2014/02/03_deploy_mssql_jdbc_driver_388316.png
  9. Go to the Gateway Management Cockpit (https://smp3server:8083/gateway/cockpit), open tab “DESTINATIONS” and create a new one/wp-content/uploads/2014/02/04_create_destination_388318.png
Property Value
Destination Name DestMicrosoftDB
Destination Type DATABASE
Destination URL jdbc:sqlserver://mssqlserver:1433;databaseName=SampleDB
Database Driver com.microsoft.sqlserver.jdbc.SQLServerDriver
Authentication Type Basic Authentication
User Name yourDBUser
Password yourDBPassword

Of course you might have to adapt the values to fit your environment.

10. After that open your eclipse with GWPA plugin, create a new Service Implementaion Project, add or create your ODataModel. Think about that the entityset has to be called like the table in your database and that the entity properties have to match the columns in the table.
05_Service_Creation.png

11. Then implement the service and choose as data source JDBC.

12. Deploy the service to your SMP3.

13. Go back to your Gateway Management Cockpit, click on the newly deployed service and assign the MSSQL destination we created in an earlier step.

06_Gateway_Cockpit_Assign_Destination.png

13. Activate the service and try if it is working
07_Working_MSSQL_Service.png

13. You are done! You successfuly exposed data coming from a Microsoft SQL Server as OData…

Troubleshooting

If you have problems to establish a connection to your Microsoft SQL Server Database, try at first if your connection string, user and password are correct. You can use e.g. the free utility SQL Client SQuirreL http://squirrel-sql.sourceforge.net/

After open it, switch to tab Drivers and choose “Microsoft MSSQL Server JDBC Driver”. In tab “Extra Class Path” choose the sqljdbc4.jar file

08_SQLSERVER_ACCESS_squirrel.png

Switch to tab Aliases and add a new alias with your connection details, after that connect to your database.

09_SQLSERVER_ACCESS_squirrel.png

Errors during deployment in SMP server log

java.lang.SecurityException: Invalid signature file digest for Manifest main attributes

This refers to a signature problem of your created osgi bundle. Make sure that you in step 4 unselect the option “Unzip the JAR archives into the project”.

To report this post you need to login first.

3 Comments

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

  1. Mayur Dharukiya

    Hi Marvin,

    is it possible to create OData Service required from the Scratch or say combining multiple SOAP and Rest services, customize it and then develop new OData Service. We are encountering variety of back-ends including custom non-sap applications/portals. How to connect to NON-SAP WeB Portals and other custom web apps through integration gateway?

    (0) 
  2. Mayur Dharukiya

    Hi Marvin,

    i noticed while configuring back-end in IG Cockpit. Destination URL and Database Driver names are different in terms of how it is written. Is there any specific pattern we have to follow while mentioning Destination URL and Database Driver?

    Let me also know in case of MySQL where to find Destination URL and Database Driver Name?

    Regards,

    Mayur

    (0) 
  3. Himadri Chakravorty

    Hi Marvin,

    Thank you so much for sharing this Blog. I successfully deployed the service in SMP3.0 but while running it shows me error

    Matrix parameter ‘[v]’ in path segment ‘IG_JDBCSQL’ not supported!

    Can you please guide me how to resolve this error.

    Thanks

    Himadri

    (0) 

Leave a Reply