Configuring Datasources

Datasources can be defined in 3 places:

Configuring the Database Connector in the tomcat bundle

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

• dbconnector.properties - this file is used to define database resources and configuration properties.
• configuration.xml - define your database querysets and queries in this file

Follow these steps:

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:

      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
        
        
      • 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
      
      

       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
        

      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.

      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

      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.

      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:

      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:

      jdbc:jtds:sqlserver://<sqlhost>:59377/acswa;instance=xyzzy

      Oracle 11g

      Here is an example that connects to an Oracle 11g driver.

      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.

      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.

      <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:

• dbconnector.properties - this file is used to define database resources and configuration properties.
• configuration.xml - define your database querysets and queries in this file

Follow these steps:

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.2\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:

   # 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
     
     
   • 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.2\config directory. Add the contents below, which defines querysets named myStore and BIRT, to your configuration.xml file to get you started.

   <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.

       Click here

      The database.zip file has the following structure:
      
      

      Note the files in the database-connector-2.5.0 directory that are used to manage the Database Connector as services on *nix and Windows operating systems: These files should be run as an administrator.

      • Install-Service.bat - installs the Database Connector on a Windows system as a service named frevvo Database Connector.

      • Uninstall-service.bat - uninstalls the frevvo Database Connector on a Windows operating system

      • Start-DB Connector-Service.bat - starts the frevvo Database Connector on a Windows operating system

      • Start-DBConnector.sh - starts the frevvo Database Connector as a *nix console instance.

      • Restart-DBConnector-Service.bat - restarts the frevvo Database Connector on a Windows operating system

      • Restart-DBConnector.sh - restarts the frevvo Database Connector as a *nix console instance.

      • Stop-DBConnector-Service.bat - stops the frevvo Database Connector on a Windows operating system.

      • Stop-DbConnector.sh - stops the frevvo Database Connector *nix console instance.

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

Follow these steps:

1. Edit <frevvo-home>\tomcat\conf\Catalina\localhost\context.xml.default,
   
   

   1. Add the Resource name parameter. An example is shown below:

      <Resource name="jdbc/<your database name>"
                auth="Container"         
                driverClassName="com.mysql.jdbc.Driver"
                username="<your database user>"
                password="<your database password>"
                type="javax.sql.DataSource"
                url="jdbc:mysql://localhost/<your queryset name>?autoconnect=true"/>

2. Edit frevvo\tomcat\conf\dbconnector.properties file. Verify the Add the frevvo.connectors.database.configuration property has been set and Add the dbconnector.resource-ref.name property as shown below:

   dbconnector.queryset@BIRT.resource-ref.name=jdbc/<name of your resource>

Disabling a QuerySet/Query

When you are developing your integration, you might want to disable a particular queryset/query so you can focus on the one you are troubleshooting. QuerySets/queries can be disabled in one of two ways:

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.

   <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.

   dbconnector.querySet@BIRT.enabled=false

   This property disables all querysets

   dbconnector.querySet.enabled=false