Connecting to an Informix Stored Procedure with Data Services Server

Connecting to an Informix Stored Procedure with Data Services Server

In this post I will discuss in steps how to connect to an Informix database using WSO2 Data Services Server. For this post, I will use the Informix developer edition and the WSO2 Data Services Server3.2.2 on a windows 7 Operating system.

The objective is to create a data service using the UI of WSO2 Data Services Server and extract the customers first and last names by passing the first name as the input.

Lets create a stored procedure in informix as below. This will return a customers first and last names when we pass the first name as a parameter to first name

Create Procedure read_userdata (lastname CHAR(25))
RETURNING CHAR(25),CHAR(25);
DEFINE p_lname,p_fname CHAR(15);

SELECT firstn,lastn INTO u_fname,u_lname FROM customer WHERE lastn = lastname;

RETURN u_fname, u_lname;
END PROCEDURE;

The data services created via Data Services Server will be as below. This is the xml file created from the system when we create a new stored procedure using the UI on WSO2 Data Services Server3.2.2.

<data name="customer_sp_DataService" serviceNamespace="testDS" transports="http https">
   <config id="default">
      <property name="carbon_datasource_name">data_source1</property>
   </config>
   <query id="select_all_customer_query" useConfig="default">
      <sql>EXECUTE PROCEDURE read_userdata(:lastn)</sql>
      <result element="customers" rowName="customer" useColumnNumbers="true">
         <element column="2" name="lname" xsdType="string"/>
         <element column="1" name="Fname" xsdType="string"/>
      </result>
      <param name="lname" sqlType="STRING"/>
   </query>
   <operation name="select_all_customer_operation">
      <call-query href="select_all_customer_query">
         <with-param name="lname" query-param="lname"/>
      </call-query>
   </operation>
</data>

In the above example, the use of EXECUTE PROCEDURE read_userdata(:lname) we pass the last name as a parameter into the stored procedure and extract the first and last names of the user.