Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Section
Column
width0px

 

These steps are required when you convert from a demo/trial

Frevvoproduct
server installation to a production installation.

Tip

While you are demo-ing

Frevvoproduct
you do not need to do the Database Setup steps discussed below.

Your

Frevvoproduct
 server requires an SQL database for storing users and form submissions. The follow SQL databases have been certified:

The

Frevvoproduct
server may function with other SQL database types but only the above set has been certified.

Warning

The default HSQLDB is ONLY sufficient for demo/trial versions of

Frevvoproduct
. All production
Frevvoproduct
users MUST switch their
Frevvoproduct
software to a production quality database.

The following steps describe how to change the underlying database from the demo/trial HSQLDB to a production quality SQL database. The database configuration file frevvo.xml is located in <frevvo-home>/frevvo/tomcat/conf/Catalina/localhost directory.

Column
width240px

On This page:

Table of Contents
maxLevel1
 

Select your database type

...

Code Block
<!--
  HSQLDB Resource 
       <Resource auth="Container"
                type="javax.sql.DataSource"
                factory="org.apache.commons.dbcp.BasicDataSourceFactory"
                name="jdbc/frevvoDS"
                driverClassName="org.hsqldb.jdbcDriver"
                username="sa"                password=""
                url="jdbc:hsqldb:file:${catalina.home}/../data/db/forms"
                maxActive="200"
                maxIdle="20"
                maxWait="10000"
                validationQuery="select 1 from INFORMATION_SCHEMA.SYSTEM_USERS"
                testOnBorrow="true"
                testOnIdle="true"
                timeBetweenEvictionRunsMillis="10000"
                removeAbandoned="true"
                logAbandoned="true"/>
 -->

Next, uncomment the pair of definitions for your database. Ex: if you are using SQL server, uncomment the definition for SQLSERVER Resource. Save the changes.

Code Block
 <!--
      <Resource auth="Container"         SQLSERVER Resource  
    type="javax.sql.DataSource-->
  
          <Resource auth="Container"
                factory="org.apache.commons.dbcp.BasicDataSourceFactory"
                namemaxActive="jdbc/userDS200"
                driverClassNamemaxIdle="org.hsqldb.jdbcDriver20"
                usernamemaxWait="sa10000"
                passwordvalidationQuery="select 1"
                urltestOnBorrow="jdbc:hsqldb:file:${catalina.home}/../data/db/users""true"
                testOnIdle="true"
                     maxActivetimeBetweenEvictionRunsMillis="20010000"
                maxIdleremoveAbandoned="20true"
                maxWaitlogAbandoned="10000true"
                validationQueryname="select 1 from INFORMATION_SCHEMA.SYSTEM_USERSjdbc/frevvoDS"
                testOnBorrow="true"
 driverClassName="net.sourceforge.jtds.jdbcx.JtdsDataSource"
                testOnIdleusername="trueroot"
                timeBetweenEvictionRunsMillispassword="10000"
                removeAbandonedtype="truejavax.sql.DataSource"
                logAbandonedurl="truejdbc:jtds:sqlserver://localhost/frevvoSubmissions"/>

-->

Next, uncomment the pair of definitions for your database. Ex: if you are using SQL server, uncomment the definition for SQLSERVER Resource. Save the changes.

Code Block
 <!--
                SQLSERVER Resource  
   -->
  
          <Resource auth="Container"
                factory="org.apache.commons.dbcp.BasicDataSourceFactory"
                maxActive="200"
                maxIdle="20"
                maxWait="10000"
                validationQuery="select 1"
                testOnBorrow="true"
                testOnIdle="true"
                timeBetweenEvictionRunsMillis="10000"
                removeAbandoned="true"
                logAbandoned="true"
                name="jdbc/frevvoDS"
                driverClassName="net.sourceforge.jtds.jdbcx.JtdsDataSource"
                username="root"
                password=""
                type="javax.sql.DataSource"
                url="jdbc:jtds:sqlserver://localhost/frevvoSubmissions"/>
          <Resource auth="Container"
                factory="org.apache.commons.dbcp.BasicDataSourceFactory"
                maxActive="200"
                maxIdle="20"
                maxWait="10000"
                validationQuery="select 1"
                testOnBorrow="true"
                testOnIdle="true"
                timeBetweenEvictionRunsMillis="10000"
                removeAbandoned="true"
                logAbandoned="true"
                name="jdbc/userDS"
                driverClassName="net.sourceforge.jtds.jdbcx.JtdsDataSource"
                username="root"
                password=""
                type="javax.sql.DataSource"
                url="jdbc:jtds:sqlserver://localhost/users"/>

Configure the Live Forms submissions database

Note
  1. The forms and users databases below must be created with UTF-8 encoding.

