esProc JDBC
esProc can be embedded into Java applications. The latter can call the cellset program written in esProc using the JDBC-style of connection. The method of calling the esProc program is the same as that of calling the stored procedure. The following is a brief introduction of how to use esProc JDBC.
esProc JDBC is like an incomplete database JDBC driver without physical tables. It can be regarded simply as a database that only supports the stored procedure. In addition, it has a built-in computing engine, thus no standalone servers are needed.
esProc JDBC has three basic jars, which are all situated in [installation directory]\esProc\lib:
esproc-bin-xxxx.jar esProc computing engine and JDBC driver
icu4j_60.3.jar handle internationalization
jdom-1.1.3.jar.jar parse the configuration files
If other databases are to be used as the datasources of esProc JDBC, then the driver jars of these databases should be in place. For example, hsqldb-2.2.8.jar is necessary when using the demo database. Please note that the esProc JDBC requires JDK1.8 or higher versions.
In the cellset code, the result set is returned by return statement.
| A | |
|---|---|
| 1 | =connect("demo") |
| 2 | =A1.query("select * from EMPLOYEE where EID=?",arg1) |
| 3 | >A1.close() |
| 4 | return A2 |
In the application code, arg1 is a cellset parameter. Let’s name this dfx file test.dfx.
Note: The result set of dfx is returned by return statement. When dfx is called, its parameters will get assigned in the order of their appearance, rather than according to the names.
-
Load the necessary jars. Load the basic esProc JDBC jars mentioned above while launching the Java application. These jars can be put in the directory of WEB-INF/lib under the web application.
-
Deploy raqsoftConfig.xml and the dfx files.
Configure the raqsoftConfig.xml file, which contains the basic configuration information of esProc, such as search path, datasource configuration, etc. The file can be found in the path esProc\config in esProc’s installation directory and has the same information as that set in the esProc Option tab. The configuration is allowed to be adjusted before deployment (like modifying the Search path used to search for the dfx file):
The data source for use in dfx can be configured in the Datasource Manager:
After the modification, put raqsoftConfig.xml in the class path of the application that will use them.
Put the test.dfx created in Step 1 in the class path of the application, or put it in the path specified by node in the configuration file (i.e. the above-mentioned search path).
-
Further configure file raqsoftConfig.xml manually if necessary. For more details, refer to related documents. Note that the configuration file name must not be changed.
-
Call the dfx file in Java program.
public void testDataServer(){
Connection con = null;
java.sql.PreparedStatement st;
java.sql.PreparedStatement st2;
try{
//establish a connection
Class.forName("com.esproc.jdbc.InternalDriver");
con= DriverManager.getConnection("jdbc:esproc:local://");
//call the stored procedure in which test is the name of the dfx file
st =con.prepareCall("call test(?)");
//set the parameters
st.setObject(1,"3");
//the result obtained by executing the following code is the same as that obtained using the above calling method
st =con.prepareCall("call test(3)");
//execute the stored procedure
st.execute();
//get result set
ResultSet set = st.getResultSet();
}
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 learn more about the calling methods and configuration, please refer to Integration & Application.
Refer to the following essays to learn about the methods of calling an SPL script in the other applications:
How to Call an SPL Script in Java
How to Call an SPL Script in Python
How to Call a Remote SPL Script in Java
How to Call an SPL Script in BIRT