Calling MySQL/Oracle Functions in a Query

Given below is how to add a MySQL/Oracle function call to a data service.

Calling a MySQL function

Assume you have the following MySQL function, which takes a string parameter and returns the same as output. Create a database before executing the query.

create function myFunction(p_inparam varchar(20)) 
  returns varchar(20)
  begin
     declare output_text varchar(20);
     set output_text = p_inparam;
     return output_text;
  end

To call this function from the data service, add a query to the data service definition file (.dbs) pointing to an RDBMS datasource that connects to the MySQL database. For example,

<sql>select myFunction('WSAS') as ABC</sql>

Given below is the complete data service configuration we use here:

<data name="sqlfunctionService">
   <config id="mynew">
      <property name="driverClassName">com.mysql.jdbc.Driver</property>
      <property name="url">jdbc:mysql://localhost:3306/mentor_graphics&lt;/property>
      <property name="username">root</property>
      <property name="password">root</property>
   </config>
   <query id="NewfunctionQuery" useConfig="mynew">
      <sql>select myFunction('WSAS') as ABC</sql>
      <result element="wsas" rowName="wsas">
         <element column="output_text" name="n_param" xsdType="string"/>
      </result>
      <param name="imparam" sqlType="STRING"/>
   </query>
   <operation name="functionop">
      <call-query href="NewfunctionQuery">
         <with-param name="imparam" query-param="imparam"/>
      </call-query>
   </operation>
</data>

You can also call this function in the Query Details page of the management console as follows:

Calling an Oracle function

Assume you have the following Oracle stored function, which returns the total number of entries in a table:

CREATE OR REPLACE FUNCTION myfunction(ename IN VARCHAR, eid IN NUMBER) RETURN INTEGER 
AS myCount INTEGER;
BEGIN
    INSERT INTO TEAMS values(eid, ename);
    SELECT COUNT(*) into myCount from TEAMS;
    RETURN myCount;
END;
/

Create a table before executing the query as follows:

CREATE TABLE TEAMS(id INTEGER, team VARCHAR(30));

To call this function from the data service, add a query to the data service definition file (.dbs). For example,

"{call ?:=myfunction(?,?)}"

First input parameter carries the return value of the function. Other two parameters are inputs to the function. You must define an Input parameter with OUT type to get the result of function (i.e., the first parameter in the query above). Then, define a Output parameter to get this value as a result set from the data service. The following code segment does this:

<result element="TotalTeams" rowName="">
    <element name="totalTeams" column="totalTeams" xsdType="xs:integer" />
</result>
<param name="totalTeams" sqlType="INTEGER" type="OUT" ordinal="1" />

Given below is the complete data service configuration we use here:

<data name="testOracleFunction">
   <config id="or">
      <property name="org.wso2.ws.dataservice.driver">oracle.jdbc.driver.OracleDriver</property>
      <property name="org.wso2.ws.dataservice.protocol">jdbc:oracle:thin:user/pwd@localhost:1521/XE</property>
      <property name="org.wso2.ws.dataservice.user">user</property>
      <property name="org.wso2.ws.dataservice.password">pwd</property>
   </config>
   <query id="q1" useConfig="or">
      <sql>{call ?:=myfunction(?,?)}</sql>
      <result element="TotalTeams" rowName="">
         <element name="totalTeams" column="totalTeams" xsdType="xs:integer" />
      </result>
      <param name="totalTeams" sqlType="INTEGER" type="OUT" ordinal="1" />
      <param name="ename" sqlType="STRING" ordinal="2" />
      <param name="eid" sqlType="INTEGER" ordinal="3" />
   </query>
   <operation name="op1">
      <call-query href="q1">
         <with-param name="ename" query-param="ename" />
         <with-param name="eid" query-param="eid" />
      </call-query>
   </operation>
</data>

You can call this function in the Query Details page of the management console as before.