For your selected database type in frevvo.xml

...

Frevvoproduct

...

Configure the Live Forms submissions database

Note
  1. The forms and users databases below must be created with UTF-8 encoding.

For your selected database type in frevvo.xml

  1. Locate the frevvoDS data source url parameter:url="jdbc:jtds:sqlserver://localhost/frevvo". You can change the name "frevvo" to whatever you wish, "
    Frevvoproduct
    " for example. But it must match the database name you create in your SQL server. See verify connection Url below.
  2. Create a UTF-8 encoded database "frevvo" (assuming you left the url parameter database name as the default) in your SQL server
  3. In the
    Frevvoproduct
     installation locate the script <frevvo-home>/data/sql/forms-<database type>.sql. Ex: for mySQL the script is named <frevvo-home>/data/sql/forms-mysql.sql
  4. Run that script in your "
    Frevvoproduct
    " database. That will create all the tables required to persist submissions
  5. Set the frevvoDS data source parameters username and password to a user that has write permissions to the "
    Frevvoproduct
    " database.
  6. The submissions database setup is now complete.

Configure the LiveForms Live Forms users database

For your selected database type in frevvo.xml,

  1. Locate the userDS data source url parameter. : url="jdbc:jtds:sqlserver://localhost/users". You can change the name "users" to whatever you wish, "
    Frevvoproduct
     users" for example. But it must match the database name you create in your SQL server. See verify connection Url below.
  2. Create a utf8 UTF-8 encoded database "users" (assuming you left the url parameter database name as the default) in your SQL server
  3. In the
    Frevvoproduct
     installation locate the script <frevvo-home>/data/sql/users-<database type>.sql. Ex: for mySQL the script is named <frevvo-home>/data/sql/users-mysql5.sql
  4. Run that script in the "users" database
  5. Set the userDS data source username and password parameter to a user that has write permissions to your "users" database.

...

Info

See the certified database list above for all the databases officially supported by frevvo.

...

supported by frevvo.

  •  org.hibernate.dialect.Oracle9DialectHSQLDialect
  •  org.hibernate.dialect.OracleDialectMySQL5Dialect
  •  org.hibernate.dialect.PostgreSQLDialectMySQL5InnoDBDialect
  •  org.hibernate.dialect.SQLServerDialectOracle9Dialect
  •  org.hibernate.dialect.Sybase11Dialect

UTF-8 Encoded Database

Warning

The forms and users databases must be created with UTF-8 encoding.

If you're using a improperly encoded database for frevvoDS or usersDS you may run into one or more of the following runtime issues:

  1. International characters will not work correctly without the proper encoding.
  2. Workflows directed to a specific user may not appear on the user's task list.

Setting the encoding depends upon the database you are using. Please consult your database documentation.

MySQL UTF-8 Example

These commands will create the proper

Frevvoproduct
 and users utf8 encoded database in mySql:

Code Block
languagesql
create database if not exists frevvo default character set "utf8" default collate "utf8_unicode_ci";
create database if not exists users default character set "utf8" default collate "utf8_unicode_ci";

You can convert a database and/or table to UTF-8. Using the mySQL configuration file or via the mySQL cmd tool.

Via the configuration file:

Code Block
[mysqld]
default-character-set=utf8
default-collation=utf8_unicode_cii

Via the mySQL cmd tool:

Code Block
ALTER DATABASE frevvo CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER DATABASE users CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `frevvo16089`.`formsubmission` CHARACTER SET utf8 COLLATE utf8_unicode_ci; 

Run the SQL Scripts

What does it mean to run the SQL scripts? The *.sql file is a text file with SQL statements to be executed by the SQL server. Here is one way to run an SQL script for a mySql database to create the tables needed by

Frevvoproduct
 on a windows box.

  1. Open a Command Prompt window 
  2. cd to <frevvo installdir>\frevvo\data\sql
  3. execute the command: mysql <my_db_name> -u <user_name> -p < forms-mysql.sql

This will prompt you for the mysql password for <user_name> and then execute the script creating all the required tables.

Install a JDBC Driver

Frevvoproduct
's submission and user databases rely on the JDBC API. You can use any relational database as long as you have a JDBC driver for that database, however. See the list of certified databases. If you choose a database that's not in this list you do so at your own risk.

Warning

You MUST use the JDBC4 type driver.

The

Frevvoproduct
 download package comes with several pre-installed drivers. Look in <frevvo-home>/tomcat/lib and you will see (among other jar files) the following pre-installed driver jar files:

  • hsqldb-2.2.4.jar - HSQLDB driver
  • postgresql-9.0-801.jdbc4.jar - Postgress driver

If you don't find what you need pre-installed you will need to locate a driver compatible to your database. Usually you can download your driver from the internet. Try one of these locations

