esProc can be embedded in a Java application. Invoking an esProc cellset program is like accessing a database via JDBC. During the invocation the cellset program is packaged as a stored procedure, so it is called in the same way as that the latter is called.
1. Basic usage
esProc JDBC resembles a database JDBC driver without a physical table and can be simply regarded as a database with only the stored procedure. Besides, esProc JDBC is a fully embedded computing engine that all computations can be performed within it, whereas the JDBC provided by ordinary databases is only an interface and computations are performed in an independent database server.
First deploy the application as the article esProc Integration & Application: Deploying JDBC introduces.
1.1 Cellset files used by esProc JDBC
Similar to the cross-cellset call with call function, the cellset code used in an esProc JDBC will return a result set through result 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 table sequence holding 100 records and with an orderly-set ID field and a randomly-generated Amount field is created. In A3, the result statement returns A1’s table sequence.
This cellset file will be used in the following to explain how to make a call via esProc JDBC in Java.
1.2 Basic method of Java invocation
Before calling esProc to execute the cellset file, you need to configure related information according to the following instructions:
1) Load the necessary jars when launching a Java application (see esProc Integration & Application: Deploying JDBC for the introduction of jars) . With a WEB application, these jars can be put into WEB-INF/lib folder.
2) Deploy dfxConfig.xml and config.xml
config.xml contains esProc’s basic configuration information, like registration code, searching path, main path, datasource configuration and so on. The file is located in esProc’s [installation directory]\esProc\config folder, in which the information is the same as the settings on esProc’s Option page. Its configuration can be adjusted before the deployment.
During the deployment, config.xml is allowed to be renamed esprocJDBCconfig.xml in case there are already config.xml files for other purposes under classpath. esProc JDBC driver will find and use esprocJDBCconfig.xml first in preference to config.xml, which will be used when the former does not exist.
Configure in dfxConfig.xml the concurrency value of esProc, information about logs, connection pool, and etc. This file is located in esProc’s [installation directory]\esProc\classes folder.
Note: The configuration files should be copied and put into the application project’s classpath, their names must remain dfxConfig.xml and config.xml, or esprocJDBCconfig.xml , and must not be changed. For detailed explanation about the files, please refer to esProc Integration & Application: Deploying JDBC.
3) Deploy dfx file
The above createTable1.dfx can be put into either the application project’s claspath or the searching path specified by config.xml’s <paths/> node, or the main path specified by <mainPath/>.
4) Call dfx in Java
public void testDataServer(){
Connection con = null;
com.esproc.jdbc.InternalCStatement 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 dfx file
st =(com. esproc.jdbc.InternalCStatement)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);
}
}
}
}
Use “call createTable1()” statement to run the cellset file – createTable1.dfx and return the result as the ResultSet object when calling an esProc file. 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:
2. Call different cellset files
Basic method of Java invocation in 1.3 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 | rerun B2.new(EID,NAME+ ” “+ SURNAME:NAME, GENDER, BIRTHDAY) |
| 5 | else | return “None” |
In this cellset file, data are got from demo, the datasource, and a date parameter – Date – is used:
The datasource demo used in the cellset needs to be configured in config.xml. In this respect, please refer to esProc Integration & Application: Deploying JDBC for details. The connection to the datasource established in the cellset should be closed using db.close() function after it accomplishes its mission.
For the call from Java, the code for establishing a connection and outputting the result is the same as the previous example. The difference lies in the method of calling the file:
// call the stored procedure; createTable2 is the name of dfx file and ? represents the to-be-set parameters
st =(com. esproc.jdbc.InternalCStatement)con.prepareCall(“call createTable2(?)”);
// set the parameters
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 waiting to be set. In this case, st.setObject() needs to be used to set the parameters according to their order; this is irrelevant to the parameter names in the cellset. In this cellset file, employees whose birthdays are on the day presented in the parameter will be listed. Note that the month will start with 0 when the date is set for Calendar. The printed out result is as follows:
During parameter setting, the parameter objects can be directly set according to different parameter types; but the date parameter can be entered in the form of a string, which will be automatically parsed by esProc:
// set the parameter
st.setObject(1, “1/1/1980”);
Note that the format of the string you entered should be in consistent with the date format set in config.xml. After the program is executed, the result is the same as that of the previous example.
In addition to the above calling method, the parameters can be fixed in the statement. For example:
// call the stored procedure; createTable2 is the name of dfx file and the parameter is fixed in the statement
st =(com. esproc.jdbc.InternalCStatement)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 objects cannot be set according to different parameter types; the parameters can only be 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 result statement. As can be seen from this result, the column name will be automatically generated for the result set, making it a standard Result for returning, even if the data returned from the cellset is a single value.