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.

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

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

4.2.3 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 Other 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.

4.2.4 esProc 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.

esProc JDBC employs the esProc application by calling and embedding it in. 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.splx 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 the script 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: Set connection parameters for the database data source in the raqsoftConfig.xml file directly:

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

<Config Version="2">

<Runtime>

  <!— encrypted datasource list -->

<DBList encryptLevel="0">

<!-- datasource name, which must be the same as that 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"/>

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

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

</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 encryptLevel="0">

</DBList >

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

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

<defDataSource>demo</defDataSource>

<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 script file can be called by a Java program:

public void testDataServer(){

Connection con = null;

java.sql.CallableStatement 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 splx/spl/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.

4.2.5 esProc ODBC

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.

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. The data source doesn’t soupport JRE1.8 and higher versions. 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 script file specified in the esProc search path and return result; Can use an absolute path to specify the script file.

The script file called by the esProc-ODBC service is the same as that called by esProc JDBC, like the previously mentioned test.splx 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.splx 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.