Creating WSO2 EI DataServices With Sybase Database

this post will show a quick example of how we can connect with the 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's start.

you might also be interested in: wso2 enterprise integrator for distributed messaging

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 docker hub: 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 some time, ~30s, we can connect to the sybase instance. the default credentials can be found in the docker image page in docker hub. we can use a sql client to connect to it; a good one is dbeaver.

in our example, we will connect with the admin user(sa). in dbeaver, the connection details will look like below:

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

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, and 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:

1. log into carbon: https://localhost:9443/carbon
2. go into configure tab -> datasources, and then click add datasource:
3. 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

if everything is set up correctly, 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:

<data name="sybasetest" transports="http https local">
   <config enableodata="false" id="sybase">
      <property name="carbon_datasource_name">sybase</property>
   </config>
   <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>
   <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>
   <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>
   <operation name="queryallproducts">
      <call-query href="query_all_products"/>
   </operation>
   <operation name="getcolumns">
      <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>
   </operation>
   <resource method="get" path="/products">
      <call-query href="query_all_products"/>
   </resource>
   <resource method="get" path="/roles">
      <call-query href="getroles"/>
   </resource>
   <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>
</data>

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 expects 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 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 parameters 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 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 :)

further reading

database connectivity and transaction in python

python odbc custom api