comp.databases.informix.isql: Cool New Tool for ISQL users

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: 0 ########################### loop_scr: Make an INFORMIX screen ################# 1 if [ $# -lt 3 ]; then 2 echo "Usage: loop_scr database table.lst output_file [project]" 3 exit 4 else 5 i=0 6 for TBL in `cat $TAB_LIST` 7 do 8 i=`expr $i + 1` 9 TAG_PFX=`echo ${TAG_PREFIX_ARR} | awk -F":" ' BEGIN { arr_cnt=0 10 idx_no='${i}' 11 } 12 { arr_cnt = split( $0, pic_arr , ":" ) } 13 { print pic_arr[idx_no] } ' ` 14 INFO_ZERO=${TBL}_0.tmp 15 INFO_ONE=${TBL}_1.tmp 16 INFO_TWO=${TBL}_2.tmp 17 INFO_THREE=${TBL}_3.tmp 18 echo $TAG_PFX 19 get_data_types 20 cnvt_data_types 21 done 22 cat *_3.tmp > ${GRAND_ATTR}1.tmp 23 make_attr_list 24 draw_screen 25 build_scr_hdr 26 build_screen 27 build_scr_tbl 28 dump_attrs 29 append_instr 30 add_req_cmts 31 put_borders 32 rm *.tmp 33 fi 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.