esProc supports a variety of heterogeneous datasources, including the databases. This article will introduce the methods of esProc’s connection to various databases.
1. Configure datasource manager
To connect to a relational database, please follow these steps:
1) Click Datasource connection on Tool options menu to open the datasource manager.
2) Click New to create a datasource and select its type.
3) Select information like database type, charset, and etc. Set connection parameters for the datasource and name them.
4) Connect to the database. The datasource manager will display the result of connection and supports connecting to multiple databases.
esProc has its own system datasource – demo, which is located in esProc\bin in IDE installation directory. Find it and run start DataBase.bat to launch the datasource demo.
esProc’s Integration Development Environment (IDE) provides JDBC configure prompts for many types of databases, including SQL server, Oracle, DB2, Sybase, Access, MySQL, HSQL, Teradata, PostgreSQL and etc. esProc programmers need to prepare these databases’ JDBC drivers, which are provided by database service providers, by themselves and put them into \common\jdbc in esProc’s IDE installation directory.
2. Retrieve data from databases
After the drivers are put in place and configured, programmers can connect esProc to the databases in IDE conveniently and select desired data from them. For example:
| A | |
| 1 | =demo.query(“select * from CITIES”) |
| 2 | $ select * from CITIES |
| 3 | =connect(“demo”) |
| 4 | =A3.query(“select * from CITIES”) |
| 5 | =interval@ms(A6,now()) |
In the cellset, A1 uses the connected datasource – “demo” – in the datasource manager to query data. A2 uses the database connected by default to query data. A4 uses the database connection created by A3 to query data. Programmers can also control the created database connection with program and for further discussion, please refer to esPorc Accesses Databases: Data Connection Management. A1, A2 and A4 return the same results:
Here SQL is used to return directly the result of querying data from a database. Please refer to esProc Accesses Databases: Using SQL for details.
3. Configure other databases
esProc can also connect to a database through JDBC-ODBC bridge. The ODBC driver also needs to be prepared by programmers. The esProc IDE’s ODBC configuration interface is as follows:
Select the ODBC datasource for configuration from the list and set related parameters including user name and password.
If the database to which one wants to connect is outside of the list of databases provided by esProc, one can choose Other type for connection. The configuration interface is as follows:
In the same way, put the JDBC driver used here into \common\jdbc in esProc’s IDE installation directory and enter the name of the driver, database URL, and other information into the configuration page.
4. esProc JDBC
esProc JDBC takes esProc as a built-in application. In other web applications, programmers can call a cellset program using a JDBC-like connection. With esProc JDBC, a cellset program is packaged as a stored procedure, so the method of calling a cellset program is the same as that of calling a stored procedure.
The cellset code for an esProc file will return the result set using return function, like the test.dfx file shown below:
| A | |
| 1 | =connect(“demo”) |
| 2 | =A1.query(“select * from EMPLOYEE where EID=?”,arg1) |
| 3 | >A1.close() |
| 4 | return A2 |
In the above code, arg1 is a cellset argument, which should get assigned when dfx file is called. The computed result is a table sequence which is returned using a return or result statement. Please note that some of the files called by JDBC may need to connect to other datasources, like “demo” in this example. Programmers also need to set parameters for these datasources, and they should handle the datasource configuration in esProc JDBC in configuration file config.xml or dfxConfig.xml.
1) Method One for configuring datasources: Set connection parameters for the database datasource directly in config.xml:
<?xml version=”1.0″ encoding=” UTF-8″?>
< Config Version=”1″>
<DBList>
<!– datasource name, which 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, db.query() function can be used directly to access the database; if it is false, the connection won’t be established automatically and thus connect(db) statement must be used to establish the connection first.
–>
<property name=”autoConnect” value=”true”/>
<property name=”useSchema” value=”false”/>
<property name=”addTilde” value=”false”/>
</DB>
</DBList >
</dfxConfig>
2) Method Two for configuring datasources: Configure Application Server connection pool in dfxConfig.xml.
<?xml version=”1.0″ encoding=” UTF-8″?>
<dfxConfig>
<!– specify the datasource name here –>
<jndi-ds-configs>
<!—prefix jndi –>
<jndi-prefix>java:comp/env</jndi-prefix>
<!– datasource name, which must be the same as that in the 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 value is true, db.query() function can be used directly to access the database; if it is false, the connection won’t be established automatically and thus connect(db) statement must be used to establish the connection first.
–>
<autoConnect>true</autoConnect>
</jndi-ds-config>
</jndi-ds-configs>
</dfxConfig>
During configuration, the names of configuration files must be config.xml and dfxConfig.xml and are not allowed to be changed. While configuring information for connecting to a database, reentering is forbidden and esProc JDBC itself cannot be used as a data source and configured in a database connection.
This article only introduces the methods for configuring datasources in esProc JDBC. For using and configuring esProc JDBC, please refer to Integration and Use of esProc JDBC.
Once the datasource has been configured, the above-mentioned dfx can be called by a Java program:
public void testDataServer(){
Connection con = null;
com.esproc.jdbc.InternalCStatement st;
com.esproc.jdbc.InternalCStatement st2;
try{
//create a connection
Class.forName(“com.esproc.jdbc.InternalDriver”);
con= DriverManager.getConnection(“jdbc:esproc:local://”);
// call the stored procedure, in which test is the name of file dfx
st =(com. esproc.jdbc.InternalCStatement)con.prepareCall(“call test(?)”);
// set parameters
st.setObject(1,”3″);
// the result of the following statement is the same as that got by using the above calling methods
st =(com. esproc.jdbc.InternalCStatement)con.prepareCall(“call test(3)”);
// execute the stored procedure
st.execute();
// get the result set
ResultSet set = st.getResultSet();
// create a Statement directly
st2=(com. esproc.jdbc.InternalCStatement)con.createStatement();
// execute the cellset file directly and get the same result as that of the call test(3) in the above
set=st2.executeQuery(“test 3”);
// execute the statement immediately and query data from a specified datasource
set=st2.executeQuery(“$(demo)select * from STUDENTS”);
}
catch(Exception e){
System.out.println(e);
}
finally{
// close the database connection
if (con!=null) {
try {
con.close();
}
catch(Exception e) {
System.out.println(e);
}
}
}
}
In the above code, the connecting string of esProc JDBC is “jdbc:esproc:local://” and in this case, default configuration will be used; if …?config=…; is used in the connecting string, use the configuration of a specified .xml file and ignore the definitions in the config.xml.
For example, con= DriverManager.getConnection(“jdbc:esproc:local:/?config=myconfig.xml”); should use the configuration of myconfig.xml.