Configuring connection class and web.xml file to connect MSSQL Database with JDBC

[Former Member]

Hi experts,

I am new at SAP and the company where I am intern at gave me a project that runs on SAP Cloud Platform and uses the data from Microsoft SQL express database that runs locally. And also, application should connect to IoT device and send the information after processing the data from MS SQL Database . I have thought to connect the database with Cloud Connector and JDBC by following the blog "How to set up a JDBC Connection using the Cloud Connector", but I couldn't configure how to develop connection class and configure the persistence layers for SAP Cloud Platform in web.xml so that it can run on cloud correctly. I have tried relational way to connect :

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

String connectionUrl = "jdbc:sqlserver://localhost:53447;" + "database=MachineFirstTest;" + "user=...;" + "password=......."; Connection con = DriverManager.getConnection(connectionUrl); System.out.println("Connected.");

// Create and execute an SQL statement that returns some data.

String SQL = "SELECT TOP (1000) [Id]\r\n" + " ,[Machine_name]\r\n" + " ,[Productivity]\r\n" + " FROM [MachineFirstTest].[dbo].[Productivity_name]";

Statement stmt = con.createStatement();

ResultSet rs = stmt.executeQuery(SQL);

However, it fails. I know I should connect to database in a different way, but I couldn't find any resource to use.

Also, I have just set up Cloud Connector connection "on premise to cloud" and I have chosen TCP connection.

I am not good at programming Web Application, what should I do to reach MS SQL database runs locally to run java application on SAP Cloud Platform ?

Best Regards,

Ceren

[Ivan-Mirisola]

Hi Ceren,

Usually you would choose as local database a software the is also available in the cloud. MS-SQL backing service isn't available in SCP for instance. So, choosing this DB as local database doesn't make a lot of sense, once you have other options that are available in both local and SCP. Ideally, the local database serves the following purposes when developing cloud native applications: development and testing.

However, if you are absolutely sure that MSSQL is the way to go for local development, you should be using JPA and I would suggest you start using Spring Boot which is ready for JPA and ORM development.

You could also develop a Java application with JPA manually - referencing the database JDBC driver inside the web.xml file - but I still think that for startup projects such as yours you ought to start with JPA.

Finally, the Cloud Connector is not meant to connect your cloud application to a local database. The on-premise to cloud is actually targeted at allowing database tools to connect to your cloud database - such as HANA or PostgresSQL. Please read:

https://help.sap.com/viewer/cca91383641e40ffbe03bdc78f00f681/Cloud/en-US/a2ca4e857ce74f26b5dcf3483e7...

Best regards,
Ivan