An Author may create SQL queries to be run in a database from within the EASAP Builder.

To configure a connection to the database,

• The required driver must be uploaded to the EASA Server
• Specific database connections must be defined

EASA is written in Java™ and uses JDBC™ for database connections. EASA needs a JDBC-enabled driver to connect to an external database. A JDBC driver has a *.jar file extension.

Each database, such as SQL Server™, Oracle®, MySQL®, Sybase®, etc., requires its own driver.

EASA includes a set of ODBC-type JDBC drivers under: EASA > Administrator > Configure > Databases > Configure Drivers

For other types of JDBC driver, we need to upload the driver to the EASA Server before configuring the database connection:

1. Open a web browser and go to the EASA web address.
2. Log in and set: EASA > Set Mode > Administrator
3. Select: Configure > Databases
4. Click: Configure Drivers
5. Click: Browse
6. Select the local *.jar driver file

Some JDBC drivers come in the form of *.zip files. If so, simply rename them to have a *.jar extension prior to uploading the files.

• Click: Upload button.
• Finally, stop and restart the EASA Server for the new database driver to be recognized. Do one of the following,
  1. use the 'Stop EASA' and 'Start EASA' shortcuts on the desktop
  2. restart the EASA service

Use an EASA 'Announcement' to notify Users when the EASA Server will be restarted, see: Announcements and Alerts

The JDBC driver has now been uploaded to the EASA Server and is now enabled to connect to the database.

Once the correct JDBC driver is located on the EASA Server, configure and connect to the new database connection:

1. Open a web browser, go to the EASA web pages.
2. Log in to EASA
3. Set mode: Administrator
4. Select: EASA > Configure > Databases
5. Click: Configure Databases
6. Click: Add New Database
7. On the 'New Database' form select the 'Database Type:' from choices available in the list.
8. Specify 'Database Name:' which will be used by authors when selecting a database to access.
9. Specify the 'URL:' address which points to the location of the database
   For StockData to work from a remote EASAP Server replace 'localhost with a hostname or IP address

For more information on database URLs, please refer to the document opened by clicking on: 'Click here for a list of database drivers and urls' link located on the New Database page

• If needed, specify the 'User Name:' and 'Password:' to be used when connecting to the database.
• At this point, you should select the appropriate driver by providing its 'Driver Name'
• For testing purposes a 'Validation Query:' may be entered.
  'Generate Optimal Validation Query' automatically creates valid SQL for the specific database.
• 'Advanced Settings' will override the database connection pool defaults.
• Click: 'Save' to complete the definition of the database connection.

At this point, you will be brought back to the 'Configure Databases' page and you should see the new database in the table with its Status displayed as ‘unknown’. The final step is to run a test to establish the connection by

• Click: Run Test ()

If the test connection was successful, then you should see the message 'Test passed' and the results of the Validation Query on the 'Test Results' page, which is displayed after the 'Run Test' button is pressed. Also, when you return to the 'Configure Databases' page, you should see a 'Status' of ‘active’ followed by the date and time of the last successful test for your new database connection.

'Validation Query:' is not mandatory, but if one is specified it must return some data. A completely empty or null result will cause an error to be returned.

'Query Tester' allows queries to be repeatedly modified and run.

If the test connection failed, you will see error messages on the 'Test Results' page. You will need to go back and make modifications or corrections to the database connection settings as described in the next section, until the test connection is successful.

EASA's default database changed from EASA 5.5 to EASA 6.0.

If MySQL has not yet been installed, modify:

• <SERVERDATA>\admin\config\context.properties
  easa.db.port=new_port_number

If MySQL has already been installed update the port number in the following file:

• <EASAROOT>\db\mysql-5.7.17-win32\my.ini

In either case above the following files must be updated with the new port number:

• <SERVERDATA>\easa-share\admin\config\
  easa.cfg.xml
  excel.cfg.xml
  excelTesting.cfg.xml
  signer.cfg.xml (from EASA 6.1 onward)

• Restart the service

Modify the two files below,:

• <SERVERDATA>\admin\config\context.properties
  easa.db.port=new_port_number
• <EASAROOT>\EASA5.2\webapps\easa\WEB-INF\classes\hibernate.cfg.xml
  <property name=“hibernate.connection.url”>
       jdbc:hsqldb:hsql://localhost:new_port_number/easa;SCHEMA=EASA;hsqldb.write_delay=false;
  </property>

• Restart the service

Excel uses ANSI which is a machine dependent character set.

Follow the two steps below to change the character set to a standard, utf8 in this example:

1. Update two properties in the following two files
   • <SERVERDATA>\easa-share\admin\config\
     excel.cfg.xml
     excelTesting.cfg.xml
   • <property name=“hibernate.connection.CharSet”>utf8</property>
   • <property name=“hibernate.connection.characterEncoding”>utf8</property>
2. Modify the database schema; run the script below

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE exceltesting.data CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE exceltesting.named_range CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE excel.data CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE excel.named_range CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
SET FOREIGN_KEY_CHECKS=1;
ALTER DATABASE exceltesting CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER DATABASE excel CHARACTER SET utf8 COLLATE utf8_general_ci;

When there are remote servers set up to run EASAPs, the database configurations will apply to each server individually. These remote servers don't use the EASA server in connecting to the database but make direct connections instead based on the configurations.

What that means is that the following parameters are not global values but are applied to each remote server:

For example, if there is a desired maximum number of global connections to the database at any given time, it should be divided by the number of total EASAP Servers and the result should be used as a starting point to configure the above values.

There are two ways a remote EASAP/Excel Server and a database server may connect:

1. Direct connection between EASAP/Excel Server machine (recommended)
2. Via EASA Server as a proxy server that will pass database traffic back and forth

Which is the better option? It depends on the network layout (Are the machines visible to each other?)

Modify: EASA > Administrator > Configure > Databases > Configure Databases > Advanced Settings

To enable a direct connection between the two servers, set: Allow Direct Connection > true

To make the EASA Server act as a proxy for communication between the two servers, set: Allow Direct Connection > false

Easa Proxy Port' will be used by the remote machine to communicate with the EASA Server.

To use the EASA Server as a proxy server with an Oracle database, the database must be configured to allow 'transparent proxy' and will require configuring the EASA Server as an 'Oracle API Gateway', see…

https://docs.oracle.com/cd/E39820_01/doc.11121/gateway_docs/content/common_transparent_proxy.html

If you need to modify the definition of your database connection, for example to switch to a new driver or to change the URL to a new location of the database, then you can edit the connection directly from the Configure Databases table.

Follow these steps for modifying a database connection:

1. Click on the box next to the database in the table and then click: Edit Database
2. Make your changes on the 'Edit Database' form
3. Click: Save
4. Finally, you can click on the 'Run Test' button () to test and review the modification to the database connection.

If you need to delete a database connection, click on the check box next to the database in the table and then click: Delete

Follow these steps to remove old JDBC drivers from EASA version since 4.3 :

1. Stop the EASA service on the EASA Server
2. Remove the driver file from: <SERVERDATA>\drivers
3. Start the EASA service

Follow these steps to remove an old JDBC driver on EASA versions 4.3 or older:

1. Stop the EASA service on the EASA Server
2. On the EASA Server, remove the driver file from: <EASAROOT>\webapps\easa\WEB-INF\lib
3. Remove the corresponding line from the file: <EASAROOT>\webapps\easa\WEB-INF\lib folder\db.properties
4. Start the EASA service