Additional Blogs by SAP
cancel
Showing results for 
Search instead for 
Did you mean: 
marvin_hoffmann
Active Participant

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)

  5. The sqljdbc4.jar should be included inside your project
  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)
  9. Go to the Gateway Management Cockpit (https://smp3server:8083/gateway/cockpit), open tab "DESTINATIONS" and create a new one
PropertyValue
Destination NameDestMicrosoftDB
Destination TypeDATABASE
Destination URLjdbc:sqlserver://mssqlserver:1433;databaseName=SampleDB
Database Drivercom.microsoft.sqlserver.jdbc.SQLServerDriver
Authentication TypeBasic Authentication
User NameyourDBUser
PasswordyourDBPassword

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. Activate the service and try if it is working

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

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".

3 Comments