A Dynamic SQL/Forms Database Browser
This program lets the user browse data from and insert data into any table in any database, using a dynamically defined form. The program uses Dynamic SQL and Dynamic FRS statements to process the interactive data. You should already have used VIFRED to create a Default Form based on the database table that you want to browse. VIFRED will build a form with fields that have the same names and data types as the columns of the specified database table.
When run, the program prompts the user for the name of the database, the table and the form. The form is profiled using the describe form statement, and the field name, data type, and length information is processed. From this information, the program fills in the SQLDA data and null indicator areas, and builds two Dynamic SQL statement strings to select data from and insert data into the database.
The Browse menu item retrieves the data from the database using an SQL cursor associated with the dynamic select statement, and displays that data using the dynamic putform statement. A submenu allows the user to continue with the next row or return to the main menu. The Insert menu item retrieves the data from the form using the dynamic getform statement, and adds the data to the database table using a prepared insert statement. The Save menu item commits the changes and, because prepared statements are discarded, again prepares the select and insert statements. When the user selects Quit, all pending changes are rolled back and the program is terminated.
Note: The application uses function prototypes and ifdef statements to enable you to build it using either the ESQL/C or ESQL/C++ precompiler.
Sample Application
# include <stdio.h>
# include <string.h>
# include <malloc.h>
/*
** Declare the SQLCA structure and the SQLDA typedef.
*/
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
EXEC SQL DECLARE sel_stmt STATEMENT; /* Dynamic SQL SELECT statement */
EXEC SQL DECLARE ins_stmt STATEMENT; /* Dynamic SQL INSERT statement */
EXEC SQL DECLARE csr CURSOR FOR sel_stmt; /* Cursor for SELECT statement
/
/*
** Buffer lengths.
*/
# define NAME_MAX 50 /* Max name lengths */
# define STMT_MAX 1000 /* Max SQL statement length */
/*
** Global SQL variables.
*/
IISQLDA *sqlda = (IISQLDA *)0; /* Pointer to the SQL dynamic area */
/*
** Result storage buffer for dynamic SQL and FRS statements.
** This result buffer is dynamically allocated and filled.
** Each SQLDA SQLVAR sets its SQLDATA and SQLIND pointers to
** offsets in this buffer.
*/
struct {
int res_length; /* Size of res_data */
char *res_data; /* Pointer to allocated result buffer */
} res_buf = {0, (char *)0};
/*
** Procedures in this file.
** Function prototypes for C++ only so that this is compatible
** with old-style C compilers
*/
# ifdef __cplusplus
int Describe_Form(char *formname, char *tabname, char *sel_buf, char *ins_buf);
/* DESCRIBE form and set up SQL statements */
void Init_Sqlda(int num_elems); /* Initialize SQLDA */
char *calloc(unsigned nelem, unsigned elsize);
# else
int Describe_Form(); /* DESCRIBE form and set up SQL statements */
void Init_Sqlda(); /* Initialize SQLDA */
char *calloc(); /* C allocation routine */
# endif /* __cplusplus */
/*
** Procedure: main
** Purpose: Main body of Dynamic SQL forms application. Prompt for
** database, form and table name. Call Describe_Form
** to obtain a profile of the form and set up the SQL
** statements. Then allow the user to interactively browse
** the database table and append new data.
*/
main()
{
EXEC SQL BEGIN DECLARE SECTION;
char dbname[NAME_MAX +1]; /* Database name */
char formname[NAME_MAX +1]; /* Form name */
char tabname[NAME_MAX +1]; /* Table name */
char sel_buf[STMT_MAX +1]; /* Prepared SELECT */
char ins_buf[STMT_MAX +1]; /* Prepared INSERT */
int err; /* Error status */
char ret[10]; /* Prompt error buffer */
EXEC SQL END DECLARE SECTION;
EXEC FRS FORMS;
/* Prompt for database name - will abort on errors
*/
EXEC SQL WHENEVER SQLERROR STOP;
EXEC FRS PROMPT ('Database name: ', :dbname);
EXEC SQL CONNECT :dbname;
EXEC SQL WHENEVER SQLERROR CALL SQLPRINT;
/*
** Prompt for table name - later a Dynamic SQL SELECT statement
** will be built from it.
*/
EXEC FRS PROMPT ('Table name: ', :tabname);
/*
** Prompt for form name. Check forms errors reported
** through INQUIRE_FRS.
*/
EXEC FRS PROMPT ('Form name: ', :formname);
EXEC FRS MESSAGE 'Loading form ...';
EXEC FRS FORMINIT :formname;
EXEC FRS INQUIRE_FRS FRS (:err = ERRORNO);
if (err > 0)
{
EXEC FRS MESSAGE 'Could not load form. Exiting.';
EXEC FRS ENDFORMS;
EXEC SQL DISCONNECT;
exit(1);
}
/* Commit any work done so far - access of forms catalogs */
EXEC SQL COMMIT;
/* Describe the form and build the SQL statement strings */
if (!Describe_Form(formname, tabname, sel_buf, ins_buf))
{
EXEC FRS MESSAGE 'Could not describe form. Exiting.';
EXEC FRS ENDFORMS;
EXEC SQL DISCONNECT;
exit(1);
}
/*
** PREPARE the SELECT and INSERT statements that correspond to the
** menu items Browse and Insert. If the Save menu item is chosen
** the statements are reprepared.
*/
EXEC SQL PREPARE sel_stmt FROM :sel_buf;
err = sqlca.sqlcode;
EXEC SQL PREPARE ins_stmt FROM :ins_buf;
if ((err < 0) || (sqlca.sqlcode < 0))
{
EXEC FRS MESSAGE 'Could not prepare SQL statements. Exiting.';
EXEC FRS ENDFORMS;
EXEC SQL DISCONNECT;
exit(1);
}
/*
** Display the form and interact with user, allowing browsing
** and the inserting of new data.
*/
EXEC FRS DISPLAY :formname FILL;
EXEC FRS INITIALIZE;
EXEC FRS ACTIVATE MENUITEM 'Browse';
EXEC FRS BEGIN;
/*
** Retrieve data and display the first row on the form, allowing
** the user to browse through successive rows. If data types
** from the database table are not consistent with data
** descriptions obtained from the form, a retrieval error
** will occur. Inform the user of this or other errors.
**
** Note that the data will return sorted by the first field that
** was described, as the SELECT statement, sel_stmt, included an
** ORDER BY clause.
*/
EXEC SQL OPEN csr;
/* Fetch and display each row */
while (sqlca.sqlcode == 0)
{
EXEC SQL FETCH csr USING DESCRIPTOR :sqlda;
if (sqlca.sqlcode != 0)
{
EXEC SQL CLOSE csr;
EXEC FRS PROMPT NOECHO ('No more rows :', :ret);
EXEC FRS CLEAR FIELD ALL;
EXEC FRS RESUME;
}
EXEC FRS PUTFORM :formname USING DESCRIPTOR :sqlda;
EXEC FRS INQUIRE_FRS FRS (:err = ERRORNO);
if (err > 0)
{
EXEC SQL CLOSE csr;
EXEC FRS RESUME;
}
/* Display data before prompting user with submenu */
EXEC FRS REDISPLAY;
EXEC FRS SUBMENU;
EXEC FRS ACTIVATE MENUITEM 'Next', FRSKEY4;
EXEC FRS BEGIN;
/* Continue with cursor loop */
EXEC FRS MESSAGE 'Next row ...';
EXEC FRS CLEAR FIELD ALL;
EXEC FRS END;
EXEC FRS ACTIVATE MENUITEM 'End', FRSKEY3;
EXEC FRS BEGIN;
EXEC SQL CLOSE csr;
EXEC FRS CLEAR FIELD ALL;
EXEC FRS RESUME;
EXEC FRS END;
} /* While there are more rows */
EXEC FRS END;
EXEC FRS ACTIVATE MENUITEM 'Insert';
EXEC FRS BEGIN;
EXEC FRS GETFORM :formname USING DESCRIPTOR :sqlda;
EXEC FRS INQUIRE_FRS FRS (:err = ERRORNO);
if (err > 0)
{
EXEC FRS CLEAR FIELD ALL;
EXEC FRS RESUME;
}
EXEC SQL EXECUTE ins_stmt USING DESCRIPTOR :sqlda;
if ((sqlca.sqlcode < 0) || (sqlca.sqlerrd[2] == 0))
{
EXEC FRS PROMPT NOECHO ('No rows inserted :', :ret);
}
else
{
EXEC FRS PROMPT NOECHO ('One row inserted :', :ret);
}
EXEC FRS END;
EXEC FRS ACTIVATE MENUITEM 'Save';
EXEC FRS BEGIN;
/*
** COMMIT any changes and then re-PREPARE the SELECT and INSERT
** statements as the COMMIT statements discards them.
*/
EXEC SQL COMMIT;
EXEC SQL PREPARE sel_stmt FROM :sel_buf;
err = sqlca.sqlcode;
EXEC SQL PREPARE ins_stmt FROM :ins_buf;
if ((err < 0) || (sqlca.sqlcode < 0))
{
EXEC FRS PROMPT NOECHO ('Could not reprepare SQL statements :',
:ret);
EXEC FRS BREAKDISPLAY;
}
EXEC FRS END;
EXEC FRS ACTIVATE MENUITEM 'Clear';
EXEC FRS BEGIN;
EXEC FRS CLEAR FIELD ALL;
EXEC FRS END;
EXEC FRS ACTIVATE MENUITEM 'Quit', FRSKEY2;
EXEC FRS BEGIN;
EXEC SQL ROLLBACK;
EXEC FRS BREAKDISPLAY;
EXEC FRS END;
EXEC FRS FINALIZE;
EXEC FRS ENDFORMS;
EXEC SQL DISCONNECT;
} /* main */
/*
** Procedure: Describe_Form
** Purpose: Profile the specified form for name and data type
** information. Using the DESCRIBE FORM statement, the
** SQLDA is loaded with field information from the form.
** This procedure processes this information to allocate
** result storage, point at storage for dynamic FRS data
** retrieval and assignment, and build SQL statements
** strings for subsequent dynamic SELECT and INSERT
** statements. For example, assume the form (and table)
** 'emp' has the following fields:
**
** Field Name Type Nullable?
** ---------- ---- ---------
** name char(10) No
** age integer4 Yes
** salary money Yes
**
** Based on 'emp', this procedure will construct the SQLDA.
** A data storage buffer, whose size is determined by
** accumulating the field data type lengths, is allocated.
** The SQLDATA and SQLIND fields are pointed at offsets into
** the result storage buffer. The following SQLDA is built:
**
** sqlvar[0]
** sqltype = IISQ_CHA_TYPE
** sqllen = 10
** sqldata = offset #1 into storage
** sqlind = null
** sqlname = 'name'
** sqlvar[1]
** sqltype = -IISQ_INT_TYPE
** sqllen = 4
** sqldata = offset #2 into storage
** sqlind = offset #3 into storage
** sqlname = 'age'
** sqlvar[2]
** sqltype = -IISQ_FLT_TYPE
** sqllen = 8
** sqldata = offset #4 into storage
** sqlind = offset #5 into storage
** sqlname = 'salary'
**
** The procedure does not verify that the allocation routine
** that is called does not fail.
** This procedure also builds two dynamic SQL statements strings.
** Note that the procedure should be extended to verify that the
** statement strings do fit into the statement buffers (this was
** not done in this example). The above example would construct
** the following statement strings:
**
** 'SELECT name, age, salary FROM emp ORDER BY name'
** 'INSERT INTO emp (name, age, salary) VALUES (?, ?, ?)'
**
** Parameters:
** formname - Name of form to profile.
** tabname - Name of database table.
** sel_buf - Buffer to hold SELECT statement string.
** ins_buf - Buffer to hold INSERT statement string.
** Returns:
** TRUE/FALSE - Success/failure - will fail on error
** or upon finding a table field.
*/
# ifdef __cplusplus
int
Describe_Form(char *formname, char *tabname, char *sel_buf, char *ins_buf)
# else
int
Describe_Form(formname, tabname, sel_buf, ins_buf)
char *formname;
char *tabname;
char *sel_buf;
char *ins_buf;
# endif /* __cplusplus */
{
char names[STMT_MAX +1]; /* Names for SQL statements */
char *nm;
char marks[STMT_MAX +1]; /* Place holders for INSERT */
char *mk;
int err; /* Error status */
char ret[10]; /* Prompt error buffer */
int i; /* Index into SQLVAR */
IISQLVAR *sqv; /* Pointer to SQLVAR */
int base_type; /* Base type w/o nullability*/
int nullable; /* Is nullable (SQLTYPE < 0) */
int res_cur_size; /* Result size required */
/*
** Allocate a new SQLDA and DESCRIBE the form. Start out with a
** default SQLDA for 10 fields. If we cannot fully describe the
** form (our SQLDA is too small) then allocate a new one and
** redescribe the form.
*/
Init_Sqlda(10);
EXEC FRS DESCRIBE FORM :formname ALL INTO :sqlda;
EXEC FRS INQUIRE_FRS FRS (:err = ERRORNO);
if (err > 0)
return 0; /* Error already displayed */
if (sqlda->sqld > sqlda->sqln) /* Redescribe */
{
Init_Sqlda(sqlda->sqld);
EXEC FRS DESCRIBE FORM :formname ALL INTO :sqlda;
}
else if (sqlda->sqld == 0) /* No fields */
{
EXEC FRS PROMPT NOECHO ('There are no fields in the form :', :ret);
return 0;
}
/*
** For each field determine the size and type of the data
** area, which will be allocated out of the result data area.
** This will be allocated out of res_buf in the next loop.
** If a table field type is returned then issue an error.
**
** Also, for each field add the field name to the 'names' buffer
** and the SQL place holders '?' to the 'marks' buffer, which
** will be used to build the final SELECT and INSERT statements.
*/
for (res_cur_size = 0, i = 0; i < sqlda->sqld; i++)
{
sqv = &sqlda->sqlvar[i]; /* Point at current column */
/* Find the base-type of the result (non-nullable) */
if ((base_type = sqv->sqltype) < 0)
{
nullable = 1;
base_type = -base_type;
}
else
{
nullable = 0;
}
/* Collapse different types into INT, FLOAT or CHAR */
switch (base_type)
{
case IISQ_INT_TYPE:
/* Always retrieve into a long integer */
sqv->sqltype = IISQ_INT_TYPE;
sqv->sqllen = sizeof(long);
res_cur_size += sizeof(long);
break;
case IISQ_MNY_TYPE:
case IISQ_FLT_TYPE:
/* Always retrieve into a double floating-point */
sqv->sqltype = IISQ_FLT_TYPE;
sqv->sqllen = sizeof(double);
res_cur_size += sizeof(double);
break;
case IISQ_DTE_TYPE:
sqv->sqllen = IISQ_DTE_LEN;
/* Fall through to handle like CHAR */
case IISQ_CHA_TYPE:
case IISQ_VCH_TYPE:
/*
** Assume no binary data is returned from the CHAR type.
** Also allocate one extra byte for the null terminator.
*/
sqv->sqltype = IISQ_CHA_TYPE;
res_cur_size += sqv->sqllen + 1;
/* Always round off to even data boundary */
if (res_cur_size % 2)
res_cur_size++;
break;
case IISQ_TBL_TYPE: /* Table field */
EXEC FRS PROMPT NOECHO ('Table field found in form :', :ret);
return 0;
default:
EXEC FRS PROMPT NOECHO ('Invalid field type :', :ret);
return 0;
} /* switch on base type */
/*
** Save away space for the null indicator and set
** negative type id
*/
if (nullable)
{
res_cur_size += sizeof(short);
sqv->sqltype = -sqv->sqltype;
}
/*
** Store field names and place holders (separated by commas)
** for the SQL statements.
*/
if (i == 0)
{
names[0] = marks[0] = '\0';
nm = names;
mk = marks;
}
else
{
strcat(nm++, ",");
strcat(mk++, ",");
}
sprintf(nm, "%.*s", sqv->sqlname.sqlnamel, sqv->sqlname.sqlnamec);
nm += sqv->sqlname.sqlnamel;
strcat(mk++, "?");
} /* for each column */
/*
** At this point we've saved all field names and converted all
** types to one of INT, CHAR or FLOAT. Now we allocate a single
** result buffer, and point all the result column data areas into it.
**
** If we have an old result data area that is not large enough then
** free it and allocate a new one. Otherwise we can reuse the last one.
*/
if (res_buf.res_length > 0 && res_buf.res_length < res_cur_size)
{
free(res_buf.res_data);
res_buf.res_length = 0;
}
if (res_buf.res_length == 0)
{
res_buf.res_data = calloc(1, res_cur_size);
res_buf.res_length = res_cur_size;
}
/*
** Now for each column now assign the result address (SQLDATA) and
** indicator address (SQLIND) from the result data area.
** As already calculated in the previous loop, the addresses will
** point at offsets into res_buf.
*/
for (res_cur_size = 0, i = 0; i < sqlda->sqld; i++)
{
sqv = &sqlda->sqlvar[i];
/* Find the base-type of the result (non-nullable) */
if ((base_type = sqv->sqltype) < 0)
base_type = -base_type;
/* Current data points at current offset */
sqv->sqldata = (char *)&res_buf.res_data[res_cur_size];
res_cur_size += sqv->sqllen;
if (base_type == IISQ_CHA_TYPE)
{
res_cur_size++; /* Add one for null */
if (res_cur_size % 2) /* Round off to even boundary */
res_cur_size++;
}
/* Point at result indicator variable */
if (sqv->sqltype < 0)
{
sqv->sqlind = (short *)&res_buf.res_data[res_cur_size];
res_cur_size += sizeof(short);
}
else
{
sqv->sqlind = (short *)0;
} /* if type is nullable */
} /* for each column */
/*
** Create final SELECT and INSERT statements. For the SELECT
** statement ORDER BY the first field.
*/
sqv = &sqlda->sqlvar[0];
sprintf(sel_buf, "SELECT %s FROM %s ORDER BY %.*s", names, tabname,
sqv->sqlname.sqlnamel, sqv->sqlname.sqlnamec);
sprintf(ins_buf, "INSERT INTO %s (%s) VALUES (%s)", tabname, names,
marks);
return 1;
} /* Describe_Form */
/*
** Procedure: Init_Sqlda
** Purpose: Initialize SQLDA. Free any old SQLDA's and allocate a new
** one. Set the number of SQLVAR elements.
**
** Parameters:
** num_elems - Number of elements.
*/
# ifdef __cplusplus
void
Init_Sqlda(int num_elems)
# else
void
Init_Sqlda(num_elems)
int num_elems;
# endif /* __cplusplus */
{
/* Free the old SQLDA */
if (sqlda)
free((char *)sqlda);
/* Allocate a new SQLDA */
sqlda = (IISQLDA *)calloc(1,
IISQDA_HEAD_SIZE + (num_elems * IISQDA_VAR_SIZE));
sqlda->sqln = num_elems; /* Set the size */
} /* Init_Sqlda */