Working with Database Connections

Creating/Configuring a Database Connection. 1

Pooled connection configured in application server properties 3

This document describes how to create and maintain Database Connections. As the name implies a Database Connection represents the connection between the Ebase Xi server and a database server. A Database Connection is referenced by any other elements within the Ebase Xi system which work with databases: Database Resources, Stored Procedure Resources, Dynamic Lists etc.

Creating/Configuring a Database Connection

A database connection is created by right clicking in the tree on Database Connections and selecting Create Database Connection, or from the menu File --> New --> Database Connection. A Database Connection can also be created using the Database Connection Wizard on the Tools menu.

Database type: select the database type from the list. If your database is not in the list, select Other.

Connection type: three different connection types are supported. Each connection type requires different properties which are then displayed.

· Pooled connection: this is the default. All connection properties are defined within the Ebase Xi system. This is the easiest and quickest way to create a database connection and is supported by the Database Connection Wizard. With this option, database connections are pooled. Environment variables can be included within the property values. Click here for details.

· Pooled connection configured in application server: a resource containing connection properties is specified using the configuration files or tools supplied with the application server (e.g. Tomcat) and this Database Connection just contains a link to this resource. Use this option when there is a requirement to externalize database connection parameters. With this option, database connections are pooled. Click here for details.

· Direct non-pooled connection: this option is not recommended. The Ebase Xi system connects directly to the database without going through a connection pooling layer. These connections are non-transactional and considerably slower than pooled connections. This option should only be used where the database system does not have a JDBC driver that supports connection pools. Click here for details.

Pooled connections: connections to the database remain open and are shared between multiple users. This represents a considerable performance improvement over non-pooled connections as the establishment of a database connection is a relatively expensive operation. The connection pooling software also provides the mechanism that implements transactionality; non-pooled connections are not transactional.

Pooled Connection Properties

These properties are available when connection type Pooled connection is selected. All connection properties are defined within the Ebase Xi system. This is the easiest and quickest way to create a database connection as no configuration of the application server is required. However, the disadvantage is that if the Database Connection is exported and then imported into another system, the properties may need to be manually changed.

Environment variables can be included within the property values as shown in the example below.

The factory property specifies the name of the connection pool factory class, and the remaining properties are the properties supported by that particular factory class. The factory class name must be the appropriate factory for the application server being used. Ebase Xi is distributed with Tomcat, in which case the factory class must be com.ebase.jndi.DataSourceFactory and this is supplied as the default value; changing this to use a different factory class means that transactionality may be lost.

Properties for use with Tomcat and factory com.ebase.jndi.DataSourceFactory:

driverClassName: the class name of the JDBC driver. Values are supplied automatically when Database type is changed.

url: the JDBC URL to connect to the database. This contains information on the database server name, port etc plus any properties supported by the database driver. Model values are supplied automatically when Database type is changed. Click here for more details of URL connection strings for different database systems.

username: the username to create a new connection. If this property is missing, the system will try to connect without username/password.

password: the password for the connection. Passwords are encrypted when stored in the Ebase repository database.

min: the minimum number of open connections maintained in the connection pool. If omitted, the default value is 2.

max: the maximum number of open connections maintained in the connection pool. If omitted, the default value is 20.

checkLevel: this property specifies the level of checking performed before a connection is supplied from the connection pool. The default value is 0. The supported values are:

· 0: no check, the connection is taken from the pool without any verification

· 1: test if the connection is closed or not; if it is closed, another connection is tested, until a valid connection is returned

· 2: the connection is tested by issuing the SQL statement specified with property valiadationQuery

validationQuery: the SQL statement used to test a connection before it is supplied to a requestor when checkLevel 2 is specified. The SQL statement should not be terminated with a semicolon (;).

preparedStatementCache: specifies the size of the prepared statement cache used by the connection pool. Specify 0 to disable prepared statement caching. Note that when using Oracle, a value of 0 should be specified.

For other application servers, consult the application server documentation and specify the appropriate factory class name and properties supported by that factory class.

Pooled connection configured in application server properties

These properties are available when connection type Pooled connection in application server is selected. A resource containing connection properties is configured to the application server (e.g. Tomcat) and the Database Connection contains a link to this resource. Use this option when there is a requirement to externalize database connection parameters.

Datasource id: the JNDI lookup name of the resource as configured to the application server, but without the leading jdbc/. For example, if the JNDI lookup name is configured as jdbc/MYDB, this should be specified as MYDB. For the distributed Tomcat application server, resources are configured in the context definition file (as distributed, this is file ufs.xml in folder UfsServer/tomcat/conf/Catalina/localhost but this may change if the context is renamed or additional contexts are added).

For Tomcat, the properties used to configure a resource are the same as those shown above for Pooled connection properties. For example:

<Resource name="jdbc/EBASE_SAMPLES" auth="Container"

type="javax.sql.DataSource"

factory="com.ebase.jndi.DataSourceFactory"

driverClassName="org.apache.derby.jdbc.EmbeddedDriver"

url="jdbc:derby:ebase_samples;create=true"

username="ebase_samples"

password="ebase_samples"

max="30"

min="5"

validationQuery="select 1 from ac_orders"

checkLevel="1"

preparedStatementCache="16"

For application servers other than Tomcat, please follow the instructions in the application server’s documentation to configure a resource. You may also need to map the JNDI lookup name to the web application context as described in How Ebase accesses databases.

Direct non-pooled connection

These properties are available when connection type Direct non-pooled connection is selected.

!!Caution: these connections are non-transactional and considerably slower than pooled connections.

They should only be used where the database system does not have a JDBC driver that supports connection pools.

JDBC URL: the JDBC URL to connect to the database. This contains information on the database server name, port etc plus any properties supported by the database driver. Click here for more details of URL connection strings for different database systems.

JDBC Driver: the class name of the JDBC driver

Userid: the username for the connection

Password: the password for the connection

Confirm password: ditto

Database Connection Toolbar

Save: saves the Database Connection.

Test Database Connection: tests the connection. This is also available as a button at the bottom of the editor.

Create resource(s) from database schema: runs the database schema wizard to import database resources and stored procedure resources from the database.

Database Connection Wizard: runs the wizard to create a Database Connection with pooled connection properties.

Show information: shows userid and dates for creation, last update and import of this Database Connection.

Shows this help page.

Supplied JDBC Drivers

Ebase Xi is supplied with the following JDBC drivers:

Apache Derby Embedded and Network
JTDS driver used with MS Sql Server and Sybase
Postgres

All other databases require that the appropriate JDBC driver file is installed. With the delivered Tomcat application server, the driver file should be copied to UfsServer/tomcat/lib, then the server should be re-started. The easiest way to install a JDBC driver is to use the Database Connection Wizard, then follow the instructions.