Calling Stored Procedures


Sections


Overview

Stored procedures can be used within WaveMaker projects either by calling createSQLQuery() from java, or by adding the procedure to the hibernate configuration.

Adding the procedure to the hibernate configuration is very similar to using stored procedures and functions in Hibernate.

  • The named query is defined in a ql.xml file.
  • The ql.xml file is added to the service definition
  • A java service function is defined to call the named query.
  • A service variable is created to invoke the java service method.
See also: hibernate docs


MySQL

Example using hibernate

  • Import Sakila Database
  • Create new file, Sakila_Procedures.ql.xml, save in $Project\services\Sakila\src\com\sakila\data folder
  • Edit $Project\services\Sakila\src\Sakila.spring.xml file, add above ql.xml file to mappingResources list of the SessionFactory Bean, just under app-queries.ql.xml
  • Add new java service to project
  • Use Service Variable to invoke functions in Java Service
WM 6.3 Project Export
Uses sakila database

Sakila_Procedures.ql.xml file:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC 
	"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
	"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <sql-query 
        name="FilmInStock"
        callable="true"
        comment="Call the film_in_stock procedure">
        <query-param name="film_id" type="java.lang.Integer"/>
        <query-param name="store_id" type="java.lang.Integer"/>
    
        <![CDATA[
            { call film_in_stock(:film_id, :store_id, ?) }
        ]]>
    </sql-query>
</hibernate-mapping>
Adding Sakila_Procedures.ql.xml file to Bean properties list in \src\sakila.spring.xml :
...
<value>com/sakila/data/app-queries.ql.xml</value>

<!-- Below added for Stored Procedure Support -->
<value>com/sakila/data/Sakila_Procedures.ql.xml</value>

  </list>
</property>

Java Service Source Code

package dev.wavemaker.example;

//Java util
import java.util.List;
import java.util.ArrayList;

//Hibernate
import org.hibernate.Session;
import org.hibernate.Query;

//WM Runtime 
import com.wavemaker.runtime.RuntimeAccess;

//Imported Database Service and Types
import com.sakila.Sakila;  

/**
 * Example Java Service that calls stored procedures in MySQL from  WaveMaker project
 */
public class Sproc extends com.wavemaker.runtime.javaservice.JavaServiceSuperClass {
    /* Pass in one of FATAL, ERROR, WARN,  INFO and DEBUG to modify your log level;
     *  recommend changing this to FATAL or ERROR before deploying.  For info on these levels, look for tomcat/log4j documentation
     */
    public Sproc() {
       super(INFO);
    }
  
  private Sakila getSakilaService() {
	// get the Sakila Data Service Class via the Runtime
	Sakila sakila = (Sakila) RuntimeAccess.getInstance().getService(Sakila.class);        
	return sakila;
  }

  
  
    public Integer runFilmInStockCount(Integer filmId, Integer storeId) {
        log(INFO, "runFilmInStockCount, filmId: " + filmId + " storeId: " + storeId);
    	Sakila sakila = getSakilaService();
    
        try {
        	// begin a transaction.
        	sakila.begin();
        	Session sess = sakila.getDataServiceManager().getSession();
           
		//use the session to get the query in the ql.xml file
		Query q = sess.getNamedQuery("FilmInStock");
            
		//set the Parameters
        	q.setParameter("film_id", filmId);
        	q.setParameter("store_id", storeId);

                //Invoke the query
        	Integer result = q.list().size();
        
		// end the transaction
        	sakila.commit();
            
        	return result;
        }

        catch (RuntimeException ex) {
		//rollback and re-throw
		sakila.rollback();
          throw ex;
        }
    }
  

  public List<Integer> runFilmInStockIDs(Integer filmId, Integer storeId) {
    log(INFO, "runFilmInStockIDs, filmId: " + filmId + " storeId: " + storeId);
    Sakila sakila = getSakilaService();
    
        try {
	        // begin a transaction.
	        sakila.begin();
	        Session sess = sakila.getDataServiceManager().getSession();
           
		//use the session to get the query in the ql.xml file
		Query q = sess.getNamedQuery("FilmInStock");
            
		//set the Parameters
	        q.setParameter("film_id", filmId);
	        q.setParameter("store_id", storeId);

                //invoke the query
	        List result = q.list();

	        // end the transaction
	        sakila.commit();
            
        	return result;
        }

        catch (RuntimeException ex) {
		//rollback and re-throw
        sakila.rollback();
        throw ex;
        }
    }
  

