Database specific information |
For Oracle, MSSQL, MySQL, DB2, PostgreSQL.
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, database=Oracle11, or database=Oracle12. 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, 11 or 12, 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 ONLY, the following parameter is required:
databaseextension=Oracle9DatabaseExtension
High performance Oracle driver
Oracle 11g provides high performance connection caching and pooling with its oracle.jdbc.pool.OracleDataSource drivers. This driver bypasses the application server completely, so it does not use a JNDI datasource. Instead of the datasource servlet properties, provide all the JDBC connectivity info using these servlet properties:
dbdatabaseconnectionclass=com.cogix.vwf.OraclePooledDataSource dbservername=my.oracledatabase.com dbdatabasename=xe dbusername=scott dbpassword=tiger database=Oracle9 # or Oracle10 or Oracle11 or Oracle12 databaseextension=Oracle9DatabaseExtension # or Oracle10DatabaseExtension or Oracle11DatabaseExtension or Oracle12DatabaseExtension
Migrating from LONG RAW to BLOBs
Very old versions of ViewsFlash used a LONG RAW field in VWFOBJECTS instead of a BLOB.
To update VWFOBJECTS manually, use the following DDL statements. Make sure that all ViewsFlash instances in the cluster
are stopped first.
CREATE TABLE VWFOBJECTS2 (ObjectKey NOT NULL PRIMARY KEY, Kind, Age, Objdata ) AS SELECT ObjectKey, Kind, Age, TO_LOB(Objdata) from VWFOBJECTS
RENAME VWFOBJECTS TO VWFOBJECTS_BACKUP
DROP INDEX KINDINDEX
RENAME VWFOBJECTS2 AS VWFOBJECTS
CREATE INDEX KINDINDEX ON VWFOBJECTS (Kind)
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, 11 or 12
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, 11 or 12
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
In all versions of Tomcat, use the following ViewsFlash servlet parameter, if the JNDI data source is named jdbc/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.
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
In all versions of Tomcat, use the following ViewsFlash servlet parameter, if the JNDI data source is named jdbc/MyDB:
datasource=java:comp/env/jdbc/MyDB
If the application server does not include a JDBC driver for MSSQL,
download the JDBC driver that most closely matches the version of MSSQL in use.
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. Please check the documentation for your version of MySql carefully, including using the proper the JDBC connection string, and using utf8mb4 instead of utf8.
Application server notes
In all versions of Tomcat, use the following ViewsFlash servlet parameter, if the JNDI data source is named jdbc/MyDB:
datasource=java:comp/env/jdbc/MyDB
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.
Specifying the database driver
The same database= servlet parameter is used for all these versions of Postgres:
database=Postgresql
With this parameter, binary data is stored in VWFOBJECTS as a BYTEA 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 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
Postgres automatically stores Unicode in the server's default character set. Please check the documentation for your version of Postgres carefully, including using the UTF8 character set.
Application server notes
In all versions of Tomcat, use the following ViewsFlash servlet parameter, if the JNDI data source is named jdbc/MyDB:
datasource=java:comp/env/jdbc/MyDB
If the application server does not include a JDBC driver for Postgres, download the Postgres JDBC driver that most closely matches the version of Postgres in use.
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.
If the application server does not include a JDBC driver for DB2, download the DB2 JDBC driver that most closely matches the version of DB2 in use.
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.