- Excel Desktop Client new !
An Author may create SQL queries to be run in a database from within the EASAP Builder.
To configure a connection to the database:
EASA is written in Java™ therefore, it uses Sun’s JDBC™ technology database connections. As a result, EASA needs a JDBC technology-enabled driver for making a connection 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 only includes a set of JDBC drivers for ODBC type databases. See:
Once you have the appropriate JDBC driver, upload this driver to the EASA Server before configuring the connection to your database. Follow the instructions below to upload your driver:
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.
Tip: Use an EASA Announcement to notify Users when the EASA Server will be restarted, see Communicating with EASA Users.
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:
Tip: For more information on database URLs, please refer to the document opened by clicking on the Click here for a list of database drivers and urls link located on the New Database page.
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
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.
Note: 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.
To change the database port, follow the relevant instructions below:
Modify the two files below:
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.
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:
|Name of Parameter||Use||Recommended Value|
|Initial Pool Size||The number of connections that are maintained at all times.||0|
|Max Active Connections||The maximum number of connections can be active at any point in time.||Double the amount of the maximum Excel processes available.|
|Max Idle Connections||The maximum number of connections at any point in time.||Double the amount of the maximum Excel processes available.|
|Min Idle Connections||The minimum number of connections at any point in time.||Same as the Initial Pool Size.|
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:
Which is the better option? It depends on the network layout (Are the machines visible to each other?)
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:
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. The steps for modifying a database connection are as follows:
If you need to delete a database connection, click on the check box next to the database in the table and then click the Delete button.
To remove old JDBC drivers, follow these steps:
To remove an old JDBC driver on EASA versions 4.3 or older, follow these steps: