GTD Excel Report Server Connection Pool Management

GTD Excel Report Server pull data from the Database, and output data in Excel format.

GTD App supports two types of data source.

• GTD-APP Managed Data Source
• Container Managed Data Source

In this section, we discuss GTD-APP Managed Data Source.

To learn Container Managed Data Source, please click here.

Connection pooling is a technique of creating and managing a pool of connections that are ready for use by any thread that needs them.

This technique of pooling connections is based on the fact that most applications only need a thread to have access to a JDBC connection when they are actively processing a transaction, which usually take only milliseconds to complete. When not processing a transaction, the connection would otherwise sit idle. Instead, connection pooling allows the idle connection to be used by some other thread to do useful work.

The main benefits to connection pooling are:

• Reduced connection creation time
• Simplified programming model
• Controlled resource usage

GTD Report Server retrieves data via JDBC driver, so that it supports many different databases, such as Oracle, MySQL, SQL Server.

There are many types of JDBC drivers. If all the drivers is packed into the installation packackge, the size will be large. So the installation package includes 3 types of JDBC driver for Oracle, MySQL, SQL Server.

Perhaps you are using other database such as PostgreSQL, Sybase, DB2, in this case, you should try the following procedure:

1. download the drivers and put it to the directory below.

${GTD_HOME}/ExcelReportSvr/WebRoot/WEB-INF/lib

If your database is not PostgreSQL, Sybase or DB2, please omit the step 2 and 3.

2. Logon GTD Server, using user with administrator or developer privilege, and then click JDBC Driver Manager link from Options tab.

3. Find the corresponding JDBC driver, and then click Enable button.

1. Logon GTD Server, using user with administrator or developer privilege.

Please visit Testing GTD server in console mode if you don't know how to logon the GTD Server.

2. The Home page appear, please click the Database Connection Manager link from Options tab.

3. The Connection Pool/Data Source Manager page appears. It is simular to the figure below.

This page lists information of all connection pools of GTD Server. What you can do in this page:

• View all pools information, such the URL and the number of checked out connection.
• Update connection pool, for example, change the password.
• Configure JDBC Drivers
• Add connection pool
• Remove connection pool

1. Login GTD Server, using user with administrator/developer privilege, navigate to Connection Pool/Data Source Manager

2. Click "Configure a new JDBC Connection Pool... " in the bottom-left of Connection Pool/Data Source Manager.

3. The Configure a JDBC Connection Pool page appears.

4. Fill in the information given to you by your DBA or administrator, and then click Submit button.

Example:

Troubleshooting

Q. I can not find my database from Database Type select box.

A: Please navigate to JDBC Driver Manager, look for your database.

• If your database exists in JDBC Driver Manager, please enable it. For more information, please visit JDBC Driver Manager section.
• If you can not find your database in JDBC Driver Manager, you should have to create connection pool through Advanced mode.

The section above describes how to create common connection pool for common databases. You might need to create connection pool through advanced mode due to the following reasons.

• Your database is not listed in JDBC Driver Manager, for example informix.
• You want to use some special jdbc driver other than the default driver. For example you want to use jTDS driver instead the driver provided by Microsoft for SQL Server.
• You want to set some Configuration Properties for the connection. For example, connectTimeout Property for MySQL.

To create an advanced connection pool, do the following procedure.

1. Login GTD Server, using user with administrator/developer privilege, navigate to Connection Pool/Data Source Manager

2. Click "Configure a new JDBC Connection Pool... " in the bottom-left of Connection Pool/Data Source Manager.

3. The Configure a JDBC Connection Pool page appears, and then click Advanced button.

4. You will see a page simular to the figure below. Fill in the information required, and then click Submit button.

Example:

Troubleshooting

Q. I get "driver class not found' error.

A: Please download the drivers and put it to the directory below.

${GTD_HOME}/ExcelReportSvr/WebRoot/WEB-INF/lib

This section applies to Oracle Database only.

TNSNAMES.ORA is a SQL*Net configuration file that defines databases addresses for establishing connections to them.

Basic Format of tnsnames.ora File

net_service_name=
(DESCRIPTION=
(ADDRESS=(protocol_address_information))
(CONNECT_DATA=
(SERVICE_NAME=service_name)))

A sample

ocrl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl.mycompany.com)(PORT = 1541))
)
(CONNECT_DATA =
(SERVICE_NAME = ocrl)
)
)

How to add multiple data sources from tnsnames.ora

1. In Connection Pool/Data Source Manager, click Setup Oracle Data Source by Tnsnames.ora .

2. Open the tnsnames.ora with text editor(e.g. NotePad), copy/paste the entries to the inbox. See the figure below.

3. Click submit button.

4. The GTD server will parse the tnsnames.ora, and then list the entries. See the figure below.

5. Please select the entries you want to add by checking the Is Add box.

6. Enter the user name and password, adjust the name of the data source.

7. Click the submit button.

8. The entry(s) is added, but the GTD will not verify the data source, you should please go to Connection Pool/Data Source Manager, click View, and then verify the data source.

In order to help the new user get start quickly, the GTD Server embeds a HSQLDB database. There are about 3 tables is creatd in the database.

Here is the DDL statement:

CREATE TABLE department (
name varchar(20) NOT NULL, id int NOT NULL PRIMARY KEY );

CREATE TABLE employee (
name varchar(20) default NULL,
payment double default NULL,
bonus double default NULL,
birthDate date default NULL,
id int NOT NULL PRIMARY KEY,
depid int, FOREIGN KEY (depid) REFERENCES department (id) );

create table salesrecord (
productName varchar(100),
productLine varchar(100),
customerName varchar(100),
quantity int,
MSRP DECIMAL(10,2),
buyPrice DECIMAL(10,2),
orderDate date );