Deploying JDBC

You can choose to skip this section if you are not a professional programmer. This won’t affect your learning about the other contents of this Tutorial

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.

6.4.1 Basic steps

Follow instructions below to deploy esProc JDBC:

1)  Load necessary jars

Load the jars needed by esProc at the launch of a Java application. Put the following jars under WEB-INF/lib directory in the Web application environment. esProc JDBC requires two basic jars, which can be found in [instllation directory]\esProc\lib:

esproc-bin-xxxx.jar        esProc computing engine and JDBC driver

icu4j_60.3.jar    Handle internationalization

There are jars specifically for certain functionalities under \esProc\lib directory:

To use databases as data sources, their driver jars are required; hsqldb-2.7.3-jdk8.jar is specifically used for demo database;

To read and write Microsoft Office files requires poi*.jar and xmlbeans-5.2.0.jar;

To use the graphic functionalities requires jars for SVG-typed image processing, which include batik-all-1.16.jar, xml-apis-1.4.01.jar, xmlgraphics-commons-2.8.jar, and xml-apis-ext-1.3.04.jar;

Note that esProc JDBC requires JDK1.8 or a higher version.

2)  Deploy raqsoftConfig.xml file

The file can be found in [installation directory]\esProc\lib. It needs to be copied and placed under the class path of the application project. The file’s name should remain what it is specified and must not be changed. Detailed information about the file is discussed in Configuring raqsoftConfig.xml.

3)  Deploy the script file

Place the necessary script file in either the application project’s classpath or the search path specified by raqsoftConfig.xml’s <splPathList/> node, or the main path specified by <mainPath/>.

4)  Invoke esProc JDBC in Java

About the methods of invoking a script file in Java and of executing the code directly, please refer to Java Invocation.

6.4.2 Configuring raqsoftConfig.xm l

The raqsoftConfig.xml file contains configuration information, including esProc’s main path, the script file’s search path, and etc., as shown below:

<?xml version="1.0" encoding="UTF-8"?>

< Config Version="2">

<Runtime>

<!-- Method one for data source configuration: configure connection pool and specify the data source name in the application server -->

<!—encryptLevel is the level of user password encryption in the data source: 0 represents plaintext, 1 represents password encryption, and 2 represents encryption of URL, username and password -->

<DBList encryptLevel="0">

<!-- Data source name must be the same as that defined in the script file -->

<DB name="demo">

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

<property name="driver" value="org.hsqldb.jdbcDriver"/>

<!—type represents serial number of the database type: UNKNOWN:0; ORACLE:1; SQLSVR:2; SYBASE:3; SQLANY:4; INFMIX:5; FOXPRO:6; ACCESS:7; FOXBAS:8; DB2:9; MYSQL:10; KINGBASE:11; DERBY:12; HSQL:13; TERADATA:14; POSTGRES:15; DATALOGIC:16; IMPALA:17; DBOne：20; ESSBASE:101;-->

<property name="type" value="13"/>

<property name="user" value="sa"/>

<property name="password" value=""/>

<!-- Set the batch size -->

<property name="batchSize" value="1000"/>

<!--

Automatically connect or not. If the value is true, use db.query() function or a SQL statement starting with $ directly to access the database; if it is false, there will be no automatic connection; you should first establish the connection with connect(db) statement before you can access the database.

-->

<property name="autoConnect" value="true"/>

<!—Set whether to use schema name. For instance, when “UseSchema” value is set as true, the syntax in the automatically generated SQL statement is select tbl.col1,tbl.col2 from tb1; otherwise the syntax is select col1,col2 from tbl -->

<property name="useSchema" value="false"/>

<!-- Set whether to enclose object in quotes -->

<property name="addTilde" value="false"/>

<!-- Set whether to transcode the content retrieved from the database; when setting the value as true, the retrieved data will be transcoded for display when the database encoding is different from encoding at the frontend-->

<property name="needTransContent" value="false" />

<!-- Set whether to transcode the SQL statement because you may need to convert the type of encoded characters at the frontend into the database encoding type for SQL execution from frontend to the database -->

<property name="needTransSentence" value="false" />

<!-- Set whether to set case-sensitive for the SQL statement-->

<property name="caseSensitive" value="false" />

</DB>

<DB name="hsql">

<property name="url" value="jdbc:hsqldb:hsql://localhost/hsql"/>

<property name="driver" value="org.hsqldb.jdbcDriver"/>

<property name="type" value="13"/>

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

<!-- esProc charset-->

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

<!--

Configure the search path of the script file. Multiple paths separated by ";" are allowed. The script file can also be placed in the application project’s classpath, which has the priority over the search path for file loading.

-->

<splPathList>

< splPath>D:\files\spl;D:\files\txt;D:\files</splPath>

</splPathList>

<!—Date type, time type and other data types -->

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

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

<dateTimeFormat> yyyy-MM-dd HH:mm:ss</dateTimeFormat>

<!-- Method one for data source configuration: configure connection pool and specify the data source name in the application server -->

<!-- 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; don’t start a relative path with “/” or “\” -->

<tempPath>temp</tempPath>

<!-- Set file buffer size, whose unit is byte -->

<bufSize>65536</bufSize>

<!-- Set the maximum number of parallel tasks -->

 

<parallelNum>4</parallelNum>

 

<!-- Set the composite file block size -->

<blockSize>1048576</blockSize>

<!-- Set default number of subcursors in a multicursor -->

<cursorParallelNum>1</cursorParallelNum>

<!-- A null string in text data -->