    public String sampleJavaOperation() {
       String result  = null;
       try {
        log(INFO, "Starting sample operation");
        result = "Hello World";
        log(INFO, "Returning " + result);
       } 
     catch(Exception e) {
        log(ERROR, "The sample java service operation has failed", e);
       }
       return result;
    }

}

HT: MySQL Stored Procedure
version: 3.2


Example using createSQLQuery

These examples use java calling createSQLQuery to call stored procedures. MySQL Database included in zip file.

Format a data grid

stored procedure:
xtab - Produces a crosstab result set
GetGroupedData - Selects data in Groups table into a temporary table, substitutes GroupDate by MONTHNAME(GroupDate) and calls the xtab procedure to produce a crosstab display of the temporary table.

Download | Forum Link WM version: 6.0

Java Service Source Code

import java.util.ArrayList;
import java.util.List;
import org.hibernate.Session;
import com.testdb.data.*;
import com.testdb.*;
import com.wavemaker.runtime.RuntimeAccess;
public class GroupsService
{
  public List<GroupedData> GetGroupedData()
  {

    try
    {
      List<GroupedData> grouped_data = new ArrayList<GroupedData>();
      TestDb testdb = (TestDb) RuntimeAccess.getInstance().getService(
          TestDb.class);
      testdb.begin();
      Session session = testdb.getDataServiceManager().getSession();

      List<?> tempList = session.createSQLQuery("Call GetGroupedData();")// "call xtab('GroupDate', 'GroupDate', 'FROM Groups', 'GroupCount', 'GroupName', 'FROM Groups GROUP BY GroupName')")
      .list();
      for (Object o : tempList)
      {
        if (o.getClass().isArray())
        {
          Object[] oa = (Object[]) o;
          if (14 != oa.length) { throw new RuntimeException(
              "length of oa was: " + oa.length); }

          GroupedData gd = new GroupedData();
          gd.setGroupName(oa[0].toString());
          gd.setJanuary(Integer.parseInt(oa[1].toString()));
          gd.setFebruary(Integer.parseInt(oa[2].toString()));
          gd.setMarch(Integer.parseInt(oa[3].toString()));
          gd.setApril(Integer.parseInt(oa[4].toString()));
          gd.setMay(Integer.parseInt(oa[5].toString()));
          gd.setJune(Integer.parseInt(oa[6].toString()));
          gd.setJuly(Integer.parseInt(oa[7].toString()));
          gd.setAugust(Integer.parseInt(oa[8].toString()));
          gd.setSeptember(Integer.parseInt(oa[9].toString()));
          gd.setOctober(Integer.parseInt(oa[10].toString()));
          gd.setNovember(Integer.parseInt(oa[11].toString()));
          gd.setDecember(Integer.parseInt(oa[12].toString()));
          gd.setTotal(Integer.parseInt(oa[13].toString()));
          grouped_data.add(gd);
        }
        else
        {
          throw new RuntimeException("o was " + o.getClass()
              + ", not array");
        }
      }

      return grouped_data;
    }
    catch (Exception ex)
    {
      return null;
    }
  }

}

Oracle Specific

"For Oracle the following rules apply:

A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define a REF CURSOR type. See Oracle literature for further information." Source
If a procedure does not have an OUT and does not use SYS_REFCURSOR, the error "Invalid column index" will be raised.


MSSQL/Sybase Specific

"For Sybase or MS SQL server the following rules apply:

The procedure must return a result set. Note that since these servers can return multiple result sets and update counts, Hibernate will iterate the results and take the first result that is a result set as its return value. Everything else will be discarded. If you can enable SET NOCOUNT ON in your procedure it will probably be more efficient, but this is not a requirement."

ref

Here is more doc for Stored Functions on MS SQL Stored Function Documentation and Stored Procedure Documentation


      Share/Bookmark
© 2004-  WaveMaker, Inc