# 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 */