On this page:

Configuration Steps

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

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 stops. Then Double click <frevvo-home>\start-frevvo

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

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

...

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

...

...

...

...

...

...

Define the Data Source

The frevvo database The  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 | configuration file location]] then  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 [[#Starting_and_Testing_the_Connector | 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.

...

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

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:

...

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]. If your SQL server was installed this way you must use the instance parameter. For example if you database instance was named xyzzy:

...

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