genquery.pl: A Perl html forms interface for SQL databases

Summary

This package has various functions for generating html code from an SQL table structure and for generating SQL statements (Select, Insert, Update, Delete) from an html form. You can easily build a complete forms interface to an SQL database (query, add, update, delete) without any programming! The software is available at http://www.odbsoft.com/cook/sources.htm

Functionality

• Automatic generation of input and query forms and submit buttons for an SQL table by reading the table structure.
• Automatic processing of the submit button (i.e. generation and submission of appropriate SQL statements from the form variables and user input).
• 4 types of form input variables: a CHAR(1) column produces a check box (value 'Y' or blank); any other CHAR field produces either a standard text field or a drop-down list (see below); a TEXT column produces a textarea field. These are the only column types supported at the moment, aside from the integer autonumbering ID column. TEXT columns can contain 64k data.
• 5 types of forms: query.pl generates a SELECT statement from user input and produces a one-line-per-record list. Each record on this list has an href link to the full record display, produced by view.pl. add.pl creates a new record, update.pl updates and delete.pl deletes .
• Requires an auto-numbering primary key column on every table called ID, and a security key for verifying user authority to update/delete the record called userkey.
• Users are required to enter a private security key when entering records which is verified when updating or deleting. On a secured system (login required) this key is the http userid read from the http header/environment variable so no user input is required while still protecting individual records.
• Possibility to write custom handlers for processing and initializing of fields, i.e. drop down lists. See GenForm() in genquery.pl
• Possibility to have a help text display next to form input columns, see GenForm() in genquery.pl.
• Possibility to automatically produce record creation and modification timestamps by including columns named Date_Created and Date_Updated in the table.
• Runs on Unix with DBI:mysql and on Win32 with DBI:ODBC. Prototype your application on Win32, install on Unix. Read the FAQ about this.

Function details

All functions are very simply called by submitting a link with the tablename in the submission parameters. The 'table' parameter is required on all forms, the 'id' parameter is required on update and delete, all other parameters are optional.

query.pl?table=tablename&fields=col1,col2,...&orderby=col1,col2...&debug=1

Produces a query form and submit button. The fields optional parameter is the fields to include on the form; the default is to include all non TEXT (i.e. long) columns. The orderby parameter orders the result set. The debug parameter displays the generated SQL statement(s).

view.pl?table=tablename&id=xxx

Produces a full-record printout with all columns.

add.pl?table=tablename&debug=1

Produces a data-entry form and submit button.

update.pl?table=tablename&id=xxx&debug=1

Produces a filled in data-entry form and allows user to modify record after verifying security key.

delete.pl?table=tablename&id=xxx&debug=1

Deletes the record after verifying the security key.

Using MySQL

On Unix mysql tablenames are case sensitive, but not column names. Create your tables and columns with capitalization to maximize readability, since these are the names that will be displayed on the forms.

Generating a table within an html document with a server-side include (SSI)

Rename your html file with a .shtml extension and add the following command:

<!--#exec cmd="perl query2.pl table=Resources fields=ID,Resource_Type " -->

Give your file execute access and put it in your cgi-bin directory.

Writing and testing your application

To edit the source code, use an editor that preserves tabs (for instance. Microsoft Developer Studio).

For security reasons SQL error information is not returned to the web user. Test your application from the command line by creating a data file with the names/values of submission parameters on each line ("field=value") and then issuing the command: perl query.pl <t.dat; or else enter parameters when prompted and end with ctrl-Z (ctrl-D on Unix). Remember to tell genquery.pl whether you are using a local (ODBC) data-source or a remote one, see dbOpen() function.

Consult List of known bugs.

Author

Send feedback, appreciation, enhancements, and business propositions to marc@odbsoft.com

This software is freely modifiable and distributable, but please keep the author/contact information on the source header.