Backup command for MAC

Using rsync to backup a directory onto a USB with files which have changed and or been removed.

Note for myself:

rsync -avrzeh --progress --delete /Users/papicella/vmware/ /Volumes/Wizzy\ Wig\ Mac\ Drive/Users/papicella/vmware/

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

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

Load 8 Million Rows in 88 Seconds – NewSQL Speed

Recently published this SQLFire load speed scenario on the VMware vFabric blog

http://blogs.vmware.com/vfabric/2012/09/load-8-million-rows-in-88-seconds-newsql-speed.html