...

Configuring the

...

Database Connector in the tomcat bundle

When deploying the connector in the tomcat bundle, the relevant configuration files are:

...

1. These instructions assume you have already installed the tomcat bundle and the Database Connector and have verified that the connector up and running.
2. Stop if it is running. You do not have to stop the Insight Server.

3. Navigate to the <frevvo-home>\tomcat\conf directory. Create a file named dbconnector.properties. This file will contain database connector configuration properties and datasource information.

   1. Add the frevvo.connectors.database.configurations property to specify the path to your configuration.xml file.  Use the forward slash - \ - in the path as the backslash in a properties file must be escaped.

   2. Define datasources for each query set in your configuration.xml

      Here is an example if you are using MySQL:

      Code Block

      frevvo.connectors.database.configuration=file:///C:/frevvo/config/configuration.xml dbconnector.queryset@myStore.resource-def.url=jdbc:mysql://localhost:3306/classicmodels?createDatabaseIfNotExist=true&autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&useServerPrepStmts=true dbconnector.queryset@myStore.resource-def.user=root dbconnector.queryset@myStore.resource-def.password=root dbconnector.queryset@BIRT.resource-def.url=jdbc:mysql://localhost:3306/classicmodels?createDatabaseIfNotExist=true&autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&useServerPrepStmts=true dbconnector.queryset@BIRT.resource-def.user=root dbconnector.queryset@BIRT.resource-def.password=root

      Let's take a look at the properties in this example:

      • the frevvo.connectors.database.configuration property specifies the path to your custom configuration.xml file. Be sure to use the forward slash - \ - in the path even if you are running on the Windows Operating system.
      • the next section provides the resource URL, database user and password for the myStore queryset defined in the configuration file.
      • the next section provides the resource URL, database user and password for the BIRT queryset
        
        

      Note

      A database URL is a Universal Resource Locator (URL) that specifies a particular type of database server (compatible with the JDBC driver you installed. In addition you can also specify the database name to use for the connection. The format for the database URL is database driver specific. For example,  the correct format for a MySQL database is: jdbc:mysql://[host][:port optional]/[database name] Check with your database administrator or the documentation for your database drive for details on the correct database URL format. You can add other parameters to the URL. For examle, it is recommended that you add the useServerPrepStmts=true parameter to the database connection URL if you are using MySQL as your external database. This property improves the queryset/query validation if a column or table is missing. With this parameter in place, the MySQL driver reports a warning that the table or column doesn't exist. Validation sql and drivers are automatically selected by the database connector. There is no need to explicitly set them in the datasource definitions If you prefer to define your datasources on the container level, refer to the  Defining the Data Source with Resource-ref topic. This approach is only supported in the tomcat bundle.

      Examples of Datasource Definitions in the dbconnector.properties file
      
      

      Expand
      title Click here for examples of dbconnector.properties files with datasources for some common databases
      MySQL example A queryset named "alltest" against a MySql database named "test" running on localhost on the default port when the login for user "root" requires no password. You can add more parameters such as: createDatabaseIfNotExist=true - this property set to true will create an empty database if the specified one does not exist useServerPrepStmts=true - this property is recommended for MySQL database. With the property in place the driver reports a warning if a table or column does not exist Code Block dbconnector.queryset@alltest.resource-def.url=jdbc:mysql://localhost:3306/test dbconnector.queryset@alltest.resource-def.user=<your database user id> dbconnector.queryset@alltest.resource-def.password=<your database password> HSQLDB example Here is an example that connects to an HSQLDB driver. Code Block dbconnector.queryset@mystore.resource-def.url=jdbc:hsqldb dbconnector.queryset@mystore.resource-def.user=<your database user id> dbconnector.queryset@mystore.resource-def.password=<your database password> SQL Example Here is an example that connects to a SQL Server driver Code Block dbconnector.queryset@BIRT.resource-def.url=jdbc:sqlserver://192.168.1.102;DatabaseName=mystore dbconnector.queryset@BIRT.resource-def.user=<yourUser> dbconnector.queryset@BIRT.resource-def.password=<yourPassword> SQL jTDS Example This Url connects to a SQL Server using the jTDS driver that works with Microsoft SQL 2008. Code Block dbconnector.queryset@mystore.resource-def.url=jdbc:jtds:sqlserver://localhost:1433;DatabaseName=db dbconnector.queryset@myStore.resource-def.user=<yourUser> dbconnector.queryset@myStore.resource-def.password=<yourPassword> For SQL server, the database name can also be specified as part of the Url rather than using the DatabaseName optional parameter. For example if your database name is acswa and the SQL server is running on 59377: Code Block dbconnector.queryset@BIRT.resource-def.url=jdbc:jtds:sqlserver://<sqlhost>:59377/acswa As of SQL 2000, Microsoft SQL allows installation of multiple SQL named instances. If your SQL server was installed this way you must use the instance parameter. For example if you database instance was named xyzzy: Code Block jdbc:jtds:sqlserver://<sqlhost>:59377/acswa;instance=xyzzy Oracle 11g Here is an example that connects to an Oracle 11g driver. Code Block dbconnector.queryset@mystore.resource-def.url=jdbc:oracle:thin:@localhost:1521:ServiceName dbconnector.queryset@myStore.resource-def.user=<yourUser> dbconnector.queryset@myStore.resource-def.password=<yourPassword>

   3. You can add other properties to this file. For example, If you want to change the loglevel for the database-connector.YYYY-MM-DD logfile when creating and troubleshooting queries for your forms/flows, add the spring.profiles.active property as shown below. This property loads a profile called dev which sets the loglevel to DEBUG. Refer to Logfiles for more details about logging options.

      Code Block
      spring.profiles.active=dev

   4. Create a subfolder named config under your <frevvo-home> directory i.e. c:\frevvo\config.

   5. Create your configuration.xml file in the config directory or the location specified in the frevvo.connectors.database.configurations parameter in the dbconnector.properties file. Querysets/queries are defined in this file. Add the contents below, which defines querysets named myStore and BIRT, to your configuration.xml file to get you started.

      Code Block
      <dbconnector> <queryset name="myStore"> </queryset> <queryset name="BIRT"> </queryset> </dbconnector>

      Refer to the Defining SQL Queries topic for detailed information about the configuration.xml file content.

4. Verify that you:
   
   1. Navigated to the <frevvo-home>\frevvo\ext\connectors directory.
   2. Unzipped the database.zip file to a temporary location of your choice: e.g. c:\tmp. This will create a folder c:\tmp\database. We'll refer to this directory below as <db-tmp>
   3. Copied the <db-tmp>\database\database-connector-2.5.0\database.war to <frevvo-home>\tomcat\webapps folder.
   4. Installed the JDBC driver

5. Start and the Insight server if necessary

6. .Browse the http://localhost:8082/database/status page

7. Verify that query validation page is loaded with status Passed and is pointing to your custom configuration.xml file.

Configuring the v2.5.

...

2 connector in Standalone mode

When deploying the connector in Standalone mode, the relevant configuration files are:

...

1. Verify that you have
   
   • Downloaded the Standalone connector zip file.
   • Unzipped the database.zip file to a location of your choice. We will refer to this directory as <db-home>
   • Installed the JDBC driver, if required.

2. Edit the dbconnector.properties in the database\database-connector-2.5.12\config directory of the standalone installation. The dbconnector.properties file is where you can customize database connection properties (such as server port) and configure datasource definitions. Add the datasource definitions for your querysets/queries to this file then save it.
   
   Here is an example if you are using MySQL:

   Code Block

   # Customize the DbConnector here logging.file=./logs/database-connector.%d{yyyy-MM-dd}.log server.port=8081 dbconnector.queryset@myStore.resource-def.url=jdbc:mysql://localhost:3306/classicmodels?createDatabaseIfNotExist=true&autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&useServerPrepStmts=true dbconnector.queryset@myStore.resource-def.user=root dbconnector.queryset@myStore.resource-def.password=root dbconnector.queryset@BIRT.resource-def.url=jdbc:mysql://localhost:3306/classicmodels?createDatabaseIfNotExist=true&autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&useServerPrepStmts=true dbconnector.queryset@BIRT.resource-def.user=root dbconnector.queryset@BIRT.resource-def.password=root

   Let's take a look at the properties. You must provide the resource information for each queryset in your configuration.xml file.

   • the server.port property specifies the port number that your database is using.
   • the next section provides the resource URL, database user and password for the myStore queryset
   • the next section provides the resource URL, database user and password for the BIRT queryset
     
     

   Note

   A database URL is a Universal Resource Locator (URL) that specifies a particular type of database server (compatible with the JDBC driver you installed or was pre-installed in database\WEB-INF\lib) and a particular host.  In addition you can also specify the database name to use for the connection. The format for the database URL is database driver specific. For example,  the correct format for a MySQL database is: jdbc:mysql://[host][:port optional]/[database name] Check with your database administrator or the documentation for your database drive for details on the correct database URL format. Add the useServerPrepStmts=true parameter to the database connection URL if you are using MySQL as your database. This parameter is needed to improve the queryset/query validation if a column or table is missing. With this parameter in place, the MySQL driver reports a warning that the table or column doesn't exist.

3. Create your configuration.xml file in the <db-home>\database\database-connector-2.5.12\config directory. Add the contents below, which defines querysets named myStore and BIRT, to your configuration.xml file to get you started.

   Code Block
   <dbconnector> <queryset name="myStore"> </queryset> <queryset name="BIRT"> </queryset> </dbconnector>

   
   Refer to the Defining SQL Queries topic for detailed information about the configuration.xml file content.
   
   

4. Restart the connector using one of these methods:

   1. Open a command prompt and navigate to the <db-home>\database\database-connector-2.5.0 directory
      Type java -jar database.war

   2. Or If you installed the Database Connector as a service, click the Restart-DBConnector-Service.bat,sh to restart  the service for your operating system. Click below for a list of the batch files and shell scripts that come with the connector.

      Expand
      title Click here
      Insert excerpt DB Connector Installation DB Connector Installation nopanel true

5. Browse the http://localhost:8081/database/status page

6. Verify that query validation page is loaded with status Passed and is pointing to your custom configuration.xml file.

Defining the Data Source with Resource-ref in the tomcat bundle

...

1. Add the enable="true" attribute with a value of false to the <querySet/> or individual <query> elements in the configuration.xml file to completely disable it.

   Code Block
   <dbconnector> <querySet name="BIRT" enabled="false" ...>

2. The same can be done in a dbconnector.properties file by following the XML structure. The property shown below uses the @BIRT selector to set the enabled attribute to false only for the BIRT querySet.

   Code Block
   dbconnector.querySet@BIRT.enabled=false

   This property disables all querysets

   Code Block
   dbconnector.querySet.enabled=false

...