Internet Forms and Database

1
Internet Forms and Database

• Bob Kisel
• Amgraf, Inc.
• Bob_at_amgraf.com

2
Internet Forms and Database

• Types of Databases Available for Internet Forms
• The Basics of Iform Database Creation
• The Basics of Iform Database Interaction

3
Types of Databases Available

• MySQL Open Source Database (either Unix or Win32
  versions)
• Oracle (Unix or Win32 also)
• SQL Server 2000
• Access 2000 (Possible but I would not recommend
  it.)

4
Access Methods

• We provide and recommend Perl DBI CGI scripting
  to interface to the database manager used.
• Native interface code can written that is invoked
  by the script interface.

5
Database Operations

• Submit Form Data to Database
• Load Reference Data Into Select Boxes
• Load Previous Form Data
• Load One or More Fields Based on Data Input
• Save Form Data Without Doing Submit

6
Submit Form Data

• All Non-Disabled Input Fields are passed to the
  CGI script. (Be careful of fields disabled based
  on other criteria, if they need to go with
  submitted data, enable in beforesubmit function.)
• All Input Fields are Subscripted, make certain
  script allows for this.

7
Load Reference Data

• Typically to Load Data into Select Lists
• Setup Criteria
• Invoke _initSelection JavaScript Function
• Arguments (Script Path, Criteria, Display_Mode,
  Select List Object)

8
Load Reference Data

• Script Path Relative or Absolute Path to Server
  Script on Same Server Where Page was Served From.
• Typically we Like to Put Them All in a Query
  Directory Since They May be Useful to Other HTML
  Pages and also so that if one is Similar to
  Another, we Copy and Edit it.

9
Load Reference Data

• Criteria is a String of KeywordValue Pairs
  Separated by the Character.
• ALL Values should passed to JavaScript escape
  function singly if there is any chance of
  containing Non-alphanumeric characters.

10
Load Reference Data

• Server Script Looks for Given Keywords
• Builds the Actual SQL Command
• Formats the Return Data as
• IndexValue
• JavaScript then Loads Data into Select List in
  Order Returned.
• Note Index is a Unique Value for List. It may be
  a Number or Key from Database.

11
Load Reference Data

• Display Mode is
• 1 Leave a blank line at top of Select List
• 0 No Blank Line at Top of List
• -1 If Only One Line Returned, No Blank, else
  Add a Blank Line to Top

12
Load Previous Form Data

• Typically a Server Script is Required to
  Dynamically Modify the HTML Data for the Page.
• This is Done by Inserting Code into the
  initialize function of the HTML Page.
• This code will set the value of one or more
  fields.

13
Load Previous Form Data

• In the case of most forms the values of all the
  fields are set in the initialize function.
• In other cases only a Key value is set and the
  code in the initialize function then initializes
  all of the dependent fields based on this.
• You need to pick the method that works best for
  your system design.

14
Load Dependent Fields

• Allows you to load one or more fields based on a
  criteria string passed to a server CGI script.
• The formulation of criteria identical to loading
  reference data.
• _InitDependentFields Function
• (Script Path, Criteria, Object Array, Key Array,
  Object Count)

15
Load Dependent Fields

• Object Array and Key Array are sequenced the
  same.
• Object array is pointers to input fields to be
  filled in.
• Key array are strings that are matched to the
  script return data keys to identify which field
  gets which value.

16
Load Dependent Fields

• This is easy to setup in the JavaScript code,
  just difficult to type all the values in without
  mistake. (Not bad if only setting up to about 5
  fields, bad if it is the entire form.)
• If you setup up your form to use an Input
  database, code is generated to load the field
  arrays for each table, but you must insert code
  to invoke _InitDependentFields function.

17
Load Dependent Fields

• This prevents problems with typing while allowing
  you to control precisely when the server script
  is called to load the data.
• Typically this is done in initialize function but
  may be invoked again if some selection is made to
  reflect a new criteria.
• The goal is to load data only when needed.

18
Save Form Data

• There are cases when you want to be able to save
  the data filled in the form without actually
  leaving the form.
• Case 1 The form needs to be saved multiple
  times, varying 1 or 2 fields and you dont want
  to go into and out of the form multiple times.

19
Save Form Data

• Case 2 You open a sub-form to expand the
  technique for data collection, and the results of
  the sub-form need to be captured. Typically when
  the sub-form is done, it must close its own
  window to return control to original form, not
  via the submit.

20
Some of my Favorite Examples

• MO State Highway Patrol.
• Bank Form Ordering.