Integration Gateway and Microsoft SQL Server as data source
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
- Download and unzip Microsoft JDBC Driver for SQLServer 4.0
Microsoft JDBC Driver for SQL Server
- Open eclipse (Version: Java EE Developer) and click on New > Other… > Plug-in Development > Plug-in from Existing JAR Archives
- 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
- 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)
- The sqljdbc4.jar should be included inside your project
- Now choose (right click on the project) Export > Plug-in Development > Deployable plug-ins and fragments
- Select (if not already pre-selected) your project choose a destination directory and click on “Finish”
- 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)
- Go to the Gateway Management Cockpit (https://smp3server:8083/gateway/cockpit), open tab “DESTINATIONS” and create a new one
|Authentication Type||Basic Authentication|
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.
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.
13. You are done! You successfuly exposed data coming from a Microsoft SQL Server as OData…
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
Switch to tab Aliases and add a new alias with your connection details, after that connect to your database.
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”.