esProc can connect to database's jdbc driver, or to a database through jdbc-odbc bridge. Because of the problem of copyright, programmers who use esProc need to prepare jdbc or odbc driver of a database by themselves. When jdbc jar package driver is prepared, it need to be put into /common/jdbc of esProc's IDE installation directory, e.g., the directory C:\Program Files (x86)\MicroInsight\common\jdbc.
ODBC interface configuration of esProc's integrated development environment is as follows:
esProc's integrated development environment provides jdbc configure prompts of multiple databases, including SQL server, Oracle, DB2, Sybase, Access, mysql, hsql, teradata, postgres, etc. If the database waiting to be connected doesn't fall into the list, it can be added with other type. The interface configuration is as follows:
After the jar package driver is prepared and configuration is finished, the database will be conveniently connected within IDE and table data will be fetched:
In the above figure, cell A1 is connected to a hsql database named demo. Cell A2 uses SQL statement to query the table of employee information, and stores the table in this cell, which is a variable, as esProc's table sequence; arg1 is an input parameter. Cell A3 closes database connection. Cell A4 returns the query result outwards. The red box in the esProc IDE's bottom right corner displays demo's table name and field name, which is convenient for programmers to write SQL statement.
As the other functions provided by esProc, query function contains options and parameters. Take the expression query@1("select * from employee") as an example, @1 represents that 1 option is used, and by looking up the function reference, only the first record fetched by SQL statement will be returned. There are parameters in the parentheses. In the above figure, there is only a string of SQL statement and no other parameters in the parentheses, which shows that all other parameters have been set by default.
The cellset in the above figure can be integrated in Java application, and, acting as an esProc jdbc driver, it could be called by Java program . Steps include:
1. Prepare dfx file.
Save esProc program as test.dfx.
2. Deploy esProc jars .
Putnecessary jars for calling esProc program in classpath of Java application. They could be put in WEB-INF/lib directory for a web application. These jars are located in the esProc IDE’s installation directory \esProc\lib, which includes:
dm.jar esProc computing engine and JDBC driver
poi-3.7-20101029.jar process reading and writing of Excel
log4j_128.jar process logs
icu4j_3_4_5.jar process internationalization
dom4j-1.6.1.jar analyze configuration
3. Deploy database drvier jar .
Put database jdbc drivers needed for esProc to connect to the database in Java application's class path. For instance, hsql.jar of demo database.
4. Configure dfxConfig.xml and config.xml files
Config.xml file contains basic configuration information for esProc, such as registration code, address search path, master directory, configuration of data sources. They can be found in directory esProc\config in esProc's installation path, in which the information is the same as that set in the esProc's option page. dfxConfig.xml can be found in directory esProc\classes in installation path. In this article, we’ll illustrate some of the configuration for esProc to connect to a database. For the other, please see A Course of esProc.
1) Configuration method one: Directly configure connection parameters of database data source.
Config.xml file：
<DBList>
<!-- name of data source must be in consistent with that in 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 setting is true, db.query() function can be directly used to access a database; if it is false, the database cannot be automatically connected and connect(db) statement must be used for connection before db.query() function does its job.
-->
<property name="autoConnect" value="true"/>
<property name="useSchema" value="false"/>
<property name="addTilde" value="false"/>
</DB>
</DBList>
2）Configuration method two: Configure connection pool and jndi in Java application, and designate name of jndi in dfxConfig.xml file.
dfxConfig.xml file：
<jndi-ds-configs>
<!--jndi prefix -->
<jndi-prefix>java:comp/env</jndi-prefix>
<!-- name of data source must be in consistent with that in dfx file -->
<jndi-ds-config>
<name>demo</name>
<dbType>HSQL</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 a database; if it is false, the database cannot be automatically connected and connect(db) statement must be used for connection before db.query() function does its job.
-->
<autoConnect>true</autoConnect>
</jndi-ds-config>
</jndi-ds-configs>
Please note that:
Name of configuration files must be config.xml and dfxConfig.xml and cannot be changed.
Reentering is forbidden and esProc JDBC itself cannot be used as a data source and configured as a database connection.
If the two configurations have the data sources of the same name, the data source in config.xml will prevail.
5. Deploy dfxConfig.xml, config.xml and test.dfx files.
Put dfxConfig.xml and config.xml files in classpath of Java application, or directly package them into dm.jar.
Put test.dfx file in classpath of Java application, or put it in the absolute path designated by <paths/> node of dfxConfig.xml file.
6. Call test.dfx in java program.
If ...?config=... is used in connecting string of esProc JDBC, configuration of .xml will be used and that of config.xml will be ignored. Default setting will be enabled if there is no config parameter in connecting string.
For instance, configuration of myconfig.xml is used in the expression con= DriverManager.getConnection("jdbc:esproc:local://?config=myconfig.xml") .
Code sample is as follows:
publicvoid testDataServer(){
Connection con = null;
com.esproc.jdbc.InternalCStatementst;
com.esproc.jdbc.InternalCStatement st2;
try{
// establish connection
Class.forName("com.esproc.jdbc.InternalDriver");
con= DriverManager.getConnection("jdbc:esproc:local://");
//call the stored procedure, in which test is the file name of dfx
st =(com.esproc.jdbc.InternalCStatement)con.prepareCall("call test(?)");
//set parameters
st.setObject(1,"3");
//the following statement has the same effect as that of the previous call
st =(com.esproc.jdbc.InternalCStatement)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 connection
if (con!=null) {
try {
con.close();
}
catch(Exception e) {
System.out.println(e);
}
}
}
}
没有评论:
发表评论