Database specific information

Oracle 8, 9, 10, 11

Specifying the database driver

Depending on the version of Oracle that is in use, add the appropriate database= servlet parameter:

With Oracle 8, use database=Oracle
With this parameter, binary data is stored in VWFOBJECTS as LONG RAW. Text fields are stored as VARCHAR(2000).

With Oracle 9, 10, or 11, use database=Oracle9, database=Oracle10, or database=Oracle10. These are identical, but named differently for possible future changes.
With this parameter, binary data is stored in VWFOBJECTS as a BLOB. Text fields are stored as VARCHAR(2000).

With Oracle 9, 10, or 11, database=Oracle9Blob can be used if having trouble with one of the above.
With this parameter, binary data is also stored in VWFOBJECTS as a BLOB, but the driver uses Blob methods explicitly.
When using database=Oracle9Blob, the following parameter is required:
databaseextension=Oracle9DatabaseExtension

Storing very long text fields
Some questionnaires require saving very large text fields. Text fields are normally limited to 2000 or 4000 characters, depending on the Oracle version. With Oracle 9 and above, it is possible to store larger fields: select "Save in Normalized database" in the questionnaire's Save page. The data will be saved in the Normalized table VWFDATA using a CLOB field, and it can be retrieved using the Analysis / Data option. To use this option with Oracle 9 or higher, use the following servlet parameter:
databaseextension=Oracle9DatabaseExtension

Storing multi-byte Unicode data such as Japanese, Chinese, Arabico
Oracle must be configured to store data in UTF-8 encoding. Use this servlet parameter:
dbcharwidthmultiplier=3
The maximum width of fields will be reduced by a factor of 3. In Oracle 8 this is VARCHAR(666) and VARCHAR (1332) in higher versions. This does not affect data stored in CLOBs in the Normalized table VWFDATA.

Upgrading from Oracle 8 to 9,10,or 11
Change the database= parameter to the appropriate driver, as described above, and restart the ViewsFlash application.
If you use the databaseextension=Oracle9DatabaseExtension parameter described above, the VWFDATA table will be altered automatically when switching to this driver, and data is moved from the old VARCHAR field to the new CLOB field as it is accessed.
If the user associated with the datasource does not have table creation and alter rights, then stop the ViewsFlash application and manually alter the VWFDATA table with:
ALTER TABLE VWFDATA ADD ( ANSWERCLOB CLOB )
and restart the ViewsFlash application.

Upgrading from Oracle 9 to 10 to 11
Change the database= parameter to the appropriate driver, as described above, and restart the ViewsFlash application. Nothing will change in the tables themselves.

Application server notes

To configure Tomcat 5.5, see JNDI Resources HOW-TO.
To configure Tomcat 6.0, see JNDI Resources HOW-TO.
In both, use the following ViewsFlash servlet parameter, if the JNDI data source is configured as MyDB:
datasource=java:comp/env/jdbc/MyDB

If the application server does not include a JDBC driver for Oracle, download the JDBC driver that most closely matches the version of Oracle in use.

Using the Oracle JDBC driver directly
In Oracle only, it is possible to connect to the database without using a datasource.
This method can be used with Oracle 8, 9, or 10. Binary data is stored as LONG RAW and text fields as VARCHAR(2000). Normalized data is stored as a VARCHAR.

Use the following servlet parameters:

dbdatabaseconnectionclass=OraclePooledDatasource
dbstores=data|polls|results|styles|votes
dbdrivertype=thin
dbdatasource=yourdatabaseserver.yourcompany.com
dbdatabasename=yourdatabasename
dbprotocol=tcp
dbport=1521
dbusername=yourviewsflashusername
dbpassword=yourviewsflashuserpassword

MSSQL Server 7, MSSQL 2000, MSSQL 2005

Specifying the database driver

The same database= servlet parameter is used for all these versions of MSSQL:
database=MSSQL
With this parameter, binary data is stored in VWFOBJECTS as an IMAGE field. Text fields are stored as NVARCHAR(2000).

Storing very long text fields
Some questionnaires require saving very large text fields. Text fields are normally limited to 2000 characters. To store larger fields, select "Save in Normalized database" in the questionnaire's Save page. The data will be saved in the Normalized table VWFDATA, and it can be retrieved using the Analysis / Data option.

ViewsFlash releases prior to 5.8 used VARCHAR(2000) for the text field in the Normalized table. In release 5.8, this has been changed to NVARCHAR(2000) for MSSQL Server 7 and MSSQL Server 2000, and to NVARCHAR(max) in MSSQL 2005. Thus, in ViewsFlash 5.8 with MSSQL 2005, it is possible to save practically unlimited text in the Normalized database.

