1.9 esProc JDBC

Read(1422) Label: jdbc, java invocation,

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.

Description of esProc JDBC jars

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:

  dm.jar        esProc computing engine and JDBC driver

  icu4j_3_4_5.jar    handle internationalization

  jdom.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.jar is necessary when using the demo database. Please note that the esProc JDBC requires JDK1.6 or higher versions.

Basic uses

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. 

1)  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.

2)  Deploy raqsoftConfig.xml and the dfx files.

Configure the raqsoftConfig.xml file, which contains the basic configuration information of esProc, such as esProc license, 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 <dfxPathList/> node in the configuration file (i.e. the above-mentioned search path).

3)  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.

4)  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.