Server Administration Application – Configuring
Database Connections
Database
Connection Properties
Gateway
Pooled Connection Properties
Pooled
connection configured in application server properties
See also: Server Administration Application Home Page, Database URL Connection Strings
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.
· Gateway pooled connection: this the same as a Pooled connection but it connects over a Verj.io Gateway Tunnel instead of going directly to the Database. This is useful for accessing private databases securely.
· 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
- JTDS driver used with MS SQL Server and Sybase
- PostgreSQL
- 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 |
|
· 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. |
|
|
SQL statement used to test a connection before it is supplied to a requestor when checkLevel 2 is specified. Here are some common examples:
!! 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). |
Gateway Pooled Connection Properties
When a Gateway pooled connection is select there are additional Database Connection properties.
|
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. |
|
Gateway |
The name of the Verj.io Gateway to use. |
|
Gateway Tunnel |
The name of the Gateway Tunnel on the selected Verj.io Gateway to use. |
|
Database Properties |
Additional url parameters to be appended to the end of the database url parameter. |
|
Url |
Displays the complete URL the chosen database driver will use. The &&{…} is the Gateway Tunnel Variable Name used to send the database connection over the selected Gateway Tunnel. |
All the other properties are the same as for Pooled connections (see above), except that the Url property cannot be specified.
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.