Setup_for_SQL_Server

• Rational
• Installation
  • Database Setup
  • Application Server Setup
  • Creating the Contract Table
  • Verify Data is Stored

Rational

IBD can store part of the information captured in the form in a relational database. Out of the box, IBD:

• Stores the contract information (under /instance/Contract in the main instance).
• Works with SQL Server. (The instructions below are for SQL Server, but this should work with other relational databases for which you have a JDBC driver).
• Stores each "contract" as a row of the contract table.

Installation

Database Setup

Install SQL Server and create a database with a name of your choice. What follows assumes that the database name is ibd. You don't need to explicitly create a table in the database; this will be done later by the application (see below).

Application Server Setup

First, setup a data source in your application server (or servlet container). With Tomcat, edit the conf/server.xml and add the following resource inside the <Context> element you setup for the IBD application:

<Resource name="jdbc/ibd" auth="Container" type="javax.sql.DataSource"
      initialSize="3" maxActive="10" maxIdle="20" maxWait="30000"
      driverClassName="com.microsoft.jdbc.sqlserver.SQLServerDriver"
      poolPreparedStatements="true"
      username="sa"
      password=""
      url="jdbc:microsoft:sqlserver://192.168.0.201:1433;DatabaseName=ibd;SelectMethod=Cursor"/>

Make sure to change the user name, password, IP address (or host name), and database name as appropriate. The name of the resource (jdbc/ibd) must be left unchanged, as the data source is referenced using this name in the code.

Second, install the SQL Server JDBC driver. Download the SQL Server JDBC driver, which is a file named mssqlserver.jar. Place this file in the appropriate directory so the driver is available to your application server. For Tomcat, copy mssqlserver.jar to common/lib.

After doing those changes, you need to restart Tomcat.

Creating the Contract Table

To create the contract table, make sure Tomcat is started, and go to the following URL: http://localhost:8080/orbeon/fr/service/custom/iadb/create-table?form=standard-rfp

This will return a web page with the DDL you need to run to create the standard_rfp table in the ibd database. You are responsible for running this DDL as appropriate against the idb database. Replace on the URI standard-rfp by the name of another form to get the DDL of the table corresponding to that form (e.g. form=goods). Note that if the form name contains dashes, they will be replaced by underscores in the table name.

Verify Data is Stored

To check that saving to SQL Server works as expected:

• Start with a new form.
• Under Requested Consulting Services select Conustant's Services Time-Based Contract.
• Under Contract, Applicable law and language, choose No for Applicable law is the same as the Client's Country, then enter some text in the field Law that will govern the contract. Then hit the Save to database button:

[Image:Save-to-datebase.png]

• This field you modified is bound to Contract/GovernmentLaw/Country in the instance. This element will be automatically mapped to the column GovernmentLaw_Country. Check that the table contains the text you entered in the previous step by running select GovernmentLaw_Country from contract. You should now see the value you entered:

[Image:Country-column.png]