An Interactive Database Browser Using Param Statements
This application lets the user browse and update data in any table in any database. You should already have used VIFRED to create a default form based on the database table to be browsed. VIFRED builds a form whose fields have the same names and data types as the columns of the database table specified.
The program prompts the user for the name of the database, the table, and the form. In the Get_Form_Data procedure, it uses the formdata statement to find out the name, data type and length of each field on the form. It uses this information to dynamically build the elements for the param versions of the retrieve, append, putform and getform statements. These elements include the param target string, which describes the data to be processed, and the array of variable addresses, which informs the statement where to get or put the data. The type information the formdata statement collects includes the option of making a field nullable. If a field is nullable, the program builds a target string that specifies the use of a null indicator, and it sets the corresponding element of the array of variable addresses to point to a null indicator variable.
After the components of the param clause have been built, the program displays the form. If the user selects the Browse menu item, the program uses a param version of the retrieve statement to obtain the data. For each row, the putform and redisplay statements exhibit this data to the user. A submenu allows the user to get the next row or to stop browsing. When the user selects the Insert menu item, the program uses the param versions of the getform and append statements to add a new row to the database.
/*
** Global declarations
*/
/*
** Target string buffers for use in PARAM clauses of GETFORM,
** PUTFORM, APPEND and RETRIEVE statements. Note that the APPEND
** and PUTFORM statements have the same target string syntax.
** Therefore in this application, because the form used
** corresponds exactly to the database table, these two statements
** can use the same target string, "put_target_list".
*/
## char put_target_list[1000] = {0};
## /* For APPEND and PUTFORM statements */
## char get_target_list[1000] = {0}; /* For GETFORM statement */
## char ret_target_list[1000] = {0}; /* For RETRIEVE statement */
# define maxcols 127 /* DB maximum number of columns */
# define charbufsize 3000 /* Size of "pool" of char strings */
/*
** An array of addresses of program data for use in the PARAM
** clauses. This array will be initialized by the program to point
** to variables and null indicators.
*/
## char *var_addresses[MAXCOLS*2];
/* Addresses of variables and indicators */
/*
** Variables for holding data of type integer, float and
** character string.Note that to economize on memory usage,
** character data is managed as segments on one large array,
** "char_vars". Numeric variables and indicators are managed as an
** array of structures. The addresses of these data areas
** are assigned to the "var_addresses" array, according to
** the type of the field/database column.
*/
char char_vars[CHARBUFSIZE +1]; /* Pool for character data */
struct {
int intv; /* For integer data */
double fltv; /* For floating-point data */
short indv; /* For null indicators */
} vars[MAXCOLS];
/*
** Procedure: main
** Purpose: Start up program and Ingres, prompting user for
** names of form and table. Call Get_Form_Data() to obtain
** profile of form. Then allow user to interactively
** browse the database table and/or append new data.
*/
## main()
## {
## char dbname[25], formname[25], tabname[25];
## int inq_error; /* Catch database and forms errors *
## int num_updates; /* Catch error on database appends */
int want_next; /* Browse flag */
## forms
## prompt ("Database name: ", dbname)
/*
** Use of "-E" flag tells Ingres not to quit on start-up
** errors
*/
## ingres "-E" dbname
## inquire_ingres (inq_error = errorno)
if (inq_error > 0)
{
## message "Could not start Ingres. Exiting."
## exit
## endforms
exit(-1);
}
/* Prompt for table and form names */
## prompt ("Table name: ", tabname)
## range of t is tabname
## inquire_ingres (inq_error = errorno)
if (inq_error > 0)
{
## message "Nonexistent table. Exiting."
## exit
## endforms
exit(-1);
}
## prompt ("Form name: ", formname)
## forminit formname
/* All forms errors are reported through INQUIRE_FRS */
## inquire_frs frs (inq_error = errorno)
if (inq_error > 0)
{
## message "Could not access form. Exiting."
## exit
## endforms
exit(-1);
}
/*
** Get profile of form. Construct target lists and access
** variables for use in queries to browse and update data.
*/
if (!Get_Form_Data (formname, tabname))
{
## message "Could not profile form. Exiting."
## exit
## endforms
exit(-1);
}
/*
** Display form and interact with user, allowing browsing and
** appending of new data.
*/
## display formname fill
## initialize
## activate menuitem "Browse"
## {
/*
** Retrieve data and display first row on form, allowing
** user to browse through successive rows. If data types
** from table are not consistent with data descriptions
** obtained from user's form, a retrieval error will
** occur. Inform user of this or other errors.
** Sort on first column. Note the use of "ret_varN" to
** indicate the column name to sort on.
*/
## retrieve (param(ret_target_list, var_addresses))
## sort by ret_var1
## {
want_next = 0;
## putform formname (param(put_target_list, var_addresses))
## inquire_frs frs (inq_error = errorno)
if (inq_error > 0)
{
## message "Could not put data into form"
## endretrieve
}
/* Display data before prompting user with submenu */
## redisplay
## submenu
## activate menuitem "Next"
## {
## message "Next row"
want_next = 1;
## }
## activate menuitem "End"
## {
## endretrieve
## }
## } /* End of Retrieve Loop */
## inquire_ingres (inq_error = errorno)
if (inq_error > 0)
{
## message "Could not retrieve data from database"
}
else if (want_next == 1)
{
/* Retrieve loop ended because of no more rows */
## message "No more rows"
}
## sleep 2
/* Clear fields filled in submenu operations */
## clear field all
## }
## activate menuitem "Insert"
## {
## getform formname (param(get_target_list, var_addresses))
## inquire_frs frs (inq_error = errorno)
if (inq_error > 0)
{
## clear field all
## resume
}
## append to tabname (param(put_target_list, var_addresses))
## inquire_ingres (inq_error = errorno, num_updates = rowcount)
if (inq_error > 0 || num_updates == 0)
{
## message "No rows appended because of error."
}
else
{
## message "One row inserted"
}
## sleep 2
## }
## activate menuitem "Clear"
## {
## clear field all
## }
## activate menuitem "End"
## {
## breakdisplay
## }
## finalize
## exit
## endforms
## }
/*
** Procedure: Get_Form_Data
** Purpose: Get the name and data type of each field of a
** form using the FORMDATA loop. From this information,
** build the target strings and array of variable
** addresses for use in the PARAM target list of
** database and forms statements. For example, assume
** the form has the following fields:
**
**
** Field name Type Nullable?
** ---------- ---- ---------
** name character No
** age integer Yes
** salary money Yes
**
** Based on this form, this procedure will construct
** the following target string for the PARAM clause of
** a PUTFORM statement:
**
** "name = %c, age = %i4:%i2, salary = %f8:i2"
**
** Note that the target strings for other statements
** have differing syntax, depending on whether the
** field/column name or the user variable is the
** target of the statement.
**
** The other element of the PARAM clause, the
** "var_addresses" array, would be constructed by
** this procedure as follows:
**
** var_addresses[0] = pointer into "char_vars" array
** var_addresses[1] = address of vars[0].intv
** var_addresses[2] = address of vars[0].indv
** var_addresses[3] = address of vars[1].fltv
** var_addresses[4] = address of vars[1].indv
**
** Parameters:
** formname
** - Name of form to profile.
*/
## int
## Get_Form_Data(formname)
## char *formname;
## {
## int inq_error;
## int fld_type; /* Data type of field */
## char fld_name[25]; /* Name of field */
## int fld_length; /* Length of (character) field */
## int is_table; /* Is field a table field? */
char loc_target[15]; /* Temporary target description */
int addr_cnt = 0; /* Number of variable addresses */
int fld_cnt = 0; /* Index to variable structures array */
char *char_ptr = char_vars; /* Index into character pool */
int ret_stat = 1; /* Return status */
/* Data types of fields */
# define date 3
# define money 5
# define int 30
# define float 31
# define char 20
# define vchar 21
# define c 32
# define text 37
## formdata formname
## {
/* Get data information and name of each field */
## inquire_frs field "" (fld_type=datatype, fld_name=name,
## fld_length = length, is_table = table)
/* Return on errors */
## inquire_frs frs (inq_error = errorno)
if (inq_error > 0)
{
ret_stat = 0;
## enddata
}
/*
** This application does not process table fields.
** However, the TABLEDATA statement is available
** to profile table fields.
*/
if (is_table == 1)
{
## message "Table field in form"
## sleep 2
ret_stat = 0;
## enddata
}
/* More fields than allowable columns in database? */
if (fld_cnt >= maxcols)
{
## message
## "Number of fields exceeds allowable database columns"
## sleep 2
ret_stat = 0;
## enddata
}
/* Separate target list items with commas */
if (fld_cnt > 0)
{
strcat (put_target_list, ",");
strcat (get_target_list, ",");
strcat (ret_target_list, ",");
}
/* Field/col name is the target in put/append statements */
strcat (put_target_list, fld_name);
/*
** Enter data type information in target list. Point array
** of addresses into relevant data pool. Note that by
** testing the absolute value of the data type value, the
** program defers the question of nullable data to a later
** segment of the code, where it is handled in common for
** all types.
** (Recall that a negative data type indicates a nullable
** field.)
*/
switch (abs(fld_type))
{
case int:
strcat (put_target_list, "= %i4");
strcat (get_target_list, "%i4");
strcat (ret_target_list, "%i4");
var_addresses[addr_cnt++]
= (char *)&vars[fld_cnt].intv;
break;
case float:
case money:
strcat (put_target_list, "= %f8");
strcat (get_target_list, "%f8");
strcat (ret_target_list, "%f8");
var_addresses[addr_cnt++] =
(char *)&vars[fld_cnt].fltv;
break;
case c:
case char:
case text:
case vchar:
case date:
strcat (put_target_list, "=%c");
sprintf (loc_target, "%%c%d", fld_length);
strcat (get_target_list, loc_target);
strcat (ret_target_list, loc_target);
/*
** Assign a segment of character buffer as space
** for data associated with this field. If
** assignment would cause overflow, give error
** and return.
*/
if (char_ptr + fld_length >= &char_vars[CHARBUFSIZE])
{
## message "Character data fields will cause overflow"
## sleep 2
ret_stat = 0;
## enddata
}
var_addresses[addr_cnt++] = char_ptr;
char_ptr += fld_length +1;
/* Allow room for terminator */
break;
default:
## message "Field has unknown data type"
ret_stat = 0;
## enddata
} /* End switch */
/* If field is nullable, complete target lists
** and address assignments to allow for null data.
*/
if (fld_type \ 0)
{
strcat (put_target_list, ":%i2" );
strcat (get_target_list, ":%i2" );
strcat (ret_target_list, ":%i2" );
var_addresses[addr_cnt++] = (char *)&vars[fld_cnt].indv;
}
/* Ready for next structure variable */
fld_cnt++;
/*
** Field/column name is the object in getform/retrieve
*/ statements */
strcat (get_target_list, "=");
strcat (get_target_list, fld_name);
strcat (ret_target_list, "=");
strcat (ret_target_list, "t.");
strcat (ret_target_list, fld_name);
## } /* End of formdata loop */
return ret_stat;
## } /* Get_Form_Data */