Database Configuration

esProc supports using a variety of heterogeneous data sources, including the databases, at the same time. This part introduces the methods of connecting to various databases in esProc.

Database access through datasource manager

To connect to a relational database, follow these steps:

1)  Click Datasource connection on Tool options menu to open the datasource manager;

2)  Click New to create a data source and select its type.

3)  Select information like database type and charset, and 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 at one time.

esProc has the built-in system data source – demo, which can be launched through Start -> AllProgram -> esProc -> , or by running startDataBase.bat which is located in esProc\bin in IDE installation directory.

esProc’s Integrated Development Environment (IDE) provides JDBC configuration prompts for various databases. You need to prepare these databases’ JDBC drivers, which are provided by database vendors, and place them in \common\jdbc in esProc’s IDE installation directory.

Retrieving data from databases

After the driver jars are put in place and configured, you can connect esProc to the desired database in IDE conveniently and retrieve data from it. For example:

 

A

1

=demo.query("select * from CITIES")

2

$ select * from CITIES

3

=connect("demo")

4

=A3.query("select * from CITIES")

In the cellset, A1 uses the demo datasource connected in the datasource manager to query data in the database. A2 performs a query through the default database connection. A4 uses the database connection created by A3 to query data. Programmers can also control a database connection with program. Refer to Database Connection Management for further discussion. A1, A2 and A4 return the same results:

Here SQL is used to return directly the result of querying data from a database. Here the SQL statement directly returns the result of querying the database table. Refer to Using SQL for details.

Configuring other databases

esProc allows connecting to a database through JDBC-ODBC bridge. The ODBC driver needs to be prepared manually. The esProc IDE’s ODBC configuration interface is as follows:

Select an ODBC data source for configuration from the list and set related parameters including user name and password.

If the to-be-connected JDBC database isn’t on the list, choose Unknown type for connection. The configuration interface is as follows:

Put the required JDBC driver in \common\jdbc in esProc’s IDE installation directory and enter the driver name, database URL, and other information on the configuration page.

esProc JDBC

esProc JDBC is thus named because esProc application is packed as a built-in feature. In other web applications, a cellset program can be called via a JDBC-like connection. With esProc JDBC, a cellset program packaged as a stored procedure is called in the same way as a real stored procedure.

An esProc cellset file returns a result set via return function, like the test.dfx file:

 

A

1

=connect("demo")

2

=A1.query("select * from EMPLOYEE where EID=?",arg1)

3

>A1.close()

4

return A2

arg1 is a cellset parameter, which should get assigned when dfx file is called. The result is a table sequence returned via a return statement. Note that some files called by JDBC may need to connect to another data source, like "demo" in this example, which also requires being configured through parameter settings. But the data source configuration in esProc JDBC needs to be handled in the configuration file raqsoftConfig.xml.

1)  Method one for configuring data sources: Directly set connection parameters for the database datasource in the raqsoftConfig.xml file:

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

<Config Version="2">

<Runtime>

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

<property name="dbCharset" value="GBK"/>

<property name="clientCharset" value="GBK"/>

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

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

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

</DB>

</DBList >

</Runtime>

</Config>

2)  Method two for configuring data sources: Configure Application Server connection pool in the raqsoftConfig.xml file.

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

<Config Version="2">

<Runtime>

<DBList>

</DBList >

</Runtime>

<Server>

<!—Connect a default source, which is one in a DBList or in the following JNDIList -->

<defDataSource>demo</defDataSource>

<JNDIList>

<!-- data source name, which must be the same as that specified in the dfx file -->

< JNDI 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, just use the db.query() function to access the database. If it is false, the connection won’t be established automatically; instead, connect(db) statement needs to be used to establish the connection first.-->

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

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

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

<property name="dbCharset" value="GBK"/>

<property name="clientCharset" value="GBK"/>

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

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

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

</JNDI>

</JNDIList >

</Server>

</Config>

 

During configuration, the name of the configuration file must be raqsoftConfig.xml and is not allowed to be changed. When configuring database connection information, reentering is forbidden and esProc JDBC itself cannot be used as a data source and configured.

This section only introduces the methods of data source configuration in esProc JDBC. For the use and configuration of esProc JDBC, please refer to Deploying JDBC.

Once the data source has been configured, the above-mentioned dfx file can be called by a Java program:

public void testDataServer(){

Connection con = null;

java.sql.PreparedStatement st;

java.sql.Statement st2;

try{

//establish 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 the dfx file

st =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 =con.prepareCall("call test(3)");

// execute the stored procedure

st.execute();

// get the result set

ResultSet set = st.getResultSet();

 

// create a Statement directly

st2=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 in a specified data source

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://"; 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 raqsoftConfig.xml. For example, con=DriverManager.getConnection("jdbc:esproc:local:/?config=myconfig.xml"); should use the configuration in myconfig.xml file.

esProc ODBC

esProc-ODBC data sources and other ODBC data sources are similar in their uses. esProc ODBC driver should be in place and the ODBC service needs to be started before you can access an esProc-ODBC data source. Refer to Deploying ODBC fot detailed explanation. In this section, we introduce how to configure and use esProc-ODBC data sources.

Create an ODBC data source in the Datasource window:

In the ODBC datasource window, select an esProc-ODBC data source name:

Leave the Username and Password blank as they are already set when configuring the esProc-ODBC data source. Then esProc-ODBC data source connection starts to work:

The prompt in the above window shows that esProc-ODBC is connecting to the esProc ODBC server. The esProc ODBC service provides two functionalities:

1. Query data directly from a file specified by the main path using a simple SQL statement.

2. Call the dfx file specified in the esProc search path and return result; Can use an absolute path to specify the dfx file.

The dfx file called by the esProc-ODBC service is the same as that called by esProc JDBC, like the previously mentioned test.dfx file:

 

A

1

=connect("demo")

2

=A1.query("select * from EMPLOYEE where EID=?",arg1)

3

>A1.close()

4

return A2

The following shows how to perform a query using esProc-ODBC:

 

A

1

=connect("esProcOdbc")

2

=A1.query("call test(?)",20)

3

=A1.cursor("select * from Order Electronics.txt ")

4

=A3.fetch@x(100)

5

>A1.close()

A2 calls the test.dfx file using the call statement to perform a query. Below is the result:

A3 uses a select statement to query data directly from the external data file. To do this, put the data file in the esProc main path.The external data file name can be directly used as the table name. Below is the query result A4 gets:

esProc ODBC supports quering external data with simple SQL. For more details, refer to Simple SQL.