Once you have the driver you need, copy it to <frevvo-home>/tomcat/lib.

The <frevvo-home>\tomcat\logs\frevvo.log file will report an error if you do not copy the JDBC driver to lib directory. The verbiage may differ depending on the driver and database. For example, the error for MySQL is "Cannot load JDBC driver class 'com.mysql.jdbc.Driver". Starting

Frevvoproduct
without the correct jdbc driver displays an HTTP 404 error. Coying the correct jdbc driver to the <frevvo-home>/tomcat/lib directory should resolve the issue.

Image Removed

It is also appropriate to copy the driver into any location that is in the CLASSPATH of your servlet container. In a tomcat installation another location would be <CATALINA_HOME>/lib.

Previous Pre-Installed Driver Versions

Previous versions of frevvo include these drivers:

  • hsqldb-1.8.0.7.jar - HSQLDB jdbc driver
  • com.microsoft.sqlserver.jdbc-1.1.3.jar - SQL Server driver
  • jtds-1.2.5.jar - SQL Server driver
  • org.objectweb.rmijdbc-1.0.0.jar - Access driver (also can be used for SQL Server)

If you don't find what you need pre-installed you will need to locate a driver compatible to your database. Usually you can download your driver from the internet. Try one of these locations

  • Oracle 
  • MySQL
  • DB2
  • RmiJDBC.OracleDialect
  •  org.hibernate.dialect.PostgreSQLDialect
  •  org.hibernate.dialect.SQLServerDialect
  •  org.hibernate.dialect.Sybase11Dialect

UTF-8 Encoded Database

Warning

The forms and users databases must be created with UTF-8 encoding.

If you're using a improperly encoded database for frevvoDS or usersDS you may run into one or more of the following runtime issues:

  1. International characters will not work correctly without the proper encoding.
  2. Workflows directed to a specific user may not appear on the user's task list.

Setting the encoding depends upon the database you are using. Please consult your database documentation.

MySQL UTF-8 Example

These commands will create the proper

Frevvoproduct
 and users utf8 encoded database in mySql:

Code Block
languagesql
create database if not exists frevvo default character set "utf8" default collate "utf8_unicode_ci";
create database if not exists users default character set "utf8" default collate "utf8_unicode_ci";

You can convert a database and/or table to UTF-8. Using the mySQL configuration file or via the mySQL cmd tool.

Via the configuration file:

Code Block
[mysqld]
default-character-set=utf8
default-collation=utf8_unicode_cii

Via the mySQL cmd tool:

Code Block
ALTER DATABASE frevvo CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER DATABASE users CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `frevvo16089`.`formsubmission` CHARACTER SET utf8 COLLATE utf8_unicode_ci; 

Run the SQL Scripts

What does it mean to run the SQL scripts? The *.sql file is a text file with SQL statements to be executed by the SQL server. Here is one way to run an SQL script for a mySql database to create the tables needed by

Frevvoproduct
 on a windows box.

  1. Open a Command Prompt window 
  2. cd to <frevvo installdir>\frevvo\data\sql
  3. execute the command: mysql <my_db_name> -u <user_name> -p < forms-mysql.sql

This will prompt you for the mysql password for <user_name> and then execute the script creating all the required tables.

Install a JDBC Driver

Frevvoproduct
's submission and user databases rely on the JDBC API. You can use any relational database as long as you have a JDBC driver for that database, however. See the list of certified databases. If you choose a database that's not in this list you do so at your own risk.

Warning

You MUST use the JDBC4 type driver.

The

Frevvoproduct
 download package comes with several pre-installed drivers. Look in <frevvo-home>/tomcat/lib and you will see (among other jar files) the following pre-installed driver jar files:

  • hsqldb-2.2.4.jar - HSQLDB driver
  • postgresql-9.0-801.jdbc4.jar - Postgress driver

If you don't find what you need pre-installed you will need to locate a driver compatible to your database. Usually you can download your driver from the internet. Try one of these locations

Once you have the driver you need, copy it to <frevvo-home>/tomcat/lib.

The <frevvo-home>\tomcat\logs\frevvo.log file will report an error if you do not copy the JDBC driver to lib directory. The verbiage may differ depending on the driver and database. For example, the error for MySQL is "Cannot load JDBC driver class 'com.mysql.jdbc.Driver". Starting

Frevvoproduct
without the correct jdbc driver displays an HTTP 404 error. Coying the correct jdbc driver to the <frevvo-home>/tomcat/lib directory should resolve the issue.

Image Added

It is also appropriate to copy the driver into any location that is in the CLASSPATH of your servlet container. In a tomcat installation another location would be <CATALINA_HOME>/lib.

Changing Submission database naming and escaping conventions

...