Live Forms v5.1 is no longer supported. Click here for information about upgrading to our latest GA Release.

Skip to end of metadata
Go to start of metadata

 

On this page:

Configuration Steps

1)  Copy the JDBC driver appropriate for your database into <frevvo-home>\tomcat\webapps\database\WEB-INF\lib

The database connector contains pre-installed drivers for the most popular databases. So you may be able to skip this step. See Install a JDBC driver.

2)  If you did have to copy a JDBC driver into lib you must then Restart frevvo, Double click <frevvo-home>\stop-frevvo. Wait until frevvo stops. Then Double click <frevvo-home>\start-frevvo

3)  Configure your database connector Data Source by editing <frevvo-home>\tomcat\webapps\database\WEB-INF\etc\configuration.xml

4)  Define SQL Statements to read/write to your databasse by editing <frevvo-home>\tomcat\webapps\database\WEB-INF\etc\configuration.xml.

We recommend that you do not leave your database configuration.xml file in its default locations. See configuration file location below.

Configuration File Location

We recommend not leaving the database configuration file in its default location under the <frevvo-home>\tomcat\webapps\database\WEB-INF\etc\configuration.xml.
One benefit of moving configuration.xml is any SQL query changes you make as you are in the development cycle will immediately be picked up by the database connector without having to stop/restart the connector. This can be very convenient.

To move configuration.xml to c:/frevvo/dbconfig

  1. Create folder c:/frevvo/dbconfig
  2. Copy <frevvo-home>\tomcat\webapps\database\WEB-INF\etc\configuration.xml to c:\frevvo\dbconfig\configuration.xml
  3. Edit <frevvo-home>\tomcat\conf\catalina\localhosts\context.xml.default. Add this line to context.xml.default (as a Parameter tag):
<Parameter name="frevvo.connectors.database.configuration" value="c:/frevvo/dbconfig/configuration.xml" override="false"/>

Be sure to add it OUTSIDE of the manager tags in the file. Also, verify that your path to the configuration file is correct. Here is an example of how the file should look after editing:


<Context swallowOutput="true" unloadDelay="40000">
           <Manager className="org.apache.catalina.session.PersistentManager" saveOnRestart="false">
                         <Store className="org.apache.catalina.session.FileStore"/>
           </Manager>
         <Parameter name="frevvo.connectors.database.configuration" value="C:/frevvo/dbconfig/configuration.xml" override="false"/>
</Context>

 

If frevvo is running, when you edit and save this change to context.xml.default, frevvo should pickup this change within a couple seconds. If it doesn't, then stop and restart frevvoby: Double click <frevvo-home>\stop-frevvo. Wait for it to stop. Then double click <frevvo-home>\start-frevvo

Currently you MUST restart frevvo after moving the configuration file location. If you don't restart frevvo the connector will return a blank page rather than data and the frevvo tomcat log will show an NPE error.

Define the Data Source

The frevvo database connector needs to know:

  • the path to your database
  • a username to login to your database
  • a password for this username
  • which JDBC driver your database requires.

This information is setup in the file <frevvo-home>\tomcat\webapps\database\WEB-INF\etc\configuration.xml. If you moved this file out of the webapps\database directory to a better configuration file location then edit the file there.

To configure the database connector to interact with your database:

  1. Edit configuration.xml
  2. You will see a <queryset> element already in this file (approx 164 lines long). This is the test query you ran when first testing the database connector installation.
  3. Make a duplicate copy of the entire <queryset> element (all approx 164 lines) so that you have two <queryset> elements in the configuration.xml
  4. Edit the <resource-def> element in the <queryset> element you just duplicated. The <resource-def> element defines where and how to connect to your own database.

This is the format of the <resource-def> element:

<resource-def>
    <url>YOUR database Url HERE</url>   
    <driver>YOUR driver HERE</driver>
    <user>YOUR database user HERE</user>
    <password>YOUR password HERE</password>
</resource-def>

The <resource-def> defines the database connection inside the database connector itself.

Under most operating systems the database connector will automatically reload the configuration.xml as soon as you edit and save changes, without the need to restart tomcat or the database connector itself. However under Mac OS auto reload does not work. To ensure that the change is reflected restart tomcat manually via the stop/start frevvo scripts.

Configuring a <resource-ref>

There is an alternate approach that defines the database connection in the application container (Tomcat if you are using the frevvo-tomcat bundle) instead of inside the database connector. To use this approach:

  1. Edit <frevvo-home>\tomcat\conf\Catalina\localhost\context.xml.default.
  2. Define a <resource> as shown below.
  3. Replace the <resource-def> element shown above in configuration.xml with a <resource-ref> element as shown below.

The XML samples below assume a MySql database driver. You will need to replace this sample values to match your database type.

This is the format of the <resource> element in context.xml.default.

<Resource name="jdbc/mystore_ds" 
	   	  auth="Container" 
	   	  factory="org.apache.commons.dbcp.BasicDataSourceFactory"             
	      driverClassName="com.mysql.jdbc.Driver"
	      username="YOUR database user HERE"
	      password="YOUR database password HERE"
	      type="javax.sql.DataSource"
          url="jdbc:mysql://localhost/mystore?autoconnect=true"/> 

This is the format of the <resource-ref> element in configuration.xml.

<resource-ref>
    <description>MySQL Customers</description>
    <res-ref-name>jdbc/mystore_ds</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

mySQL example

A mySql database named "test" running on localhost on the default port when the login for user "root" requires no password:

<resource-def>
    <url>jdbc:mysql://localhost/test</url>    
    <driver>com.mysql.jdbc.Driver</driver>
    <user>root</user>
    <password></password>
</resource-def>

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. In the example above for mySql the correct format 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.

HSQLDB example

Here is an example that connects to an '''HSQLDB driver'''.

<resource-def>
    <url>jdbc:hsqldb:file:mystore</url>       
    <driver>org.hsqldb.jdbcDriver</driver>
    <user>sa</user>
    <password></password>
</resource-def>

The following examples are for several common databases. Please consult your database documentation and version for the exact configuration settings.

SQL Example

Here is an example that connects to a '''SQL Server driver'''.

<resource-def>
    <url>jdbc:sqlserver://192.168.1.102;DatabaseName=mystore;</url>       
    <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
    <user>yourUser</user>
    <password>yourPassword</password>
</resource-def>

SQL jTDS Example

This Url connects to a SQL Server using the jTDS driver that works with Microsoft SQL 2008

<resource-def>   
  <url>jdbc:jtds:sqlserver://localhost:1433;DatabaseName=db;</url>
  <driver>net.sourceforge.jtds.jdbcx.JtdsDataSource</driver>
  <user>yourUser</user>
  <password>yourPassword</password>
</resource-def>

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:

<url>jdbc:jtds:sqlserver://<sqlhost>:59377/acswa</url>

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:

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

AS400/DB2 Example

Here is an example that connects to an '''AS400/DB2 Open Universal driver'''. Notice that the url also specifics a date separator used in this database.

<resource-def>
    <url>jdbc:as400://170.254.98.250:8888;date separator=-</url>       
    <driver>com.ibm.as400.access.AS400JDBCDriver</driver>
    <user>yourUser</user>
    <password>yourPassword</password>
</resource-def>

Oracle 11g

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

<resource-def>
    <url>jdbc:oracle:thin:@localhost:1521:ServiceName</url>                  
    <driver>oracle.jdbc.driver.OracleDriver</driver>
    <user>yourUser</user>
    <password>yourPassword</password>
    <validationQuery>select 1 from dual</validationQuery>
<resource-def>

 

 

 

  • No labels