SQLFire - Using Procedure Parameters with DYNAMIC RESULT SETS

In this example below we use DYNAMIC RESULT SETS with stored procedures. When you configure a procedure using the CREATE PROCEDURE statement, SQLFire assembles the method parameters and passes them to the procedure implementation class using Java reflection

The demo below is using the classic DEPT/EMP schema data. If you want to follow it setup SQLFire with that data as shown in this previous blog entry.

http://theblasfrompas.blogspot.com/2011/12/sqlfire-in-few-minutes.html

1. Create a Java Stored Procedure class with a single method as follows.

package pas.au.vmware.sqlfire.procs;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;

public class DemoProc
{
  public static void queryEmpsinDepartments(String deptno, ResultSet[] resultSet1) throws SQLException
  {
    String sql = "select empno, ename, deptno from emp where deptno = ?";
    
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rset = null;
    
    try
    {
      conn = DriverManager.getConnection("jdbc:default:connection");
      pstmt = conn.prepareStatement(sql);
      pstmt.setInt(1, Integer.parseInt(deptno));
      resultSet1[0] = pstmt.executeQuery();
      
    }
    catch (SQLException se)
    {
      throw new RuntimeException("Error in stored procedure queryEmpsinDepartments - ", se);
    }
  }
}

2. Add the JAR file to the classpath for your SQLFire distributed system prior to starting the system. Here we just use the CLASSPATH variable but you could also load the JAR file into the system itself.

export CUR_DIR=`pwd`
export CLASSPATH=$CUR_DIR/lib/procs.jar

3. Create a stored procedure as follows.

CREATE PROCEDURE queryEmpsinDepartments (IN deptno VARCHAR(2))
LANGUAGE JAVA 
PARAMETER STYLE JAVA 
READS SQL DATA 
DYNAMIC RESULT SETS 1 
EXTERNAL NAME 'pas.au.vmware.sqlfire.procs.DemoProc.queryEmpsinDepartments';

4. Invoke from a client as shown below.

package clientaccess;

import java.sql.*;

public class TestQueryEmpsinDepartments
{
  public TestQueryEmpsinDepartments()
  {
  }


  public static Connection getConnection() throws SQLException
  {

    String thinConn = "jdbc:sqlfire://localhost:1527/";
    Connection conn = DriverManager.getConnection(thinConn);
    conn.setAutoCommit(false);
    return conn;
  }
  
  public void run () throws SQLException
  {
    String sql = "call queryEmpsinDepartments(?)";
    
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rset = null;
    
    try
    {
      conn = getConnection();
      pstmt = conn.prepareCall(sql);
      pstmt.setString(1, "10");
      pstmt.execute();
      
      rset = pstmt.getResultSet();
      
      System.out.println("** Employees in department with id 10 **");
      
      while (rset.next())
      {
        System.out.println(String.format("Emp[%s, %s, %s]", rset.getInt(1), rset.getString(2), rset.getInt(3)));
      }
    }
    finally
    {
      if (rset != null)
      {
        rset.close();
      }
      
      if (pstmt != null)
      {
        pstmt.close();
      }
      
      if (conn != null)
      {
        conn.close();
      }
    }
    
  }
  
  public static void main(String[] args) throws SQLException
  {
    TestQueryEmpsinDepartments test = new TestQueryEmpsinDepartments();
    System.out.println("Started at " + new java.util.Date());
    test.run();
    System.out.println("Finished at " + new java.util.Date());
  }
} 

Output

Started at Fri Sep 14 10:59:20 EST 2012
** Employees in department with id 10 **
Emp[7934, MILLER, 10]
Emp[7782, CLARK, 10]
Emp[7839, KING, 10]
Finished at Fri Sep 14 10:59:20 EST 2012

For more information see the link below.

http://pubs.vmware.com/vfabric5/index.jsp?topic=/com.vmware.vfabric.sqlfire.1.0/developers_guide/topics/server-side/dap-impl-parameters.html