Creating WSO2 EI DataServices with Sybase database

Nov 1, 2019 · 6 minute read · Comments
WSO2 ESB EI Data Services Sybase Docker

This post will show a quick example on how we can connect with Sybase database using WSO2 EI DataServices. For this post, we have used WSO2 EI 6.5.0, but it should work with EI 7 as well. So, let us start.

Starting a Local Sybase Instance

In order to have this example to work, we need to have an instance of Sybase. For that, we will use the following Docker Image available in Dockerhub: datagrip/sybase. This Docker image contains a Sybase 16.2, to start it we just need to run the following command:

docker run -d -t -p 5000:5000 datagrip/sybase

After sometime, ~30s, we can connect to the sybase instance. The default credentials can be found in the docker image page in dockerhub. We can use a SQL Client to connect to it, one good client is DBeaver.

In our example we will connect with admin user(sa). In Dbeaver the connection details will look like below:

DBeaver Connection

After connected, we can see the list of the database schemas available:

DBeaver Databases

For our example, we will use the testdb schema. We will create a products table like below:

CREATE TABLE testdb.dbo.product ( 
ID BIGINT IDENTITY,
name VARCHAR(255), 
description VARCHAR(244),
price DECIMAL,
PRIMARY KEY(ID)
);

The command above will create the table in the testdb schema.

Connecting WSO2 EI to the Sybase Database

We can connect to Sybase using JDBC. We can use the JTDS Driver or the JConnect Driver, for our example, we will use the JConnect Driver. I could not find a link to download it, (my fault, I was lazy to look for it), but, we have it available inside the Sybase container that we started in the previous steps. In order to do that, we can use the command docker cp, but we need to have the container id. To obtain the container id we can use the docker ps command, below we can see the output:

CONTAINER ID        IMAGE   ....
2bb8d9f01869        datagrip/sybase    ...

So, to copy the jdbc driver jar from the container to our local machine we can use the following command:

docker cp [CONTAINER ID]:/opt/sybase/shared/lib/jconn4.jar .

The above command will copy the jconn4.jar from that location into the current folder in the local machine. We need to replace the [CONTAINER ID] with the id retrieved in the docker ps command output.

Once we have the jar file, we need to copy it into [WSO2_EI_HOME]/lib folder. After that we need to restart the EI Server in order to load the jar file.

Now that we have the jar file copied into the EI installation and restarted it, we can create a new Datasource pointing to the Sybase server. In this example, we will create the datasource using the Carbon Console UI, but you can find other ways to create it here.

To create the datasource, we need to:

Log into Carbon: https://localhost:9443/carbon
Go into Configure tab -> Datasources, and then click Add Datasource:
Fill the datasource details like below:

Datasource Type: RDBMS
Name: Sybase
Datasource Provider: default
Database Engine: Sybase ASE
Driver: com.sybase.jdbc4.jdbc.SybDriver
URL: jdbc:sybase:Tds:localhost:5000/testdb
User Name: sa
Password: myPassword

Carbon Console New Datasource

If everything is set up correct when we click on Test Connection, it should be successful, after that, we can click on Save.

Creating the Dataservice

The full code of the example we will create can be seen below:

In the Dataservice above we have 3 operations exposed:

A select in a table;
A Stored procedure call with no parameters;
A Stored procedure call with parameters;

Executing a Sybase Select Query

This is very similar to any select query in other RDBMS, we can see the details below:

We can see the query, operation(SOAP) and resource(REST) for that:

<query id="Query_All_Products" useConfig="Sybase">
    <sql>Select ID,name ,description, price from testdb.dbo.product</sql>
    <result element="Products" rowName="Product">
        <element column="id" name="id" xsdType="string"/>
        <element column="name" name="name" xsdType="string"/>
        <element column="description" name="description" xsdType="string"/>
        <element column="price" name="price" xsdType="decimal"/>
    </result>
</query>
...
<operation name="QueryAllProducts">
    <call-query href="Query_All_Products"/>
</operation>

<resource method="GET" path="/products">
    <call-query href="Query_All_Products"/>
</resource>

Basically, we define a query against the product table, and we map the columns returned in the query to the expected attributes in the response. We define a SOAP operation QueryAllProducts and a Rest Resource /products both of them pointing to the same query.

With that in place, we can call for example, the /products resources like below:

http://localhost:8280/services/SybaseTest/products

