6.2.1 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: