IIUG Software Repository Files

Newsgroups: comp.databases.informix
Subject: Cool New Tool for ISQL users

From: tschaefe@gate.net (Tim Schaefer)
Date: 8 Jul 1995 16:07:37 GMT

New Tool:        loop_scr

Author:          Tim Schaefer, The Computer Business Company, FL, USA

Requirements:   ISQL, AWK,  sformbld, Bourne shell    
                ISQL            -    should work with most versions
                AWK             -    at least a newer awk, but try for nawk.
                sformbld        -    Not required if you have the ISQL interface
                Bourne shell    -    still the lowest common denominator.

Introduction

Sometimes we have slow days, and look for opportunities to do things to better
our world, in particular, our programming world.  I had an opportunity the
other day to spend a moment on a tool, and thought I'd share it here.  As
Gump would've pointed out, I had no particular reason to write this tool,
other than the desire to try and make it work.  In writing it, I get a 
better understanding of some of the issues surrounding its' use.  And it
does come in handy rather than jumping into the form generator in ISQL.

Of particular curiousity to me ever since I started using INFORMIX SQL and
the 4GL, has been the "HOW" and "WHY" of the ISQL interface.  I'm probably
less interested in the WHY part, and more interested in the HOW.  We never
really get any inside information from INFORMIX folk about the WHY and HOW,
as this would expose them to even more user interaction than would probably
be healthy.  But occasionally they do talk about these products, and I'm
on bended ear.  There's always room for improvement, but this article is
not about improving the product, but improving the USE of the product.
Given that the product cannot or will not be changed, how can I improve my
USE of the product?  

The particular tool I present here is more for experimentation than anything.
I don't presume its' value to you, other than fodder for future use in 
creating your own tools.  The idea of creating tools is a constant curiousity
for me, putting a habitual thought of how to make things better than just
accepting things as they are.  This can also expose oneself to the group
with the "knowledge gap".  This is where your solution is less than optimal
and other folks know better.  But contributions being what they are, in 
this case cost-free, the expense is only the time to try it out.  I look
forward to your suggestions for improvements.  It's probably more 
interesting than vendor bashing too.

The Tool

ISQL is a strange critter.  Not too many sites use it in its' fullest 
measure, with the PERFORM mechanisms, but of late I have seen some postings
that would suggest folks are still PERFORMing.  I like some of the PERFORM
features but really defer to 4GL.  The biggest use I get out of PERFORM
is the cheap-n-easy screen runner, allowing easy access to tables without
having to write SQL, and the SQL scratch-pad, allowing the ad hoc SQL 
interface to the data base.  These two features alone make it a good buy.

A while back I had introduced a screen-code generator that allows you to 
create a screen on the command-line, in several versions, one that creates
bordered screens, and another that produces plain borderless screens.  These
are two tools I use a lot.  But the other day I needed the ability to 
create a screen with more than one table.  What to do?  Well, take an
existing tool, and improve it, of course.

By the way, I've used this tool with both the 5.01 and 7.1 engines, and it
works very well.  Bugs are discussed below.

Let's take a look at the logic of this program:

Line    Description
0-3     Argument checking
5-21    Build a data file with the raw information needed to create ATTRIBUTES 
        for each table.  Save this information in a separate file for later 
        use.
22      concatenate all the individual data files for each table into one data 
        file.
23      Convert the data into a format that will compile using sformbld or 
        fglform.  Save this data for later use.
24      Start assembling the screen(s).  This first phase builds the fields into
        a data file, and then the screens will be enhanced later.
25      The first screen for the first table needs that DATABASE statement, so
        it's put here.
26      The assembly of the screens starts, and a new "page" is created if 
        either a 15-line limit is reached or a new table name is found, so when
        you run the form it should be theoretically less confusing.
27      Next comes the "TABLES" and "ATTRIBUTES" key words.
28      Now append the actual attributes.  You'll notice that each table has a 
        new prefix on the TAG, so that you can easily differentiate from each
        table visually.  This is not necessary, but an added touch.
29      Append the INSTRUCTIONS portion.
30      There are COMMENTS on each field, that tell the user the data type and 
        length of the field.  But it's also a good safety practice to put the
        REQUIRED attribute on fields that are NOT NULL in the data base.  We
        tracked this feature early on, expressly for this purpose.
31      And now for the gravy.  Let's put borders on each screen, to enhance the
        overall look of the screen.
32      We're done!  Let's get rid of all those nasty tmp files and compile the
        form using sformbld.

        You're now ready to give it a shot.  

    Usage: loop_scr database  table.lst  output_file  [project]

    database    -   a valid data base you can access.
    table.lst   -   a file with a list of some valid tables in the
                    valid data base.
    output_file -   Name of your new multi-table screen.  ( scr.per )
    [project]   -   A 10-char string which is placed in the lefthand
                    portion of the screen, such as a project or your
                    company's initials, or the product name--whatever.

Bugs

    Well, if you're really into experimentation, make a table list with
    about 20 or 30 tables and watch what happens.  Oh yes, the loop_scr
    program will work fine.  But a couple of limitations exist with the
    PERFORM mechanism.  You can only have 26 single-character fields
    in the whole screen form.  Too bad.  Next, PERFORM will only run
    20 pages.  So, yes, you CAN create one big monster PER for your
    tables.  It might be fun to give it a try.  But it ain't gonna work.
    You might try this for all the tables and use it for some kind of 
    documentation tool, as all the fields are referenced here.

    Occasionally, the AWK logic may produce a header and then skip to
    top of page.  This is an easy edit, and the price of the tool is
    low enough to make this a bug to live with.

Epilogue

    I hope this will provide you with some functionality, if not the
    mere exploration of tool creation and use.  I look forward to 
    your suggestions for improvements, and new ideas.  I trust you'll
    find a certain amount of Object Orientated Shell Scripting here as
    well.  

Tim Schaefer
tschaefe@gate.net
July 1995

==============================---o00--(_)--00o---============================
Tim Schaefer            tschaefe@gate.net      http://www.gate.net/~tschaefe
                           And now a Florida FAQ
-----------------------------------------------------------------------------
Q: What's a REDNECK Special?
A: Catfish, shrimp, and gator balls,  w/french fries, of course
=============================================================================