Firebird sample with WSO2 DSS

I'm going to provide instructions to showcase how we can use Firebird data-source with WSO2 DSS.

Note:
I will be using the employee database(/opt/firebird/examples/empbuild/employee.fdb) which is available by-default with the Firebird 2.5.7  in this post.

Following are the Firebird/JDBC driver and WSO2 DSS versions which I have tried.

• Firebird: 2.5.X
• JDBC Driver: jaybird-full-2.2.12
• WSO2 DSS: 3.5.1

Steps:

1. Download the JDBC driver for Firebird and copy into the <DSS_HOME>/repository/components/lib/ directory.
2. Start the DSS node.(or restart if it's already started)
3. Add a new Data Service with the following configuration.

Datasource type: RDBMS

Database Engine: Generic

Driver Class: org.firebirdsql.jdbc.FBDriver

URL: jdbc:firebirdsql://localhost:3050//opt/firebird/examples/empbuild/employee.fdb

* Provide Host, Port, Database Path, User Name, Password accordingly.

Create and test the Datasource connection.

Save and proceed next, to add the query

select EMP_NO,FIRST_NAME,
LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,SALARY from employee where EMP_NO=:EMP_NO

Add Input/Output mappings accordingly.

Save and proceed next, to add the operation.

 

Save and finish.

Now if you go to the Home> Manage> Services> List> firebirdTest> Edit Data Source(XML Edit) you can see the following Data Service definition.

<data disableStreaming="true" name="firebirdTest" transports="http https local">
   <config enableOData="false" id="emp">
      <property name="driverClassName">org.firebirdsql.jdbc.FBDriver</property>
      <property name="url">jdbc:firebirdsql://localhost:3050//opt/firebird/examples/empbuild/employee.fdb</property>
      <property name="username">SYSDBA</property>
      <property name="password">admin</property>
   </config>
   <query id="select" useConfig="emp">
      <sql>select EMP_NO,FIRST_NAME,&#xd;LAST_NAME,PHONE_EXT,HIRE_DATE,DEPT_NO,SALARY from employee where EMP_NO=:EMP_NO</sql>
      <result element="Entries" rowName="Entry">
         <element column="EMP_NO" name="EMP_NO" xsdType="string"/>
         <element column="FIRST_NAME" name="FIRST_NAME" xsdType="string"/>
         <element column="LAST_NAME" name="LAST_NAME" xsdType="string"/>
         <element column="PHONE_EXT" name="PHONE_EXT" xsdType="string"/>
         <element column="HIRE_DATE" name="HIRE_DATE" xsdType="string"/>
         <element column="DEPT_NO" name="DEPT_NO" xsdType="string"/>
         <element column="SALARY" name="SALARY" xsdType="string"/>
      </result>
      <param name="EMP_NO" sqlType="STRING"/>
   </query>
   <operation disableStreaming="true" name="getEmp">
      <call-query href="select">
         <with-param name="EMP_NO" query-param="EMP_NO"/>
      </call-query>
   </operation>
</data>

How to test the data-service(Try-it tool)

Go to Home> Manage> Services> List> firebirdTest> Try this service.

Provide the EMP_NO and press send.

Further information on query/input and output mappings/operation can be found here.