Storing multi-byte Unicode data such as Japanese, Chinese, Arabic
ViewsFlash releases prior to 5.8 used VARCHAR(2000) for text fields. 5.8 and above use NVARCHAR(2000), allowing storage of Unicode data such as Japanese, Chinese, Arabic. There is no need to modify any tables when upgrading unless storing Unicode data is contemplated in the future; in that case, use an ALTER TABLE to change the desired fields from VARCHAR to NVARCHAR. For the Normalized database table VWFDATA, stop the ViewsFlash application and manually alter the VWFDATA table with:
ALTER TABLE VWFDATA ALTER COLUMN ANSWER NVARCHAR(max)
and restart the ViewsFlash application.

Upgrading from MSSQL 7 or MSSQL 2000 to MSSQL 2005
In MSSQL 2005, it is possible to store practically unlimited large blocks of text in the Normalized database in VWFDATA.
If upgrading to MSSQL 2005 from an earlier version, stop the ViewsFlash application and manually alter the VWFDATA table with:
ALTER TABLE VWFDATA ALTER COLUMN ANSWER NVARCHAR(max)
and restart the ViewsFlash application.

Application server notes

When using Adobe JRun, go to the JRun Management Console and, in the default server, under Resources, create a JDBC Data Source linking to the MSSQL server. Then provide ViewsFlash with its JNDI name (usually MSSQL) in its servlet parameters.

If the application server does not include a JDBC driver for MSSQL, download the Microsoft JDBC driver that most closely matches the version of MSSQL in use.

MySQL 4,5

Specifying the database driver

The same database= servlet parameter is used for all these versions of MySQL:
database=MySQL
With this parameter, binary data is stored in VWFOBJECTS as a MEDIUMBLOB field. Text fields are stored as VARCHAR(4000) in MySQL 5 and VARCHAR(255) in MySQL 4.

Storing very long text fields
Some questionnaires require saving very large text fields. Text fields are normally limited to 2000 characters. To store larger fields, select "Save in Normalized database" in the questionnaire's Save page. The data will be saved in the Normalized table VWFDATA as a TEXT field, of practically unlimited size, and it can be retrieved using the Analysis / Data option.

Storing multi-byte Unicode data such as Japanese, Chinese, Arabic
MySQL automatically stores Unicode in the server's default character set; we recommend using "utf8".

Upgrading from MySQL 3 to 4 to 5
Nothing needs to change.

Application server notes

When using Adobe JRun, go to the JRun Management Console and, in the default server, under Resources, create a JDBC Data Source linking to the MySQL server. Then provide ViewsFlash with the Data Source's JNDI name in its datasource= servlet parameter.

If the application server does not include a JDBC driver for MySQL, download the MySQL JDBC driver that most closely matches the version of MySQL in use.

DB2 release 8 and 9

The DB2 database tablespace assigned to ViewsFlash should include a Table Space for Regular data set up with a large page size, such as 32K. This is needed to store tables from large surveys with many questions on their own tables, which often require a large page size.

Specifying the database driver

The same database= servlet parameter is used for all these versions of DB2:
database=DB2
With this parameter, binary data is stored in VWFOBJECTS as a BLOB field. Text fields are stored as VARCHAR(2000).

Storing very long text fields
Some questionnaires require saving very large text fields. Text fields are normally limited to 2000 characters. To store larger fields, select "Save in Normalized database" in the questionnaire's Save page. The data will be saved in the Normalized table VWFDATA as a CLOB field, of practically unlimited size, and it can be retrieved using the Analysis / Data option.

Storing multi-byte Unicode data such as Japanese, Chinese, Arabic
DB2 should be configured to use UTF-8. Use this servlet parameter:
dbcharwidthmultiplier=3
With this parameter, the maximum size of text fields will be 667. This does not affect data stored in the Normalized table VWFDATA.

Upgrading from DB2 release 8 to 9, or 9 to 10
No action required.

Application server notes
In WebSphere application server, if the JNDI data source is named MyDB, use datasource=MyDB as the ViewsFlash servlet parameter.

Using schemas and tablespaces
To use tables inside a specific schema, see the dbtablenameprefix servlet parameter, eg dbtablenameprefix=COGIX_SCHEMA. (with a trailing period). All tables will use two-part names, eg COGIX_SCHEMA.VWFOBJECTS.

See the dbcreatetablespacestatement parameter to specify a CREATE TABLESPACE statement.

See the dbtablenamesuffix parameter to allocate database tables associated with specific questionnaires to a named or random tablespace.

Next: Using other Data Sources