calls path/spl( … )

Description:

Search for a program file locally via esProc JDBC for execution; if the file can’t be found, then search the server; it is used for scenarios where multiple pairs of parameters are passed in.

Syntax:

calls path/spl(…)

Note:

The function calls script file spl to execute it while passing in parameters …. It first searches local cellset files with absolute or relative search paths for the desired one. If the file can’t be found locally, it searches the server (configure the server list in the raqsoftConfig.xml file). Once the computation is completed, it concatenates members of the result set to return it as a sequence; if there are multiple result sets, then return a sequence of them.

Similar to calling a stored procedure in the normal database driver, the function collects a sequence of parameters, passes it to the script to execute, uses con.prepareCall() to call a statement, and after Statement is generated, executes it with st.executeQuery() and returns the final result set.

When calling the function, values arg1,... will be assigned to the script files parameters in turn, rather than according to the parameter names in its parameter list.

Parameters:

path

The relative search path or absolute search path of a file; it is the relative search path when this parameter is absent

spl

A script file .splx/.spl/.dfx, whose extension is determined according to the order of .splx/.spl/.dfx

Parameter. Use comma to separate the multiple parameters. Parameter names are irrelevant to the acceptance of values, which, instead, will be assigned to the parameters in order

Example:

Below is cellset file demo_calls_dfx.dfx, where Department and Salary are cellset parameters:

 

A

B

1

for (Department.len()

 

2

 

=@|file("Employee.csv").import@ct().select(DEPT==Department(A1)&&SALARY>Salary(A1))

Test code:

   

  public void testDataServer(){

    Connection con = null;

    java.sql.PreparedStatement st;

      try{

      // Establish connection

      Class.forName("com.esproc.jdbc.InternalDriver");

      con= DriverManager.getConnection("jdbc:esproc:local://");

      // Use addBatch() when there are multiple pairs of parameters

      CallableStatement cst = con.prepareCall("calls demo_calls_dfx(?,?)");

      final String[] DEPTS = new String[] { "R&D", "Sales", "Marketing" };

      for (int i = 0; i < DEPTS.length; i++) {

        cst.setString(1, DEPTS[i]);

        cst.setInt(2, 12000);

        cst.addBatch();

      }

      ResultSet set = cst.executeQuery();

      //(2) When there is only one pair of parameters, just set them and execute script directly:

    /*  CallableStatement cst = con.prepareCall("calls demo_calls_dfx(?,?)");

      cst.setString(1, "R&D");

      cst.setInt(2, 15000);

      ResultSet set = cst.executeQuery(); */

      //(3) Static parameters can also be used:

    /*  CallableStatement cst = con.prepareCall("calls demo_calls_dfx(\"R&D\",15000)");

      ResultSet set = cst.executeQuery();*/

 

      ResultSetMetaData meta = set.getMetaData();

      while (set.next()) {

        for(int i=0; i<meta.getColumnCount(); i++){           

          System.out.print(set.getObject(i+1) + "\t");

        }

        System.out.println();

      }

      }

      catch(Exception e){

      System.out.println(e);

      }

      finally{

      // Close connection

      if (con!=null) {

      try {

      con.close();

      }

      catch(Exception e) {

      System.out.println(e);

      }

      }

      }

         }