Server Administration Application – Configuring Database Connections

Pooled connection configured in application server properties 5

Introduction

Database Connections are created and maintained on the server using the Server Administration web application. They are saved in folder databases in the ebaseConf folder of the web application in the userdata file system e.g. userdata/apps/<webappname>/ebaseConf/databases. These files can be copied between servers if required.

A Database Connection provides the basic configuration details to connect to a database system e.g. hostname, port, userid, password etc. A Database Connection is required to use any of the entities that access a database: Database Resources, Stored Procedure Resources, Dynamic Lists. A Database Connection is also required when accessing a database via Javascript code using system.getDatabaseConnection(connectionName).

Click the icon to create a new pooled Database Connection using the wizard
Click the icon to create a new Database Connection manually (supports all connection types)
Click the icon to refresh the display
Click the icon to view the Data Source Information
Click the icon to delete a Database Connection
Click on a Database Connection name to display or edit its properties
Click Help on the menu at the top of the panel to display this help page
Move the mouse over the connection type icon to display the connection type (see next section) as a mouse-over text

Database Connection Types

There are three supported connection types:

· Pooled connection: this is the default and is only available when using Apache Tomcat. All connection properties are defined within the Verj.io system. This is the easiest and quickest way to create a Database Connection and is supported by the Database Connection Wizard. The term pooled connection means that 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. When a Pooled Database Connection is deployed to another server, the user is prompted to provide the connection properties on the target server. Environment variables can be included within the property values; these provide a way of externalizing the properties that vary between servers.

· Pooled connection configured in application server: with this option the connection properties are specified in a resource to the application server (e.g. Tomcat) and this Database Connection just contains a link to this resource. These connections are also pooled as described for the previous option.

· Direct non-pooled connection: this option is not recommended. The 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.

JDBC Drivers

All Database Connections require a JDBC driver and this must be added to the server system before the connection can be used. For the supplied Tomcat system, the driver file (usually a jar or zip file) should be added to userdata/tomcat/lib. A restart of the server is required. The Database Connection Wizard checks for the existence of the JDBC driver and will prompt you if it’s missing.

Verj.io is supplied with the following JDBC drivers:

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

Database Connection Properties

Database Connection properties can be edited when either creating a new Database Connection using the icon or clicking on the name of an existing Database Connection. The first panel, shown below, is always present:

Id	Database Connection name
Description	Description
Connection type	As described above
Database type	Select from the dropdown list, select Other if your database is not in the list

The remaining properties vary depending on the connection type.

Pooled Connection Properties

These properties are available when connection type Pooled connection is selected. 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. Verj.io 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.

For application servers other than Tomcat, use connection type Pooled connection configured in application server..

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 saved
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	· 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 the validationQuery property Checklevel 2 is recommended.
validationQuery	SQL statement used to test a connection before it is supplied to a requestor when checkLevel 2 is specified. Here are some common examples: Apache Derby: select 1 from SYSIBM.SYSDUMMY1 Mysql: select 1 Oracle: select 1 from dual MS SQL Server: select 1 Postgres: select version() !! This 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. A value of 0 is recommended (prepared statement caching is disabled).

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.

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 ebase.xml in folder userdata/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 SYSIBM.SYSDUMMY1"

checkLevel="2"

preparedStatementCache="0"

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 Verj.io 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 Wizard

This wizard helps in the creation of a new pooled Database Connection. The following pages are displayed by the wizard:

1. Introduction Page

Name

Enter the Database Connection name, this is the name used within the Verj.io system in all references to this Database Connection

2. Database Type Page

Database Type

Select from the dropdown list, select Other if your database is not in the list

JDBC Driver

This is set automatically from the previous field but can also be entered manually if required. When Next is clicked from this page, the system checks whether the JDBC Driver exists on the server. If the driver is not found, additional pages are displayed with instructions for locating and installing the driver.

!! Installation of a JDBC Driver requires a restart of the server. You will then need to restart the wizard to create the Database Connection.

3. Database URL Parameters Page

This is the main page where most of the connection parameters are specified.

Server Host Name	Name or ip address of the database server
User Name	User name to connect to the database
Password	Password to connect to the database (this will be encrypted when saved)
Database Name	Usually this parameter specifies the database name within the database server system. For some database systems, the terminology may be slightly different e.g. for Oracle, this field specifies the Oracle System ID (SID). The Find.. button is shown for database systems that support discovery and shows a list of available database names.

If a non-standard port is needed, this can be configured on the next page.

4. Properties Summary Page

This page shows the generated properties, and these can be changed by clicking on the appropriate values or by going back through the wizard pages.

e.g. to configure a non-standard port, edit the url property. Ports are usually specified with a colon (:) delimiter after server name e.g. localhost:1522.

Click Test Connection to check you can successfully connect to the database.

Click Finish to create a Database Connection with these properties.

Data Source Information

The data source information shows information regarding the database connection pooling and transaction manager. This popup gives a summary of the all the database connections objects in the connection pool.

The Database data source dialog window refreshes automatically every 5 seconds.

Database Id: Name of the database connection

Pool name: Name of the type of database pool.

Max pool size: The maximum size of the pool.

Min pool size: The minimum size of the pool.

Pool available size: Available number of database connections in the pool.

Pool total size: Total number of database connections in the pool.

Test query: The SQL statement query used to test the connection before it is used.

Click Refresh Information to refresh the data source information.
Click the Test Database Connection to run the validation query again the database.