Java Invocation
You can choose to skip this section if you are not a professional programmer. This won’t affect your learning about the other contents of this Tutorial.
esProc can be embedded in a Java application. Invoking an esProc cellset program is like accessing a database via JDBC. For the invocation the cellset program is packaged as a stored procedure, so it is called in the same way as the latter is called.
6.2.1 Basic uses
esProc JDBC resembles a database JDBC driver without a physical table. It can be simply treated as a database equipped only with the stored procedure. As a fully embedded computing engine, it performs all computations independently; whereas database JDBC serves only as connectivity interface and computations are performed in a separate database server.
To use the application, first deploy it as Deploying JDBC explains.
Cellset files used by esProc JDBC
Similar to the cross cellset call with call function, the cellset code used in esProc JDBC returns a result set through return statement, as shown in the following cellset file - createTable1.splx:
|
|
A |
B |
|
1 |
=create(ID,Amount) |
|
|
2 |
for 100 |
>A1.insert(0,#A2,rand(100*100)) |
|
3 |
return A1 |
|
The computation in this cellset is simple: A 100-record table sequence with a sequentially-set ID field and a randomly-generated Amount field is created. In A3, the return statement returns A1’s table sequence. Now we’ll use this cellset file to explain how to call esProc JDBC by Java.
Basic method of Java invocation
Before calling esProc JDBC to execute the cellset file, you need to configure related information according to the following instructions:
1) Load the necessary jars for launching a Java application (see Deploying JDBC for the information of jars). With a WEB application, these jars can be placed into the WEB-INF/lib folder.
2) Deploy the raqsoftConfig.xml file.
The file contains basic information for configuring esProc, including the search path, main path, data source configuration, number of concurrent tasks, log file information and connection pool and so on. The file is located in esProc’s [installation directory]\esProc\config folder, and contains the same information as the settings on esProc’s Option page. For the deployment, the configuration in it can be first adjusted.
Note: The configuration file should be copied and placed into the application project’s class path. The name must remain raqsoftConfig.xml. For more details, refer to Deploying JDBC.
3) Deploy the script file
The above createTable1.splx can be put into either the application project’s classpath or the search path specified by raqsoftConfig.xml’s <splPathList/> node, or the main path specified by <mainPath/>.
4) Call the script file in Java
public void testDataServer(){
Connection con = null;
java.sql.PreparedStatement st;
try{
// establish a connection
Class.forName("com.esproc.jdbc.InternalDriver");
con= DriverManager.getConnection("jdbc:esproc:local://");
// call the stored procedure; createTable1 is the name of the script file
st =con.prepareCall("call createTable1()");
// execute the stored procedure
st.execute();
// get the result set
ResultSet rs = st.getResultSet();
// process the result set simply by printing out its field names and data
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
for ( int c = 1; c <= colCount;c++) {
String title = rsmd.getColumnName(c);
if ( c > 1 ) {
System.out.print("\t");
}
else {
System.out.print("\n");
}
System.out.print(title);
}
while (rs.next()) {
for (int c = 1; c<= colCount; c++) {
if ( c > 1 ) {
System.out.print("\t");
}
else {
System.out.print("\n");
}
Object o = rs.getObject(c);
System.out.print(o.toString());
}
}
}
catch(Exception e){
System.out.println(e);
}
finally{
// close the connection
if (con!=null) {
try {
con.close();
}
catch(Exception e) {
System.out.println(e);
}
}
}
}
To call an esProc file, like the cellset file createTable1.splx, use "call createTable1()" statement to run it and return the result as the ResultSet object. In the subsequent program, simply print out the data of the result set. After the program is executed, the printout result is as follows:
6.2.2 Calling different cellset files
Basic method of Java invocation explained how to call a cellset file in a Java program. But what will you do with different cellset files? Take the following cellset file – createTable2.splx – as an example:
|
|
A |
B |
|
1 |
=connect("demo") |
|
|
2 |
=A1.query("select * from EMPLOYEE") |
=A2.select(month(BIRTHDAY)==month(Date)&&day(BIRTHDAY)==day(Date)) |
|
3 |
>A1.close() |
|
|
4 |
if B2.len()>1 |
return B2.new(EID,NAME+ " "+ SURNAME:NAME, GENDER, BIRTHDAY) |
|
5 |
else |
return "None" |
This cellset file obtains data the demo source and uses a date type parameter Date:
The demo data source used in the cellset needs to be configured in the raqsoftConfig.xml file. For detailed method, refer to Deploying JDBC for details. The data source connection established in a cellset should be closed using db.close() function after it accomplishes its mission.
For a call of the file from Java, the code for establishing a connection and outputting the result is the same as in the previous example. The difference lies in the method of calling the file:
// call the stored procedure; createTable2 is the name of the script file and ? represents a parameter
st =con.prepareCall("call createTable2(?)");
// set the parameter
java.util.Calendar calendar = java.util.Calendar.getInstance();
calendar.set(1980, 0, 4);
st.setObject(1, calendar.getTime());
// execute the stored procedure
st.execute();
// get the result set
ResultSet rs = st.getResultSet();
This cellset file uses a parameter. So, to call the cellset, use "call createTable2(?)", where ? represents the parameter to be get assigned. In this case, method st.setObject() should be used to pass in value of the parameter. With multiple parameters, values are assigned in order, and the input values are not related to names of the cellset parameters. We can also set value for a specified parameter. To do this, we can modify the above statement as st.setObject("Date", calendar.getTime()). This cellset file will list employees whose birthdays are on the day specified by the parameter. Note that the month starts with 0 when we set the date in the Calendar class. The print out result is as follows:
During parameter setting, the parameter object can be directly entered according to the type; but the date type parameter can be entered in the form of a string, which esProc will automatically parse:
//set the parameter
st.setObject(1, "1980-1-4");
Note that the format of the string entered should be in consistent with the date format set in the raqsoftConfig.xml file. After the program is executed, the result is the same as that of the previous example. You can also use a parameter with actual value in the statement. For example:
// call the stored procedure; createTable2 is the name of the script file and the parameter in the statement is an actual value
st =con.prepareCall("call createTable2(\"1980-1-8")");
// execute the stored procedure
st.execute();
// get the result set
ResultSet rs = st.getResultSet();
With this calling method, the parameter object cannot be set according to the data type; it can only use either the numerical type or the string type which will be parsed automatically by esProc. After the above code is executed, the result is as follows:
Since no employees were born on January 8, B5 returns a string – None – using return statement. As can be seen from the above result, column name will be automatically generated for the result set, making it a standard ResultSet for returning, even if the data returned from the cellset is a single value.
6.2.3 Cellset files that return no results or multiple results
An invoked cellset file can have no return value. Take the following cellset file – outputData1.splx – as an example:
|
|
A |
|
1 |
$(demo) select * from CITIES |
|
2 |
=A1.select(Arg.pos(left(NAME,1))>0) |
|
3 |
=file("cities.txt") |
|
4 |
>A3.export@t(A2) |
The file uses the parameter Arg, of sequence string type:
A1 directly calls the demo database to query data, instead of first establishing a connection using connect statement. In this case, the autoConnect property of demo data source in raqsoftConfig.xml’s database configuration should be true:
<property name="autoConnect" value="true"/>
Refer to Deploying JDBC for detailed configuration. Here’s the code for calling the cellset file in Java:
// call the stored procedure; outputData1 is the name of the script file
st =con.prepareCall("call outputData1(?)");
// set the parameter
com.scudata.dm.Sequence seq = new com.scudata.dm.Sequence();
seq.add("A");
seq.add("B");
seq.add("C");
st.setObject(1, seq);
// execute the stored procedure
boolean hasResult = st.execute();
// cannot get the result set as there is no return result; hasResult is false
Note: The cellset file uses a parameter of sequence type, which can only be set by generating a Sequence object. As with this example, a sequence parameter [A,B,C] is set for writing information of cities whose initials are A, B and C to the cities.txt file. If no path has been specified for storing the output file, store it in the main path, which is specified by raqsoftConfig.xml’s <mainPath/> node. The output file is as follows:
A cellset file could return multiple result sets, like createTable3.splx shows:
|
|
A |
B |
|
1 |
$(demo) select EID, NAME+' '+SURNAME FULLNAME, GENDER, STATE, BIRTHDAY from EMPLOYEE |
|
|
2 |
=A1.select(STATE == State && Age(BIRTHDAY)<age) |
|
|
3 |
=A2.select(GENDER=="F") |
=A2\A3 |
|
4 |
return A3 |
return B3 |
This cellset returns the employees who are younger than the specified age and who, at the same time, come from the specified state as two table sequences according to genders. The cellset uses two parameters, respectively specifying the oldest age and the state from which the employees come from:
This cellset file will return multiple result sets when executed.
// call the stored procedure; createTable3 is the name of the script file and ? represents the parameter
st =con.prepareCall("call createTable3(?,?)");
// set the parameters in order
st.setObject(1, 30);
st.setObject(2, "California");
// execute the stored procedure
boolean hasResult = st.execute();
// get the multiple result sets and print them out
while (hasResult) {
ResultSet rs = st.getResultSet();
// print out the results
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
for ( int c = 1; c <= colCount;c++) {
String title = rsmd.getColumnName(c);
if ( c > 1 ) {
System.out.print("\t");
}
else {
System.out.print("\n");
}
System.out.print(title);
}
while (rs.next()) {
for (int c = 1; c<= colCount; c++) {
if ( c > 1 ) {
System.out.print("\t");
}
else {
System.out.print("\n");
}
Object o = rs.getObject(c);
System.out.print(o.toString());
}
}
System.out.println();
// check if there are other return results
hasResult = st.getMoreResults();
}
When the code is executed, the female and male employees who are younger than 30 and who come from the state of California will be fetched respectively. Here’s the printed out result:
In the cellset file - createTable3.splx, a single statement – return A3,B3 – can also be used to return two record sequences. Its invocation in Java is the same as the above.
6.2.4 Executing statements automatically
To use an esProc file in the Java application, besides calling the cellset file, you can also execute the statement directly. That is to say, the invocation of a cellset file can be automatically executed as a call statement. Let’s look at how to do this. For example:
// execute the statement directly and return a result set
st =con.createStatement();
ResultSet rs1 = st.executeQuery("=age(date(\"1/1/1990\"))");
ResultSet rs2 = st.executeQuery("=5.(~*~)");
Here the method of executeQuery is used to directly execute an esProc expression starting with = and return its result as a result set. Note that an escape character needs to precede the double quotation marks enclosing the expression. Here’s the output file if the similar code is used to output the result sets:
Notice that if a returned result is a sequence, JDBC will return a one-column-and-multiple-row result set. There’s another method to call the statement:
// first judge if there exists a result set; get it if there is one
boolean hasResult1 = st.execute("=age(date(\"1/1/1990\"))");
ResultSet rs1,rs2;
if (hasResult1) {
rs1 = st.getResultSet();
}
boolean hasResult2 = st.execute("=5.(~*~)");
if (hasResult2) {
rs2 = st.getResultSet();
}
Based on the statement being executed, the execute function will return a value to show whether there is a result set. The code is functionally equivalent to the previous one.
If parameters are needed in the statement, use the operator - (x1, x2,…) to compute the expressions successively and return the last result. For example:
st =con.createStatement();
ResultSet rs1 = st.executeQuery("=(pi=3.14,r=4,r*r*pi)");
The above code computes the area of a circle. The output result set is as follows:
The parameters used in a statement will be called in a fixed format ?1,?2,… according to the order set for them in PreparedStatement. For example:
PreparedStatement pst = con.prepareStatement("=?1*?1*?2");
pst.setObject(1,3);
pst.setObject(2,3.14);
ResultSet rs = pst.executeQuery();
The code computes the area of a circle with a radius of 3, and returns the following result set:
The data set query statement starting with $ can be used with esProc JDBC. For example:
// execute the data set query statement
st =con.createStatement();
ResultSet rs1 = st.executeQuery("$(demo) select * from CITIES where POPULATION > 2000000");
Note that demo data set used here needs to be configured as automatically connected. The result is as follows:
esProc JDBC also supports executing a piece of cellset code with multiple lines and columns in one statement. To do this, compose the code into a string, with lines separated by \n and columns of the same line separated by \t. Note that the code string should be headed by == instead of = when we try to execute a block of cellset code of multiple rows and columns. For example:
ResultSet rs = st.executeQuery("==demo.cursor(\"select * from EMPLOYEE order by SALARY desc\")\t=[]\nfor A1;SALARY\n\t>B1.insert(0,A2)\nreturn B1
The execution of this string is equivalent to computing the following cellset:
|
|
A |
B |
|
1 |
=demo.cursor("select * from EMPLOYEE order by SALARY desc") |
=[] |
|
2 |
for A1;SALARY |
|
|
3 |
|
>B1.insert(0,A2) |
|
4 |
return B1 |
|
Output the returned ResultSet as follows:
We can also execute a simple SQL statement in a Java application to query a data file in the main directory. For example:
st =con.createStatement();
ResultSet rs = st.executeQuery("$select * from cities.txt");
Similar to a query statement on the data set, a simple SQL statement used in esProc JDBC also begins with symbol $. Difference is that you do not need to specify the data source name. Below is the exported ResultSet:
Similarly, we can perform a simple SQL statement within Java code when calling an esProc script. About simple SQL syntax, see Simple SQL.
6.2.5 Calling remote server via JDBC
We can call a remote server in a calculation via esProc JDBC. Check The Server Cluster to learn more about the configuration and launch of the remote server. To invoke a remote server, we need to configure IPs and port numbers for every remote server in <Units> of the node <JDBC> in the configuration file raqsoftConfig.xml:
<JDBC>
<load>Runtime</load>
<gateway>executeSQL.splx</gateway>
<Units>
<Unit>192.168.0.197:8281</Unit>
</Units>
</JDBC>
When calling a script via JDBC in a Java application, the calculation will be performed locally unless there are problems with the local operation, like missing data files. In those cases, the calculation will be performed on the remote server. If we need to perform a calculation remotely in the first place, just add the parameter ?onlyServer=true to the connecting string. For example:
Class.forName("com.esproc.jdbc.InternalDriver");
con= DriverManager.getConnection("jdbc:esproc:local://onlyServer=true");
st = con.createStatement();
ResultSet rs1 = st.executeQuery("=5.(~*~)");
ResultSet rs2 = st.executeQuery("select * from cities.txt where CID<30");
The program exports ResultSets by executing a query. Here’s the result:
We can check the system information about the calculation on the remote server’s console:
