Java programs can call esProc via JDBC. It is necessary for programmers to know how to deploy the esProc JDBC and familiarize themselves with the detailed configuration information.
1． Basic steps
The following instructions need to be followed to deploy the esProc JDBC:
1) Load necessary jars
Load the jars needed by esProc when launching a Java application. These jars can be placed under the WEB-INF/lib directory in the Web application environment. There are three basic jars necessary for esProc JDBC, all of them can be found in [instllation]\esProc\lib directory:
dm.jar esProc computing engine and JDBC driver
icu4j_3_4_5.jar Process internationalization
dom4j-1.6.1.jar Parse the configuration files
Besides the basic jars, there are more jars specifically for some certain functions,they can all be found in the \esProc\lib directory:
To use other databases as the data sources in esProc JDBC, their driver jars are required; hsqldb.jar is specifically used for database demo;
To access Microsoft Office files in esProc JDBC, geronimo-stax-api_1.0_spec-1.0.jar, poi*.jar and xmlbeans-2.3.0.jar are required;
To use the graphic function in esProc JDBC, jars for SVG-typed image processing, including batik*.jar, js.jar, pdf-transcoder.jar, xalan-2.6.0.jar, xerces_2_5_0.jar, xml-apis.jar and xml-apis-ext.jar are required;
Note that JDK1.6 or any of the higher versions of esProc JDBC is required.
2) Deploy config.xml file and dfxConfig.xml file
You can find the two configuration files – config.xml and dfxConfig.xml –respectively in [installation directory]\esProc\lib and [installation directory]\esProc\classes. They need to be copied and placed under the classpath of the application project. During the deployment, config.xml is allowed to be renamed esprocJDBCconfig.xml in case there are already config.xml files for other purposes under classpath. esProc JDBC driver will find and use esprocJDBCconfig.xml first in preference to config.xml, which will be used when the former does not exist. Names of these configuration files’ names should be remain what they are specified here and must not be changed. Detailed information about the two files will be discussed in the second section – Configuring config.xml, and the third section – Configuring dfxConfig.xml.
3) Deploy dfx file
Place the necessary dfx file in either the application project’s classpath or the searching path specified by config.xml’s <paths/> node, or the main path specified by <mainPath/>.
4) Invoke esProc JDBC in Java
About the method of invoking a dfx file in Java and of executing the code directly, please refer to esProc Integration & Application: Java Invocation.
2． Configuring config.xml
config.xml file contains configuration information, including license information, esProc’s main path, dfx file’s searching path, and etc. They are as follows:
<?xml version=”1.0″ encoding=” UTF-8″?>
< Config Version=”1″>
<!—esProc register code; leave it blank for a free version –>
< regCode>W2r@7-8cLMJ-GVU33-BhTIB3E</regCode>
<!– esProc charset–>
<charSet>ISO-8859-1</charSet>
<!–
Configure dfx file’s searching path, which is an absolute path. Multiple paths, separated by “semicolon(s)”, are allowed. The dfx file can also be placed in the application project’s classpath, which has the priority over the searching path for loading the file.
–>
<dfxPathList>
< dfxPath>D:\files\dfx;D:\files\txt;D:\files</dfxPath>
</dfxPathList>
<!– Date, time and other data types –>
<dateFormat>MM/dd/yyyy</dateFormat>
<timeFormat>HH:mm:ss</timeFormat>
<dateTimeFormat>MM/dd/yyyy HH:mm:ss</dateTimeFormat>
<!– The first method for datasource configuration: configure connection pool in the application server and specify the datasource name –>
<DBList>
<!– Datasource name must be the same as that in the dfx file –>
<DB name=”demo”>
<property name=”url” value=”jdbc:hsqldb:hsql://127.0.0.1/demo”/>
<property name=”driver” value=”org.hsqldb.jdbcDriver”/>
<property name=”type” value=”HSQL”/>
<property name=”user” value=”sa”/>
<property name=”password” value=””/>
<property name=”batchSize” value=”1000″/>
<!–
Automatically connect or not. If the value is true, use db.query() function or SQL statements starting with $ directly to access the database; if it is false, there is no automatic connection; you should first establish the connection with connect(db) statement before using the function or the statements.
–>
<property name=”autoConnect” value=”true”/>
<property name=”useSchema” value=”false”/>
<property name=”addTilde” value=”false”/>
</DB>
<DB name=”hsql”>
<property name=”url” value=”jdbc:hsqldb:hsql://localhost/demo”/>
<property name=”driver” value=”org.hsqldb.jdbcDriver”/>
<property name=”type” value=”HSQL”/>
<property name=”user” value=”sa”/>
<property name=”password”/>
<property name=”batchSize” value=”0″/>
<property name=”autoConnect” value=”false”/>
<property name=”useSchema” value=”false”/>
<property name=”addTilde” value=”false”/>
</DB>
</DBList >
<!– esProc main path, which is a single absolute path –>
<mainPath> D:\files</mainPath>
<!– The save path for temporary files; it can be an absolute path. If using a relative path, put it under the main path –>
<tempPath>\temp</tempPath>
<bufSize>65536</bufSize>
<localHost>192.168.0.86</localHost>
<localPort>8282</localPort>
<logLevel>DEBUG</logLevel>
<callxParallel>4</callxParallel>
<nodeParallel>4</nodeParallel>
</Config>
The config.xml file is shared by esProc JDBC and the Integrated Development Environment (IDE), so the above information can be configured on the IDE interface.
First, to set the registration code, select Help>Registration from the menu bar on the IDE interface to enter or query the registration code. Leave the registration code blank for a free version esProc. After the registration code is entered, you can click on Scan button to see the license information. Then click Register to complete the registration procedure:
Click Tool>Options on the menu bar to edit main configuration information. The key information related to JDBC, like paths, format of date and time and so on, can be edited on the Environment page:
General page also provides JDBC-related settings, such as log level and number of parallel tasks:
Besides, datasource information of the config.xml file can be configured in the datasource manager. Click Tool>Datasource connection on the menu bar to open the datasource manager window:
You can add, delete or modify the datasource configuration information in the datasource manager. Click Edit to edit the connection parameter of the datasource:
In the datasource manager, you can set the information about the driver used by the datasource, datasource URL, user name, password, batch size, and etc. Note that demo is the system datasource, whose connection URL cannot be changed. In addition, esProc JDBC itself cannot be used as a datasource during the datasource connection configuration.
config.xml file will be loaded automatically in Java application’s classpath for use of esProc JDBC but cannot be renamed at this point. If esProc JDBC’s connecting string uses this format (…?config=…), the .xml file specified by config in the connecting string will be loaded for use in the configuration and the file defined in the default configuration will be ignored.
For example, based on a connecting string like con= DriverManager.getConnection(“jdbc:esproc:local:/?config=myconfig.xml”), the configuration in myconfig.xml will be used.
3． Configuring dfxConfig.xml
The dfxConfig.xml file has the configuration information about concurrency, log property, connection pool, and so on. Take the following file as an example:
<dfxConfig>
<!–
Maximum amount of concurrency. Set the allowed max. number of concurrent tasks; tasks performed in parallel within the same connection are handled as the concurrent ones. The number should be within the licensing limit.
–>
<maxConcurrents>10</maxConcurrents>
<!–
Maximum wait time. If the number of concurrent tasks exceeds the maximum amount of concurrency, the extra tasks will be in wait state. Exceptions will be thrown if the wait time exceeds its specified limit.
–>
< maxWaitTime>5000</maxWaitTime>
<!– Files of log configuration property –>
< log>logger.properties</log>
<!– Configure connection pool in the application server and specify the datasource name –>
<jndi-ds-configs>
<!—jndi is a prefix –>
<jndi-prefix>java:comp/env</jndi-prefix>
<!– Datasource name must be the same as that in the dfx file –>
<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 value is true, use db.query() function directly to access the database; if it is false, there is no automatic connection; you should first establish the connection with connect(db) statement before using the function.
–>
<autoConnect>true</autoConnect>
</jndi-ds-config>
</jndi-ds-configs>
</dfxConfig>
If a datasource connection needs to be established for the dfx file invoked through the esProc JDBC, the related information should be edited in config.xml’s datasource configuration or dfxConfig.xml’s connection pool configuration. A point worth particular note is that, if db.query() function or $(db) sql accesses the database directly, the property of autoConnect of this database should be true. For the usage of esProc JDBC, please refer to esProc Integration & Application: Java Invocation.