esProc JDBC’s Integration and Application Method

esProc JDBC is like an incomplete database JDBC driver without physical tables. It regards esProc as a database only having stored procedures (strong computing power and weak storage mechanism). Similar to the use of database JDBC, esProc JDBC calls esProc program as it calls stored procedures. Their difference is that esProc JDBC is a completely embedded computing engine. All computations are completed in the embedded package rather than by an independent server like in the databases.

1. Loading driver

Jars need by esProc JDBC include dm.jar, poi-3.7-20101029.jar, log4j_128.jar, icu4j_3_4_5.jar and dom4j-1.6.1.jar. The five jarscan be obtained in\esProc\lib in esProc’s IDE installation directory. Load these jars when starting java application; they can be put in WEB-INF/lib directory for a web application.
Note that esProc JDBC requires JDK1.6 or higher versions.

2 . Modifying configuration files config.xml and dfxConfig.xml

Prepare file config.xml which contains esProc’s basic configuration information, such as registration code, addressing path, main pathand data source configuration. The file can be found in the directory esProc\config in esProc's installation directory, and its configuration can be modified before deployment (for detailed explanation of configuration information, please see Appendix).

Configuring authorized information

Configure as follows in file config.xml:

<regCode> license</regCode>

license represents authorization code. Now esProc provides users with free distributions for integration. Free authorization codes are available in the official website.

Save config.xml and dfxConfig.xml in classpath of application projects.

Here we should note that names of the configuration files must beconfig.xml and dfxConfig.ximl, and cannot be changed;reentering is forbidden and esProc JDBC itself cannot be used as a data source and configured as a database connectionduring configuring database connection information.

3.  Deploying esProc program

Put the pre-edited esProc script (dfx file) in classpath of the application project, or put it in the path designated by <paths/> node of dfxConfig.xml file.

4.  Java's calling of esProc program

We'll look at situations when esProc program returns a single result set and when it returns multiple result sets.

4.1 =Single result set

esProc script

Java's calling

public class CallSingleRS {

    publicvoidtestDataServer(){

        Connection con = null;

    com.esproc.jdbc.InternalCStatementst;

    try{

    //create a connection

    Class.forName("com.esproc.jdbc.InternalDriver");

    con= (Connection) DriverManager.getConnection("jdbc:esproc:local://");

    //call the stored procedure,dfxfile’s name is singleRs

    st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call singleRs(?)");

    //set parameters

    st.setObject(1,"5");

    //execute the stored procedure

    st.execute();

    //get result set

    ResultSetrs = (ResultSet) 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);

            }

          }

        }

    }

}

4.2  Multiple result sets

esProc script

Java's calling

Here the main code will be provided and the rest is the same as that in the previous example.

//call the stored procedure

st =( com. esproc.jdbc.InternalCStatement)con.prepareCall("call MutipleRs()");

//execute the stored procedure

boolean hasResult = st.execute();

//if the execution returns result sets

if (hasResult) {

    //get multiple result sets

ResultSet set = st.getResultSet();

intcsize = set.getMetaData().getColumnCount();

    // the multiple result sets are data rows of one column, csize is 1

while (set.next()) {

      Object o = set.getObject(1);

      //in this example, one table sequence can be read out each time and retrieve table sequences respectively in A2 and A3

    }

} 

【Appendix】 Explanation of Configuration Information

config.xml

<?xmlversion="1.0"encoding="UTF-8"?>

<ConfigVersion="1">

    <regCode>W2r@7-8cLMJ-GVU33-BhTIB3E</regCode>

    <charSet>ISO-8859-1</charSet>

    <!--Configure addressing path of dfx file, which is an absolute path. We can set multiple paths and separate them from each other by semicolons. The path of dfx file can also be put in classpath of application projects, and the classpath takes precedence over addressing path in loading files -->   

<dfxPathList>

        <dfxPath>D:\files\dfx</dfxPath>

    </dfxPathList>

    <dateFormat>yyyy-MM-dd</dateFormat>

    <timeFormat>HH:mm:ss</timeFormat>

    <dateTimeFormat>yyyy-MM-ddHH:mm:ss</dateTimeFormat>

    <!--Configuration method one: configure connection pool in the application server and designate data source name here

--> <DBList>

        <!--The data source name must in consistent with that in dfx file -->

        <DBname="demo">

            <propertyname="url"value="jdbc:hsqldb:hsql://127.0.0.1/demo"/>

            <propertyname="driver"value="org.hsqldb.jdbcDriver"/>

            <propertyname="type"value="HSQL"/>

            <propertyname="user"value="sa"/>

            <propertyname="password"value=""/>

            <propertyname="batchSize"value="1000"/>

            <!--Automatically connect or not. If the setting is true, db.query() function can be directly used to access the database; if it is false,  the connection won’t be automatic and connect(db) statement must be used first for the connection-->

            <propertyname="autoConnect"value="true"/>

            <propertyname="useSchema"value="false"/>

            <propertyname="addTilde"value="false"/>

        </DB>

    </DBList>

    <mainPath>D:\tools\raqsoft\main</mainPath>

    <tempPath>D:\tools\raqsoft\main\temp</tempPath>

    <bufSize>65536</bufSize>

    <localHost>192.168.0.86</localHost>

    <localPort>8282</localPort>

</Config>

dfxConfig.xml

<?xmlversion="1.0"encoding=" UTF-8"?>

<dfxConfig>

    <!--Maximum concurrent jobs.Set the maximum jobs allowed to concur; jobsoperating simultaneously in the same connection should also be dealt with as concurrent jobs. The maximum concurrent jobs should be within the authorized limit.-->

    <maxConcurrents>10</maxConcurrents>

    <!--The longest wait time.If tasks in operation are more thanthe maximum concurrent, the extra tasks will be in a waiting state; if the wait time reaches its limit, abnormitymessage will be shown.-->

    <maxWaitTime>5000

</maxWaitTime>

    <!--Logs configure property files-->

    <log>logger.properties

</log>

    <!--Configure connection pool in the application server and designate data source name here -->

    <jndi-ds-configs>

        <!—jndi-prefix-->

        <jndi-prefix>java:comp/env</jndi-prefix>

        <!--Data source name must be in consistent with that in dfx files -->

        <jndi-ds-config>

            <name>olap</name>

            <dbType>ACCESS</dbType>

            <dbCharset>ISO-8859-1</dbCharset>

            <clientCharset>ISO-8859-1</clientCharset>

            <needTranContent>false</needTranContent>

            <needTranSentence>false</needTranSentence>

            <!--Automatically connect or not. If the setting is true, db.query() function can be directly used to access the database; if it is false,  the connection won’t be automatic and connect(db) statement must be used first for the connection-->

            <autoConnect>true</autoConnect>

        </jndi-ds-config>

    </jndi-ds-configs>

</dfxConfig>