Publish - ODBC in UNIX

Instructions:

1. Pubish a web form and select the option Save to a database with ODBC on the Web form setup window.
2. Select the Setup button next to the ODBC option.
3. The Advanced web form options window under ODBC are as follows:
   • Service name (on the server) - The service name is the DSN entry found in the ODBC Data Source Administrator. Or, the DSN-Less connection string.
   • Login account (on the server) - This is the login name used for authorization. Depending on your database program, it will either be setup in your DSN entry or in your database program.
   • Password - This is the password used for authorization. Depending on your database program, it will either be setup in your DSN entry or in your database program.
   • Table - Your database will need to reference a table, you must specify the table name here.
   • Database type - If you the database type is listed select it from the list, otherwise leave blank.
   • Autoincriment field - If you use an SQL function to increment the primary key, please enter it here. To use a triggered auto increment field (MS, Watcom, Sybase), go to the key field's advanced options in the Edit Form Window and select 'This is an auto increment field'.
   • Generate SQL to initiate the table? - Checking this will generate an SQL string on the instructions page. This string may need to be put into your database program to generate the table.
4. These entries will be made in the projectname.ini file:
   
   SAVEODBC=1
   ODBCSERVICE=DSN name or DSN-Less connection string
   ODBCNAME=user name
   ODBCPASSWORD=user password
   ODBCTABLE=table name
   ODBC.KEY=auto
   Note: You can trace SQL statements by putting a question mark (?) before the ODBCSERVICE name. The log will be in the file logerr.txt:
   ODBCSERVICE=?dsn_name
5. Finish publishing the web form.
6. Once the web form is published, an instruction window will open. The instruction window will have an ODBC Database section on it. Follow the instructions.
7. You must compile the web CGI with the ODBC option turned on. Run the setup and also run the script makeodbc to create the ezs.cgi with ODBC.
8. Continue to follow the publishing instructions to find the link to the start page of the survey.

Setup the Driver

In the /etc/odbcinst.ini file, make a driver reference like this:

	[MySQL]
	Description	= MySQL driver
	Driver		= /usr/lib/odbc/libmyodbc.so
	Setup		= /usr/lib/odbc/libodbcmyS.so
	FileUsage	= 1
	

Check with unixODBC (off site link) for more information about setting up database drivers.

DSN-Less ODBC Connection to MySQL

The publishing settings in EZSurvey are:

Service name:	DRIVER={MySQL};SERVER=localhost;DATABASE=test;UID=raosoft;PWD=******;
Login account:	raosoft
Password:	******
Table:		projectname
Database type:	MySQL
Generate SQL:	Yes

Publish the survey and uploaded all of the generated files to the web server.

Log into the web server with ssh and run these commands in the survey directory:

chmod 600 *.asc *.ndx *.xml
chmod a+r *.html *.ini
touch random.seed
chmod a+w random.seed
chmod +x setup
./setup
chmod 4701 ezs.cgi

Log into the MySQL database with phpMyAdmin and select the database 'test'. Execute the SQL statement to create the new table in the 'test' database.

At this point, the online EZSurvey form will submit. Since it is a DSN-Less connection, there will be no user DSN problems.

Common Problems

In some cases when using unixODBC, you might get this error:

Data source name not found, and no default driver specified

If this happens, make sure the ODBCINI and ODBCSYSINI environment variables are pointing to the right odbc.ini file. For example, if your odbc.ini file is located in /etc, set the environment variables like this:

export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc

In Apache you can set the environment variables in the httpd.conf file like this:

SetEnv ODBCINI /etc/odbc.ini
SetEnv ODBCSYSINI /etc

You must also load mod_env to use SetEnv in Apache.

See also...

• Publish - Publish to UNIX