Database File Formats

EZSurvey uses a DBF database to store your data. DBF is a fixed-length format. This means that each question has a limit for the number of characters that can be stored in each answer. EZSurvey can also use delimited ASCII files and SQL databases (In EZSPro through ODBC).

DBF (xBase) Files

You should make the maximum length of write-in questions less than 250 characters for compatibility with the dBase standard. If you want, you can make response lengths up to 16,000 characters, but you will lose the ability to open the database in other programs. Clipper and FoxBase use the same format for long fields as EZSurvey does.

Multiple choice questions (single/multiple response, single/multiple list) store only the response codes in the database, separated by commas. For example, if the question was:

What fruit do you like? 1=Apples 2=Bananas 3=Oranges

and the answer was Apples and Oranges, the database entry would look like this:

1, 3

This saves disk space and makes analysis calculations much faster. It also means that you can change the text associated with a response whenever you want -- as far as the database knows, only the codes (which you don't see) have to match.

If you are sharing a project with Raosoft SurveyWin or EZReport, the same data file will be used for projects with the same name.

ASCII Files

An exported tab-deliminated file (ASC) will look like this:

name phone color dept comment Alice 555-2342 R,G 1 Corner office Bob 555-6236 B,G 3 She said, "Hi"

There are no extraneous delimiters in a tab-delimited file.

An exported comma-delimited file (CSV) will look like this:

name,phone,color,dept,comment Alice,555-2342, "R,G",1,Corner office Bob,555-6236, "B,G", 3, "She said, ""Hi"""

Quotation marks are added around any field containing commas or quotation marks.

When EZSurvey imports a database, any line (other than the first line) beginning with a # character will be ignored. This is done for compatibility with SuperMongo.

SQL Databases (through ODBC)

EZSurvey Pro has been designed to use databases from other software vendors, using the Open Database Connectivity Protocol. To use EZSurvey Pro, you must have ODBC installed on your computer. Since all database programs are idiosyncratic, EZSurvey has been optimized to work specifically with Oracle, Sybase, and Microsoft SQL Server.

EZSurvey generates a flat table inside an ODBC database to store data for a project. The table has a primary key field, called "ID". Where possible, EZSurvey will automatically generate a key, using stored procedures, sequences, or auto-increment fields. Otherwise, it generates a long pseudo-random number for each record to use as the primary key value. EZSurvey does not use CLOB objects, so the maximum field length that you can use is usually 255 characters. After EZSurvey creates a table to match a form, then if your database supports long data, you may change the table definition to increase the length of comment responses.

Web data saved in an ASCII database is stored on the server. When it is downloaded from the server, EZSurvey checks the data for errors and reformats the date, time, and number fields before submitting the data to a SQL database.

Specifics for certain databases are listed below. If the database you use is not on the list, call Raosoft, Inc. at (206) 525-4025 for more information.

Oracle

Resources created: TABLE tablename, SEQUENCE seq_tablename
Table command: CREATE TABLE tablename (ID VARCHAR(10) CONSTRAINT pk_id PRIMARY KEY, ...)
Sequence command: CREATE SEQUENCE seq tablename minvalue 1 increment by 1 nomaxvalue
Insert command: INSERT INTO tablename (ID,...) VALUES(seq_tablename.nextval,....)
Data type: VARCHAR
Max field length: 255

Sybase SQL Anywhere

Resources created: TABLE tablename
Table command: CREATE TABLE tablename (ID INTEGER AUTOINCREMENT PRIMARY KEY, ...)
Insert command: INSERT INTO tablename (ID,...) VALUES('', ...)
Data type: VARCHAR
Max field length: 255

Microsoft SQL Server BackOffice

Resources created: TABLE tablename
Table command: CREATE TABLE tablename (ID INT IDENTITY(1,1) PRIMARY KEY, ....)
Insert command: INSERT INTO tablename (fields...) VALUES(values....)
Data type: VARCHAR
Max field length: 255

Microsoft Access

Resources created: TABLE tablename
Table command: CREATE TABLE tablename (ID VARCHAR(24) PRIMARY KEY, ...)
Insert command: INSERT INTO tablename (ID, ...) VALUES(pseudorandom value,...)
Data type: VARCHAR
Max field length: 255