How to use Integration Gateway with SMP 3.0 (Part 1)

How to use Integration Gateway with SMP 3.0

Part 1:
http://scn.sap.com/people/marvin.hoffmann/blog/2014/01/08/how-to-use-integration-gateway-with-smp-30-part-1

– Describes how to set up the HSQLDB and how to deploy the JDBC driver to SMP3

Part 2:
http://scn.sap.com/people/marvin.hoffmann/blog/2014/01/08/how-to-use-integration-gateway-with-smp-30-part-2

– Describes how to model, implement and deploy the OData service with Integration Gateway

Part 3:
http://scn.sap.com/people/marvin.hoffmann/blog/2014/01/08/how-to-use-integration-gateway-with-smp-30-part-3

– Describes how to configure the Integration Gateway on SMP3 to bind the OData service to the backend and how to display the OData service

The “Integration Gateway” which is one component of the SAP Mobile Platform 3.x can be used to build and execute OData services. In comparison to the SAP Netweaver Gateway you can set different backend sources. So it is possible to choose via JDBC a database as backend and the “Integration Gateway” will convert the data coming from the database to SAP OData.

In the following tutorial (3 parts) I want to show shortly how you can use “Integration Gateway” to create an OData service which connects to the (simple java based) database HSQLDB.

Hyper SQL Database (HSQLDB)

If you do not have a HSQLDB running already, you can simply setup a new database. I described it briefly for Windows:

1. Go to HSQLDB and download the latest stable version.

2. Create a new batch file (.bat file) and fill it with the command to start (and if not available already also create a new database):

cd “C:\Database\hsqldb-2.3.1”

java -cp hsqldb/lib/hsqldb.jar org.hsqldb.server.Server –database.0 file:mydb –dbname.0 xdb

Of course you have to adapt the path to your environment.

2. Start the just created batch file and leave the command window open

3. You can use the HSQLDB Database Manager ( placed in \hsqldb-2.3.1\hsqldb\bin\runManagerSwing.bat ) to maintain the database. Specify the connection settings. The URL in our examle: jdbc:hsqldb:hsql://localhost/xdb with the default user SA and no password.

4. Now you can execute SQL statements, e.g. to create a table CUSTOMER with columns FIRST_NAME, LAST_NAME, ADDRESS, CITY, COUNTRY, BIRTH_DATE. You can use the following statement:

CREATE TABLE CUSTOMER
(
ID          INTEGER IDENTITY,
FIRST_NAME        VARCHAR(30) NOT NULL,
LAST_NAME        VARCHAR(30) NOT NULL,
ADDRESS     VARCHAR(60) NULL,
CITY     VARCHAR(30) NULL,
COUNTRY     CHAR(2) NULL,
BIRTH_DATE DATE NULL
)

To insert some sample data execute:

INSERT INTO CUSTOMER (FIRST_NAME, LAST_NAME, ADDRESS, CITY, COUNTRY, BIRTH_DATE) VALUES ('Marvin', 'Hoffmann', 'Hasso-Plattner-Ring 7', 'Walldorf', 'DE', '1920-03-02');
INSERT INTO CUSTOMER (FIRST_NAME, LAST_NAME, ADDRESS, CITY, COUNTRY, BIRTH_DATE) VALUES ('Ting', 'Hoffmann', 'Hasso-Plattner-Ring 7', 'Walldorf', 'DE', '1928-05-01');
INSERT INTO CUSTOMER (FIRST_NAME, LAST_NAME, ADDRESS, CITY, COUNTRY, BIRTH_DATE) VALUES ('Otto', 'Wigeel', 'Romstr. 13', 'Berlin', 'DE', '1967-03-02');
INSERT INTO CUSTOMER (FIRST_NAME, LAST_NAME, ADDRESS, CITY, COUNTRY, BIRTH_DATE) VALUES ('John', 'Miller', 'Carpenter Street 103', 'New York', 'US', '1988-03-02');

Check the data with an SELECT statement

5. Then you should also create a new database user…

6. …and grant access rights to this user (for testing purpose DBA)

Now you are finished preparing the database. In the next step you can connect the database to your SMP3.

Configure SMP 3.0 server for Integration Gateway JDBC connection

The SMP3 server can only establish a connection to a database if the JDBC driver is already deployed. Because SMP3 based on OSGi, it is possible to deploy the JDBC driver as OSGi enabled jar package on (SMP) runtime. You simply have to copy the file to SMP’s pickup folder.

1. Get the JDBC driver package for HSQLDB (hsqldb.jar file contained in \hsqldb-2.3.1\hsqldb\lib) and copy it into C:\SAP\MobilePlatform3\Server\pickup .

(After that you can verify if the Bundle got deployed by checking if the file hsqldb.deploy.ok got created in path C:\SAP\MobilePlatform3\Server\pickup\.state)

To configure OData Services on SMP you can use the Gateway Management Cockpit (described in http://scn.sap.com/people/marvin.hoffmann/blog/2014/01/08/how-to-use-integration-gateway-with-smp-30-part-3), but before that you need to (develop) and deploy an OData service, described in part 2 http://scn.sap.com/people/marvin.hoffmann/blog/2014/01/08/how-to-use-integration-gateway-with-smp-30-part-2