SQLFire - Generic ExecuteQuery Using Procedure Parameters with DYNAMIC RESULT SETS

In this previous post I showed how to use procedure parameters with DYNAMIC RESULT SETS.

http://theblasfrompas.blogspot.com.au/2012/09/sqlfire-using-procedure-parameters-with.html

Now I extend that and add a java class method to my stored procedures that allows an SQL query with bind variables and parameters to be used to execute arbitrary SQL. Wasn't tested that well and has no error checking BUT it generally works.

Add a method as shown below to handle a generic query with bind variables

public static void executeQuery(String sql, String params, ResultSet[] resultSet1) throws SQLException 
  {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rset = null;

    try
    {
      conn = DriverManager.getConnection("jdbc:default:connection");
      pstmt = conn.prepareStatement(sql);
      // assume that parameters are correctly set; usually for most types
      // one can just do a setString()
      String[] args = params.split(",");
      int index = 1;
      for (String arg : args) 
      {
        pstmt.setString(index++, arg.trim());
      }
      
      resultSet1[0] = pstmt.executeQuery();

    }
    catch (SQLException se)
    {
      throw new RuntimeException("Error in stored procedure executeQuery - ", se);
    }   
  }

Create the SQL for the stored procedure as shown below.

CREATE PROCEDURE executeQuery (IN query VARCHAR(200), IN params VARCHAR(200))
LANGUAGE JAVA 
PARAMETER STYLE JAVA 
READS SQL DATA 
DYNAMIC RESULT SETS 1 
EXTERNAL NAME 'pas.au.vmware.sqlfire.procs.DemoProc.executeQuery';

Test it from sqlf as shown below.

sqlf> run 'test.sql';
sqlf> maximumdisplaywidth 120;
sqlf> call executeQuery('select * from emp where deptno = ?', '10');
EMPNO      |ENAME     |JOB      |MGR        |HIREDATE  |SAL        |COMM       |DEPTNO     
-------------------------------------------------------------------------------------------
7934       |MILLER    |CLERK    |7782       |1982-01-23|1300       |NULL       |10         
7782       |CLARK     |MANAGER  |7839       |1981-06-09|2450       |NULL       |10         
7839       |KING      |PRESIDENT|NULL       |1981-11-17|5000       |NULL       |10         

3 rows selected
sqlf> call executeQuery('select * from emp where (deptno = ? or deptno = ?)', '10,30');
EMPNO      |ENAME     |JOB      |MGR        |HIREDATE  |SAL        |COMM       |DEPTNO     
-------------------------------------------------------------------------------------------
7934       |MILLER    |CLERK    |7782       |1982-01-23|1300       |NULL       |10         
7782       |CLARK     |MANAGER  |7839       |1981-06-09|2450       |NULL       |10         
7839       |KING      |PRESIDENT|NULL       |1981-11-17|5000       |NULL       |10         
7521       |WARD      |SALESMAN |7698       |1981-02-22|1250       |500        |30         
7499       |ALLEN     |SALESMAN |7698       |1981-02-21|1600       |300        |30         
7900       |JAMES     |CLERK    |7698       |1981-12-03|950        |NULL       |30         
7844       |TURNER    |SALESMAN |7698       |1981-09-08|1500       |0          |30         
7654       |MARTIN    |SALESMAN |7698       |1981-09-28|1250       |1400       |30         
7698       |BLAKE     |MANAGER  |7839       |1981-05-01|2850       |NULL       |30         

9 rows selected
sqlf> call executeQuery('select * from emp where deptno = ? and ename like ?', '10,%MILL%');
EMPNO      |ENAME     |JOB      |MGR        |HIREDATE  |SAL        |COMM       |DEPTNO     
-------------------------------------------------------------------------------------------
7934       |MILLER    |CLERK    |7782       |1982-01-23|1300       |NULL       |10         

1 row selected

If you wanted to test with from a java client you could do it as follows

package clientaccess;

import java.sql.*;
import java.sql.ResultSetMetaData;

public class ExecuteQuery
{
  public ExecuteQuery()
  {
  }


  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(String query, String params)
    throws SQLException
  {
    String sql = "call executeQuery(?, ?)";

    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rset = null;
    ResultSetMetaData meta;
    
    try
    {
      conn = getConnection();
      pstmt = conn.prepareCall(sql);
      pstmt.setString(1, query);
      pstmt.setString(2, params);
      pstmt.execute();

      rset = pstmt.getResultSet();

      meta = rset.getMetaData();
      int cols = meta.getColumnCount();

      System.out.println("\n-> Query results for : " + query);
      
      while(rset.next())
      {   
          for (int i=1; i <= cols; i++) 
          {
              System.out.print(rset.getString(i)+"\t");
          }
        
          System.out.print("\n");
      }
      
    }
    finally
    {
      if (rset != null)
      {
        rset.close();
      }

      if (pstmt != null)
      {
        pstmt.close();
      }

      if (conn != null)
      {
        conn.close();
      }
    }

  }

  public static void main(String[] args)
    throws SQLException
  {
    ExecuteQuery test = new ExecuteQuery();
    System.out.println("Started at " + new java.util.Date());
    test.run("select * from emp where deptno = ?", "20");
    test.run("select * from emp where (deptno = ? or deptno = ?)", "20,10");
    test.run("select * from emp where deptno = ? and ename like ?", "10,%MILL%");
    System.out.println("\nFinished at " + new java.util.Date());
  }
}

Output

Started at Fri Sep 14 22:35:40 EST 2012

-> Query results for : select * from emp where deptno = ?
7902    FORD    ANALYST    7566    1981-12-03    3000    null    20   
7876    ADAMS    CLERK    7788    1983-01-12    1100    null    20   
7788    SCOTT    ANALYST    7566    1982-12-09    3000    null    20   
7369    SMITH    CLERK    7902    1980-12-17    800    null    20   
7566    JONES    MANAGER    7839    1981-04-02    2975    null    20   

-> Query results for : select * from emp where (deptno = ? or deptno = ?)
7934    MILLER    CLERK    7782    1982-01-23    1300    null    10   
7782    CLARK    MANAGER    7839    1981-06-09    2450    null    10   
7839    KING    PRESIDENT    null    1981-11-17    5000    null    10   
7902    FORD    ANALYST    7566    1981-12-03    3000    null    20   
7876    ADAMS    CLERK    7788    1983-01-12    1100    null    20   
7788    SCOTT    ANALYST    7566    1982-12-09    3000    null    20   
7369    SMITH    CLERK    7902    1980-12-17    800    null    20   
7566    JONES    MANAGER    7839    1981-04-02    2975    null    20   

-> Query results for : select * from emp where deptno = ? and ename like ?
7934    MILLER    CLERK    7782    1982-01-23    1300    null    10   

Finished at Fri Sep 14 22:35:41 EST 2012