Description:
Execute the SPL script via esProc JDBC; used for scenarios where multiple pairs of parameters are passed in.
Syntax:
calls path/spl(…)
Note:
The function executes a SPL script via esProc JDBC; used for scenarios where multiple pairs of parameters are passed in
Parameter path is path of the spl script file. First search local paths for the spl file; and then the server (configure the list of servers in raqsoftConfig.xml) if the script file cannot be found.
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.
In calls path/spl(…), “(…)” can be omitted. By default, parameter values are passed in according to the order of parameters defined in the script.
Parameter:
|
path |
The relative search path or absolute search path of a script file; by default, it is the relative path |
|
spl |
A script file of .splx/.spl/.dfx format, whose extension is determined according to the order of .splx/.spl/.dfx. |
|
… |
Parameter values passed to the spl script file; they are assigned to the parameters in order without considering the parameter names. Use comma to separate the multiple parameters. |
Example:
Below is cellset file demo_calls_splx.splx, 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_splx(?,?)");
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_splx(?,?)");
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_splx(\"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);
}
}
}
}