We will have a return like below:

<Products xmlns="http://ws.wso2.org/dataservice">
    <Product>
        <id>1</id>
        <name>Book</name>
        <description>Wonderful book</description>
        <price>10</price>
    </Product>
</Products>

Executing a Stored Procedure with no parameters

In this query, we are executing an existing stored procedure in Sybase, in this case sp_activeroles, to call it we use the exec:

exec sp_activeroles

This call will return a result set with only one column “Role Name”. The query and resource can be seen below:

<query id="GetRoles" useConfig="Sybase">
    <sql>exec sp_activeroles</sql>
    <result element="Roles" rowName="Role">
        <element column="Role Name" name="RoleName" xsdType="string"/>
    </result>
</query>

<resource method="GET" path="/roles">
    <call-query href="GetRoles"/>
</resource>

The configuration above will expose a resource /roles, that we can call using the endpoint below:

http://localhost:8280/services/SybaseTest/roles

This will return a payload like below:

<?xml version="1.0" encoding="UTF-8"?>
<Roles xmlns="http://ws.wso2.org/dataservice">
    <Role>
        <RoleName>sa_role</RoleName>
    </Role>
    <Role>
        <RoleName>sso_role</RoleName>
    </Role>
    <Role>
        <RoleName>oper_role</RoleName>
    </Role>
    <Role>
        <RoleName>sybase_ts_role</RoleName>
    </Role>
    <Role>
        <RoleName>mon_role</RoleName>
    </Role>
    <Role>
        <RoleName>sa_serverprivs_role</RoleName>
    </Role>
</Roles>

Executing a Stored Procedure with Parameters

In this query we will call the stored proc sp_columns and it expect some parameters like below:

exec sp_columns 'jdbc_function_escapes', null, null, null;

This command will return a result set with a set of columns, in our example we will map only two columns to the response provided by the service.

The query for this stored proc call can be seen below:

<query id="GetColumns" useConfig="Sybase">
    <sql>exec sp_columns ?, ?, ?, ?</sql>
    <result element="Columns" rowName="Column">
        <element column="column_name" name="ColumnName" xsdType="string"/>
        <element column="type_name" name="TypeName" xsdType="string"/>
    </result>
    <param name="table_name" optional="false" sqlType="STRING"/>
    <param defaultValue="#{NULL}" name="table_owner" optional="true" sqlType="STRING"/>
    <param defaultValue="#{NULL}" name="table_qualifier" optional="true" sqlType="STRING"/>
    <param defaultValue="#{NULL}" name="column_name" optional="true" sqlType="STRING"/>
</query>

Different from the previous examples, we have the expected parameters defined with question marks, so, this procedure is expecting up to 4 parameters. And we map the values to those parameters using the param nodes, in our case we have 4 parameters.

Then, in our resource, we define the resource and also the parameters that the resource expects:

<resource method="GET" path="/columns">
    <call-query href="GetColumns">
        <with-param name="table_name" query-param="table_name"/>
        <with-param name="table_owner" query-param="table_owner"/>
        <with-param name="table_qualifier" query-param="table_qualifier"/>
        <with-param name="column_name" query-param="column_name"/>
    </call-query>
</resource>

In this example, we defined a resource /columns and we have some parameters defined, in this case we are mapping the paramters that we will pass when calling the resource to the params defined in the query. In this example we are using the same names for both of resource and query parameters. Those parameters in the resources will be passed as query strings like below:

http://localhost:8280/services/SybaseTest/columns?table_name=product
http://localhost:8280/services/SybaseTest/columns?table_name=product&column_name=price

An example of the output can be seen below:

<?xml version="1.0" encoding="UTF-8"?>
<Columns xmlns="http://ws.wso2.org/dataservice">
    <Column>
        <ColumnName>ID</ColumnName>
        <TypeName>numeric identity</TypeName>
    </Column>
    <Column>
        <ColumnName>name</ColumnName>
        <TypeName>varchar</TypeName>
    </Column>
    <Column>
        <ColumnName>description</ColumnName>
        <TypeName>varchar</TypeName>
    </Column>
    <Column>
        <ColumnName>price</ColumnName>
        <TypeName>decimal</TypeName>
    </Column>
</Columns>

That is for today, now we are able to create WSO2 EI Dataservices consuming data from Sybase servers.

See you in the next post ;)

Xico Junior