<nullStrings>nan,null,n/a</nullStrings>

<!-- Set the number of records fetched from the cursor -->

<fetchCount>9999</fetchCount>

<!-- Configure external library directory-->

<extLibsPath/>

</Esproc>

<Logger>

<!-- Log level, which can be OFF,SEVERE,WARNING,INFO,DEBUG; their priorities decrease in the order. Do not output any log information when log level is OFF; output WARNING, INFO, SERVERE when log level is INFO; and so on-->

<Level>DEBUG</Level>

</Logger>

</Runtime>

<Server>

<!—Default connection; the connected source can be one in DBList or in the following JNDIList -->

<defDataSource>demo</defDataSource>

<!—Set JNDI data source –>

<JNDIList>

<JNDI name="jdbc/test">

//Database type number; 0-14 represents database types in order: [UNKNOWN:0; ORACLE:1; SQLSVR:2; SYBASE:3; SQLANY:4; INFMIX:5; FOXPRO:6; ACCESS:7; FOXBAS:8; DB2:9; MYSQL:10; KINGBASE:11; DERBY:12; HSQL:13; TERADATA:14;]

<property name="type" value="1"/>

<property name="batchSize" value="1000"/>

// Reference database’s JNDI data source, the format is: java:comp/env/DataSource name. Find DataSource through JNDI name; the environment string prefixes used for searching the data source are different in different web servers; for instance, java:/comp/env for Tomcat, and both Weblogic and Websphere do not need one and the string can be left unentered.

Server	Tomcat	Weblogic	Webshpere	Resin	Jboss
JNDIPrefix	java:comp/env	None	None	java:comp/env	Java:

<property name="lookup" value="java:comp/env/jdbc/test"/>

 

</JNDI>

</JNDIList >

<!—The log record configuration properties file. See raqsoftLog.properties for details -->

 

<property name="logConfig" value="raqsoftLog.properties"/>

</Server>

<JDBC>

<!—Whether to read the runtime configurations or Server’s JNDIList; delete the unwanted one -->

<load>Runtime,Server</load>

<!—After the jdbc gateway is configured, all statements will be parsed by the script file and return a table sequence or a cursor; the script file has two parameters: sql and args (which is the sequence of sql’s parameters) -->

<gateway>executeSQL.splx</gateway>

<!—Remote server list for a JDBC invoke for configuring IPs and port numbers of nodes -->

<Units>

<Node>192.168.0.197:8281</Unit>

</Units>

</JDBC>

<Init>

<!— The specified script file executed first for system initialization -->

<SPL>initial.spl</SPL>

</Init >

</Config>

Both esProc JDBC and the Integrated Development Environment (IDE) share the same raqsoftConfig.xml file, so you can configure the above information on the IDE interface.

Click Tool>Options on the menu bar to edit the configuration information on the Option window. 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, the raqsoftConfig.xml file’s data source information can be configured in the data source manager. Click Tool>Datasource connection on the menu bar to open the data source manager window:

You can add, delete or modify the data source configuration information in the data source manager. Click Edit to edit the connection parameters of the data source:

In the data source manager, you can set the data source driver, data source URL, user name, password, batch size, and other information. Note that demo is the system data source, whose connection parameters cannot be changed. In addition, esProc JDBC itself cannot be used as a data source during the data source connection configuration.

The <Server/> node configures connection pool, log configuration file, and other information. If a data source needs to be connected for the script file invoked through the esProc JDBC, the related information should be configured in raqsoftConfig.xml’s data source or connection pool. Particularly, if db.query() function or $(db) sql statement is used to directly access the database, the autoConnect property of this database should be true. For detailed information about esProc JDBC, refer to Java Invocation.

In the log configuration properties file raqsoftLog.properties , you can configure log levels and other information:

// Log levels include OFF, ERROR, WARN, INFO, DEBUG and ALL, whose priority levels decrease from left to right. Level OFF turns off logging.

// Log messages. Level INFO indicates that messages of levels ERROR, WARN and INFO will be exported; each level exports messages likewise.

// Specify name and log level for Logger.

// Format: Level (can be omitted; default level is INFO), log name 1, log name 2.

Logger=LOG1

 

// Output log messages to the console. There are only two forms of message logging: console and file.

LOG1=Console

// INFO Log message level; messages whose priority levels are lower than it will be ignored. If it is omitted, level INFO is used by default.

LOG1.Level=DEBUG

 

// Output logs to the specified file.

// Specify the full path of LOG2; default full path is the application’s current working path.

LOG2=C:/raqsoft.log

 

// Default mode of logging is appending if not specified.

LOG2.Append=true

 

// Maximum number of bytes in a log file; the default value is infinite if not specified.

LOG2.MaxFileSize=10MB

 

// Maximum number of backup files; the default value is 1 if not specified.

LOG2.MaxBackupIndex=2

//LOG2.Level=DEBUG

 

// Whether or not make the log name fixed; without making it fixed, a separate log file will be generated for each date according to the specified log file name

LOG2.isFixedFileName=false

The raqsoftConfig.xml file in Java application’s classpath will be automatically loaded in the use of esProc JDBC, and cannot be renamed at this point. But if esProc JDBC’s connecting string uses the format like ...?config=..., the .xml file specified by config in the connecting string will be loaded for use in the configuration and the default file defined in the configuration will be ignored.

For example, with a connecting string like con= DriverManager.getConnection("jdbc:esproc:local:/?config=myconfig.xml"), the configuration in myconfig.xml will be used.