Select your database type

1. First, [[#Install_a_JDBC_Driver | Locate Locate and copy the necessary jdbc driver]] for  for your database into <frevvo-home>\tomcat\lib.
2. Edit the file <frevvo-home>\tomcat\conf\Catalina\localhost\frevvo.xml
3. You will see two data source definitions for each database type. '''name="jdbc/frevvoDS"''' is  is the form submissions database. ''' name="jdbc/userDS"''' is the user database. See the above sample of the sample below of the HSQLDB data source in the frevvo.xml file.
4. Comment out the pair of the definitions for the default database (HSQLDB) using the <!-- --> comment characters. The entire section must be commented out:

<!--
  HSQLDB Resource 
<Parameter name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect" override="false"/>         <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"/>
        <Parameter<Resource name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect" override="false"/>
        <Resource auth="auth="Container"
                type="javax.sql.DataSource"
                factory="org.apache.commons.dbcp.BasicDataSourceFactory"
                name="jdbc/userDS"
                driverClassName="org.hsqldb.jdbcDriver"
                username="sa"
                password=""
                url="jdbc:hsqldb:file:${catalina.home}/../data/db/users"                
                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.

 <!--
                SQLSERVER Resource  
   -->
  
         <Parameter <Resource nameauth="hibernate.dialectContainer"
value="org.hibernate.dialect.SQLServerDialect" override="false"/>         <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"/>

...

For your selected database type in frevvo.xml

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

Configure the Live Forms LiveForms users database

For your selected database type in frevvo.xml,

1. Locate the '''userDS ''' data userDS data source url parameter.<nowiki>url="jdbc:jtds:sqlserver://localhost/users"/></nowiki>. You can change the name "users" to whatever you wish, " users" Live Forms users" for example. But it must match the database name you create in your SQL server. See [[#Verify_Connection_Url | verify connection Url]] below below.
2. Create a [[#UTF8_Encoded_Database | utf8 encoded]] database "users" (assuming you left the url parameter database name as the default) in your SQL server
3. In the Live Forms installation  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_the_SQL_Scripts | Run that script]] in  in the "users" database
5. Set the '''userDS''' data userDS data source username and password parameter to a user that has write permissions to your "users" database.

...

Configuration of the connection Url is key to successfully connection connect the Live Forms server  server to your database. Consult your DBA to assist with connection issues.

...

1) verify that the "url" parameter is pointing to the correct server. If Live Forms and  and the database server are collocated "localhost" may be fine.<br/>

The Url parameters depend on the database type and installation choices made when your DBA installed your database software. Consult your DBA for the correct connection values. For example, SQL server installed on a non-default port and running on a machine named e00sca will require that you add a port number and host name to the Url. For example:

...

As of SQL 2000, Microsoft SQL allows installation of multiple [http://msdn.microsoft.com/en-us/library/aa176582%28v=sql.80%29.asp SQL named instances]. 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://e00sca:59377/frevvo;instance=xyzzy</url>

...

If you are trying a different database type and do not see an example in frevvo.xml for  your database, you can create a new data source entry. Hibernate supports the following dialects however Live Forms has  has only been certified to run with a subset of these databases. For additional dialects see Hibernate documentation. [[#Configure_the_database | Database Setup

...

•  org.hibernate.dialect.HSQLDialect
•  org.hibernate.dialect.MySQL5Dialect
•  org.hibernate.dialect.MySQL5InnoDBDialect
•  org.hibernate.dialect.Oracle9Dialect
•  org.hibernate.dialect.OracleDialect
•  org.hibernate.dialect.PostgreSQLDialect
•  org.hibernate.dialect.SQLServerDialect
•  org.hibernate.dialect.Sybase11Dialect

...

These commands will create the proper Live Forms and  and users utf8 encoded database in mySql:

...

Via the configuration file:

[mysqld]
default-character-set=utf8
default-collation=utf8_generalunicode_cicii

Via the mySQL cmd tool:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
--ALTER DATABASE frevvo CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER DATABASE frevvousers CHARACTER SET utf8 COLLATE utf8_generalunicode_ci;
ALTER TABLE formsubmission CONVERT TO`frevvo16089`.`formsubmission` CHARACTER SET utf8 COLLATE utf8_generalunicode_ci; --
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

...

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 ways way to run a an SQL script for a mySql database to create the tables needed by Live Forms on  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-mysql5.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

Live Forms'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. [[#Configure_the_database | See See the list of Live Forms certified databases]]. If you choose a database that's not in this list you do so at your own risk.

The Live Forms download  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:

...

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

• [http://msdn.microsoft.com/en-us/sqlserver/aa937724 SQL Server JDBC Driver]
• [http://www.oracle.com/technetwork/database/features/jdbc/jdbc-112010-090769.html Oracle]
• [https://www-304.ibm.com/support/docview.wss?uid=swg21363866 DB2]
• [http://dev.mysql.com/downloads/connector/j/ Mysql]
• Oracle
• DB2Image Added
• MySQL - 5.1.11 or later is recommended to resolve Tomcat memory leak issues.

Once you have the driver you need, copy it to <frevvo-home>/tomcat/lib.* [http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html Oracle]
* [https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?lang=en_US&source=swg-dm-db2jdbcdriver IBM DB2]
* [http://dev.mysql.com/downloads/connector/j/5.1.html MySQL]
* [http://rmijdbc.objectweb.org RmiJDBC]

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

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
• IBM DB2 Image Added
• MySQL
• RmiJDBC

Changing Submission database naming and escaping conventions

In specific situations, your organization may require a certain naming convention for database objects (all uppercase, or at most 32 chars, or a specific name escaping convention) and the good news is that It is possible to change some of these parameters in Live Forms by  by setting a couple of specific web context parameters.

Note also that the following settings affect only the frevvoDS database schema (and not the userDS schema) and that any changes to these settings will force Live Forms to  to create a schema that is different than the pre-generated scripts found in frevvo/data/sql/forms-*.sql when the schema is initially empty (you have to start from an empty schema).

How can you make sure that all database object names are uppercase or lowercase? You can affect this by  editing the frevvo/tomcat/conf/Catalina/localhost/frevvo.xml and adding the following parameter:

...

This information applies ONLY to the pre-installed demo database HSQLDB. When using the Live Forms tomcat  tomcat bundle installation and the default configuration settings, the forms and users database is written to the <frevvo-home>\data\db directory. You may wish to locate the databases outside of <frevvo-home>. To do this:

1. Edit <frevvo-home>\tomcat\conf\Catalina\localhost\frevvo.xml
2. You will see two data source definitions for each database type. jdbc/frevvoDS is the form submissions database. jdbc/usersDS is for the user database.
3. Configure the HSQLDB jdbc/frevvoDS Resource
4. Change <nowiki>urlurl="jdbc:hsqldb:file:${catalina.home}/../data/db/forms" </nowiki> to an absolute directory e.g. <nowiki>urlurl="jdbc:hsqldb:file:c:\tmp\frevvo-data\forms"</nowiki>
5. Configure the HSQLDB jdbc/userDS Resource
6. Change <nowiki>urlurl="jdbc:hsqldb:file:${catalina.home}/../data/db/users" </nowiki> to an absolute directory e.g. <nowiki>urlurl="jdbc:hsqldb:file:c:\tmp\frevvo-data\users"</nowiki>

...