esProc can be embedded in a Java application. This is like calling
an esProc cellset program using the method of accessing a database via JDBC. During
the process the cellset program is packaged as a stored procedure, so the
method of calling it is the same as that of calling the latter.
1. Basic usage
1.1. esProc JDBC’s jars
esProc JDBC resembles a database JDBC driver without a physical
table and can be just regarded as a database with only the stored procedure.
Besides, esProc JDBC is a fully embedded computing engine that can perform all
computations within the JDBC, whereas a database JDBC driver only provides the
interface and actually the computations are performed in an independent
database server.
esProc JDBC includes five basic jars that reside in [installation directory]\esProc\lib folder：
dm.jar esProc computing engine
and JDBC driver
poi-3.7-20101029.jar Process
the access to an Excel file
log4j_128.jar Process logs
icu4j_3_4_5.jar Process internationalization
dom4j-1.6.1.jar Parse configuration
files
If esProc JDBC needs other databases to be the datasources of esProc,
then the driver jars of these databases should be put in place. In particular, hsqldb.jar is needed for using the demo database. If you need to draw
graphics in esProc JDBC, another three jars, xml-apis-ext.jar,
batik-1.7.jar and batik-ext.jar,
for SVG-typed image processing are needed. Note that esProc JDBC requires the JDK1.6
or higher versions.
1.2. Cellset files for use in esProc JDBC
Similar to performing the cross-cellset call using 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：
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 is used to return 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.3. Basic method for the call of Java
Before calling the esProc to execute the cellset file, you need to
configure some related information according to the following instructions:
1) Load the necessary jars.
Load dm.jar and the other jars mentioned above when launching
a Java application. 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.
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 and must not be changed. For detailed explanation about
the two 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 for the call
of Java 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:
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 birthday
is 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.
3. No result or multiple results
It is allowed to return no result during calling a cellset file.
Take the following cellset file - outputData1.dfx - as an example:
The file uses Arg, a
parameter of string sequence type:
A1 directly calls the demo
database to query data, instead of establishing a connection using connect statement. In this case, the
property of demo datasource’s autoConnect in config.xml’s database configuration should be true:
<property
name="autoConnect" value="true"/>
Please refer to esProc
Integration & Application: Deploying JDBC for detailed configuration. To
call the cellset in Java, the code should be as follows:
// call the stored
procedure; outputData1 is the name of dfx file
st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call
outputData1(?)");
// set the parameters
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 file Cities.txt. If no path has
been specified for storing the output file in cellset, store it in the main
path, which is specified by config.xml’s <mainPath/> node. Data of the file are
as follows:
It is probably that a cellset file returns multiple result sets,
like the following cellset file - createTable3.dfx:
This cellset returns the data of 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 respectively. The cellset uses two
parameters, respectively specifying the employees’ 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 dfx file and ? represents the parameters
st =(com. esproc.jdbc.InternalCStatement)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, data of the female and male employees who
are younger than 30 and who come from state of California will be fetched
respectively. The printed out result is as follows:
In the cellset file - createTable3.dfx, a single statement – result A3,B3 - returns two record sequences. The case in Java is the
same as here.
4. Execute statements directly
Besides calling a cellset file, a Java program also uses esProc to
execute statements directly. Actually, serving as a call statement, the former operation can be directly executed. Let’s
look at in detail how other statements can be executed. For example:
// execute the statement
directly and return the result set
st = (com. esproc.jdbc.InternalCStatement)con.createStatement();
ResultSet rs1 =
st.executeQuery("=age(date(\"1/1/1990\"))");
ResultSet rs2 =
st.executeQuery("=5.(~*~)");
Here executeQuery is used
to directly execute the esProc expression starting with = and return the result as a result set. Note that an escape
character needs to precede the double quotation marks in the expression. Use
the similar code to print out the result sets as follows:
Notice that if the return result is a sequence, JDBC will return a
one-column-and-multiple-row result set. Or another method can be used 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 make clear whether there is
a result set. The effect of the corresponding code is the same as the previous
one.
If parameters are needed in the statement, use the operator - (x1, x2,…) to
compute the expression successively and return the last result. For example:
st = (com. esproc.jdbc.InternalCStatement)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 data set query statement starting with $ can also be used in esProc JDBC. For example:
// execute the data set
query statement
st = (com. esproc.jdbc.InternalCStatement)con.createStatement();
ResultSet rs1 =
st.executeQuery("$(demo) select * from CITIES where POPULATION >
2000000");
Note that demo, the 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 = (com. esproc.jdbc.InternalCStatement)con.createStatement();
boolean hasResult1 =
st.execute("createTable3 30,\"California\"");
The cellset file - createTable3.dfx
will be called during the execution. If parameters need to be set for the file,
they should be fixed in the statement and separated from the file name with a
space. The above statement also returns multiple result sets, which are the
same as that got using the other method in the above:
No comments:
Post a Comment