6.2 Java Invocation

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.

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.dfx:

 

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 license, 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 dfx file

The above createTable1.dfx can be put into either the application project’s classpath or the search path specified by raqsoftConfig.xml’s <dfxPathList/> node, or the main path specified by <mainPath/>.

4)  Call the dfx 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 dfx 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.dfx, 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 printed out result is as follows:

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.dfx 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 dfx 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, 1);

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(?)", in which ? represents the parameter needing to be get assigned. The method of st.setObject() needs to be used to pass in the value of the parameter. With multiple parameters, values are assigned according to their order, instead of their names. This cellset file will list employees whose birthdays are on the day specified by the parameter. Note that the month starts with 0 when the date is set in the Calendar class. The printed 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, "1/1/1980");

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 dfx file and the parameter in the statement is an actual value

st =con.prepareCall("call createTable2(\"2/29/1980\")");

// 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 February 29, 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. 

Cellset files that return no results or multiple results

An invoked cellset file can have no return value. Take the following cellset file - outputData1.dfx - 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 dfx file

st =con.prepareCall("call outputData1(?)");

// set the parameter

com.raqsoft.dm.Sequence seq = new com.raqsoft.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.dfx 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 dfx 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.dfx, 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.

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 arg1,arg2,… according to the order set for them in PreparedStatement. For example:

PreparedStatement pst = con.prepareStatement("=arg1*arg1*arg2");

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:

Use the file name to call a cellset file so that call function can be omitted. For example:

st =con.createStatement();

boolean hasResult1 = st.execute("createTable3 30,\"California\"");

The cellset file - createTable3.dfx will be called during the execution. If any parameter needs to be set for the file, it should be an actual value separated from the file name by a space. The above statement returns the same multiple result sets as those returned using the preceding method:

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. For example:

ResultSet rs = st.executeQuery("=[1,1]\nfor 10\t>A1=A1|(A1.m(-1)+A1.m(-2))\nreturn A1");");

The execution of this string is equivalent to computing the following cellset:

 

A

B

1

=[1,1]

 

2

for 10

>A1=A1|(A1.m(-1)+A1.m(-2))

3

return A1

 

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.execute("select * from cities.txt");

The exported ResultSet is like this:

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 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.dfx</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: