Cover V05, I07
Article
Figure 1
Figure 2
Figure 3
Figure 4
Listing 1
Sidebar 1

jul96.tar


Listing 1: Sybaccess

1 #-----------------------------------------------------------------------

2 # Sybaccess
3 # Author  of  this  Program:   Ed Schaefer

4 # Program Name and  Release:   Sybaccess

5 # Last Date of Modification:   01/27/95

6 # Description:

7 # This program serves as a menu system to Sybases's Interactive SQL.
8 # Among the options are choosing and displaying a current database,

9 # executing SQL queries, displaying current table statistics, and
10 # performing certain administration functions
11 #
12 trap "" 2 15  # No user interrupt allowed
13 SYBASE=/usr/sybase; export SYBASE
14 CONFIG=./config;    export CONFIG # configuration file
15 GO=\;;              export GO
16 CLEAR=`tput clear`; export CLEAR
17 PAGER=pg;           export PAGER
18 EDITOR=vi;          export EDITOR
19 TMP=/tmp;           export TMP
20 TEE=tee;            export TEE
21 PR="pr -t -8h";     export PR
22 TABLE=TEM;          export TABLE
23 edit_file=$TMP/edit_file.$$  # Temporary files
24 run_file=$TMP/run_file.$$
25 out_file=output.$$
26
27 while [ "$#" -gt 0 ]
28 do # set the command line arguments
29    case $1 in
30      -D) shift;DATABASE=$1  # Database Name
31          shift
32          ;;
33      -D*) DATABASE=`echo $1|cut -c3-`
34          shift
35          ;;
36      -U) shift;USER=$1  # User Name
37          shift
38          ;;
39      -U*) USER=`echo $1|cut -c3-`
40          shift
41          ;;
42      -P) shift;PASSWORD=$1  # User Password
43          shift
44          ;;
45      -P*) PASSWORD=`echo $1|cut -c3-`
46          shift
47          ;;
48      -S) shift;SERVER=$1  # Optional Server
49          shift
50          ;;
51      -S*) SERVER=`echo $1|cut -c3-`
52          shift
53          ;;
54      -I) shift;SYBASE=$1
55          shift
56          ;;
57      -I*) SYBASE=`echo $1|cut -c3-`  # Sybase directory location
58          shift
59          ;;
60       *) echo "Illegal Command-line Option"
61          exit
62          ;;
63   esac
64 done
65 if [ x"$USER" = x ]  # Check for valid defaults
66 then # if undefined, get USER from config file
67   if [ -r "$CONFIG" ]
68   then # does configuration file exist
69      USER=`grep USER $CONFIG|cut -d : -f2`; export USER;
70   fi
71   if [ x"$USER" = x ]
72   then # USER still undefined?
73      echo "User is undefined; Exiting!"
74      exit
75    fi
76 fi
77 if [ y"$PASSWORD" = y ]
78 then # if undefined, get PASSWORD from config file
79    if [ -r "$CONFIG" ]
80    then # does configuration file exist
81      PASSWORD=`grep PASSWORD $CONFIG|cut -d : -f2`; export PASSWORD;
82    fi
83    if [ y"$PASSWORD" = y ]
84    then # PASSWORD still undefined?
85       echo "Password is undefined; Exiting!"
86       exit
87    fi
88 fi
89 if [ z"$DATABASE" = z ]
90 then # if undefined, get DATABASE from config file
91   if [ -r "$CONFIG" ]
92   then # does configuration file exist
93      DATABASE=`grep DATABASE $CONFIG|cut -d : -f2`; export DATABASE;
94   fi
95 fi
96 # if return value is non-null, some error messsage occurred
97 # so terminate
98 arg_check=`$SYBASE/bin/isql -c$GO -U$USER -P$PASSWORD -S$SERVER << !@!
99 !@!` # terminate isql
100 if [ "$arg_check" != "" ]
101 then # returned an error message
102   echo "Sybase Login failed! Illegal User or Password. Exiting!"
103   sleep 2
104   exit
105 fi
106
107 # Function Declaration Section
108 # This function displays the Sybaccess Main Menu
109 display_main ()
110 {
111   echo "
112
113
114                 SYBACCESS MAIN MENU
115
116     D. Database select - Select the current database
117     Q. Query language  - Enter the query language menu
118     T. Table information - Enter the table information menu
119     A. Administration - Perform administration functions
120     E. Exit program - Leave this program
121
122     Enter a choice  \c"
123 }
124
125 # This function displays the current database
126 display_db () {
127 echo "-------------------------" $DATABASE "-------------------------"
128 }
129 # This function attempts to open a database by echoing the command
130 # 'use database' to ISQL.  If the return value is not null,
131 # an error message occured and the database was not
132 # opened.
133 open_db () {
134   if [ x"$DATABASE" = x ]
135   then
136      DATABASE="NO DATABASE"
137   else # check whether the database can be opened
138      db_name=`echo "use $DATABASE\n$GO" \
139        | $SYBASE/bin/isql -c$GO -U$USER -P$PASSWORD -S$SERVER`
140     if [ "$db_name" != "" ]
141     then # returned an error message, Could NOT open database
142        echo "Can NOT open database"
143        sleep 2
144        DATABASE="NO DATABASE"
145     fi
146   fi
147 }
148
149 # This function echos the string 'use database', the contents of the edit
150 # file and the GO command to a file.  The resulting file is piped to ISQL
151 # with the results being sent to an output file and to the pager utility.
152 run_edit () {
153   if [ -f "$edit_file" -a -s "$edit_file" ]
154   then
155       echo "use $DATABASE\n$GO" > $run_file
156       cat $edit_file >> $run_file
157       echo "\n$GO" >> $run_file
158       cat $run_file|$SYBASE/bin/isql -c$GO -U$USER -P$PASSWORD -S$SERVER|
159        $TEE $out_file |$PAGER
160   else
161      echo "Nothing in buffer to run"
162      sleep 1
163   fi
164 }
165
166 # This function displays the Administration Menu.
167 display_admin () {
168 echo   "
169
170
171                ADMINISTRATION MENU
172
173     C. Choose - choose a current table
174     U. Unload - Unload table: $TABLE
175     L. Load - Load table: $TABLE
176     S. Update Statistics
177     E. Return - return to main menu
178
179     Enter a choice  \c"
180 }
181
182 # This function displays the Table Menu.
183 display_table () {
184    echo "
185
186
187                    TABLE MENU
188
189        C. Choose - choose a current table
190        N. No of records in: $TABLE
191        I. Indexes on $TABLE
192        S. Columns on $TABLE
193        E. Return - return to main menu
194
195        Enter a choice  \c"
196 }
197 # This function displays the Query Menu.
198 display_sql () {
199    echo "
200
202                       QUERY MENU
202
203     N. New - create new SQL statements in run file
204     M. Modify - modify SQL statements in run file
205     V. View - view the SQL statements in run file
206     L. Load - load the SQL statements into an external file
207     R. Run  - run SQL statments
208     S. Save - save the SQL statements to an external file
209     E. Return - return to main menu
210
211     Enter a choice  \c"
212 }
213 # This function removes the old edit file and calls EDITOR
214 # (normally vi) to create a new edit file.
215 new_edit () {
216    if [ -f $edit_file ]
217    then
218       rm $edit_file
219    fi
220    $EDITOR $edit_file
221 }
222
223 # This function modifies an existing edit file.
224 modify_edit () {
225    $EDITOR $edit_file
226 }
227
228 # This function views an existing edit file.
229 view_edit () {
230    if [ -f "$edit_file" -a -s "$edit_file" ]
231    then
232       $PAGER $edit_file
233    else
234       echo "Nothing in buffer to view"
235       sleep 1
236    fi
237 }
238
239 # This function prompts for an existing file.  The contents of this
240 # file then becomes the new edit file.
241 load_edit () {
242    echo "Enter file name to load:  \c"
243    read load_name
244    if [ -r  "$load_name" ]
245    then
246       cp $load_name $edit_file
247       echo "Loading "$load_name" into edit file"
248       sleep 2
249   else
250       echo "File name: "$load_name" does NOT exist!"
251       sleep 3
252    fi
253 }
254
255 # This function prompts for a save file name.  The contents of
256 # edit file then becomes this save file.
257 save_edit () {
258    if [ -f "$edit_file" -a -s "$edit_file" ]
259    then
260       echo "Enter file name:  \c"
261       read file_name
262       if [ -r "$file_name" ]
263       then
264          echo "File name exists!"
265       else
266          cp $edit_file $file_name
267          echo "File copied!"
268       fi
269       sleep 1
270    else
271       echo "Nothing in edit file to save"
272       sleep 1
273    fi
274 }
275 # This function:
276 #  1) Prompts for a valid database if there isn't and continues
277 #     only if a valid database is entered
278 #  2) Displays the Administration Menu and prompts for a choice.
279 #  The choices are select another TABLE, update statistics for the
280 #  database, load a TABLE from ascii file, $TABLE.unl, and unload TABLE
281 #  to ascii file, $TABLE.unl.
282 do_admin () {
283    while true
284    do
285       if [ "$DATABASE" = "NO DATABASE" ]
286       then # you need a valid database to query
287          echo "Database select:  \c"
288          read DATABASE
289          open_db
290       fi
291       if [ "$DATABASE" = "NO DATABASE" ]
292       then
293          return
294       fi
295       echo $CLEAR
296       display_db
297       display_admin
298       read q_choice
299       case $q_choice
300       in
301       C|c) select_table;;
302       U|u) echo "Unloading "$TABLE
303            tab_name=$TMP/$TABLE.unl
304         $SYBASE/bin/bcp $DATABASE..$TABLE out $tab_name \
305          -U$USER -P$PASSWORD -S$SERVER -c -t\|
306         sleep 3;;
307       L|l) echo "Loading "$TABLE
308            tab_name=$TMP/$TABLE.unl
309            $SYBASE/bin/bcp $DATABASE..$TABLE in $tab_name \
310             -U$USER -P$PASSWORD -S$SERVER -c -t\|
311         sleep 3;;
312       S|s) echo "Updating Statistics - Please wait"
313            do_statistics;;
314       E|e) return;;
315       *) echo "Bad Choice!"
316          sleep 2;;
317       esac
318    done
319 }
320 # This function:
321 #  1) Prompts for a valid database if there isn't and
322 #     continues only if a valid database is entered
323 #  2) Displays the Table Menu and prompts for a choice.  The
324 #  choices are select another TABLE, find out how many records are in
325 #  the table, and call the create.table and create.index shell scripts
326 #  to display the table structure and columns respectively.
327 do_table () {
328    while true
329    do
330      if [ "$DATABASE" = "NO DATABASE" ]
331      then # you need a valid database to query
332         echo "Database select:  \c"
333         read DATABASE
334         open_db
335      fi
336      if [ "$DATABASE" = "NO DATABASE" ]
337      then
338         return
339      fi
340      echo $CLEAR
341      display_db
342      display_table
343      read q_choice
344      case $q_choice
345      in
346      C|c) select_table;;
347      N|n) cnt_table;;
348      I|i) echo "Processing "$TABLE " index "
349         ./create.index -I$SYBASE -D$DATABASE -U$USER \
350         -P$PASSWORD -T$TABLE;;
351      S|s) echo "Processing "$TABLE " columns "
352         ./create.table -I$SYBASE -D$DATABASE -U$USER \
353         -P$PASSWORD -T$TABLE;;
354      E|e) return;;
355      *) echo "Bad Choice!"
356         sleep 2;;
357      esac
358   done
359 }
360 # This function creates update statistics logic for each user type
361 # table in $DATABASE and echos it to the run_file.  The contents of
362 # run_file are then executed.
363 do_statistics () {
364    echo "use $DATABASE\n$GO" > $run_file
365    echo "set nocount on \
366     select \"update statistics \" + name
367         from $DATABASE..sysobjects
368         where type = 'U'\n$GO" \
369   |$SYBASE/bin/isql -c$GO -U$USER -P$PASSWORD -S$SERVER | \
370   grep -v "^ name                           $" | \
371   grep -v "^ ------------------------------ $" | \
372   grep -v "^$" >> $run_file
373   echo "\n$GO" >> $run_file
374   cat $run_file|$SYBASE/bin/isql -c$GO -U$USER -P$PASSWORD -S$SERVER
375   echo "statistics updated for DATABASE: "$DATABASE
376   sleep 2
377 }
378 # This function displays the Query Menu and prompts the user
379 # to enter an edit command for the edit file or run the edit file.
380 do_query () {
381    while true
382    do
383       if [ "$DATABASE" = "NO DATABASE" ]
384       then # you need a valid database to query
385          echo "Database select:  \c"
386          read DATABASE
387          open_db
388       fi
389       if [ "$DATABASE" = "NO DATABASE" ]
390       then
391          return
392       fi
393       echo $CLEAR
394       display_db
395       display_sql
396       read q_choice
397       case $q_choice
398       in
399       N|n) new_edit;;
400       M|m) modify_edit;;
401       V|v) view_edit;;
402       L|l) load_edit;;
403       R|r) run_edit;;
404       S|s) save_edit;;
405       E|e) return;;
406       *) echo "Bad Choice!"
407          sleep 2;;
408       esac
409    done
410 }
411 # This function displays all the active user tables in $DATABASE
412 # to the screen and prompts the user to enter a valid table.
413 select_table () {
414    echo $CLEAR
415    echo "                               TABLES"
416    echo "set nocount on \
417        select name from $DATABASE..sysobjects\n$GO" \
418    |$SYBASE/bin/isql -c$GO -U$USER -P$PASSWORD -S$SERVER | \
419    grep -v "^ name                           $" | \
420   grep -v "^ ------------------------------ $" | \
421   grep -v "^$" | \
422   grep -v "^ sys*"|  # elliminate the system tables
423   $PR|$PAGER
424   echo "
425        Select table:  \c"
426        read TABLE
427 }
428
429 # This function determines how many records are in $TABLE.
430 cnt_table () {
431    echo $CLEAR
432    echo "set nocount on \
433        select 'Number of records in $TABLE:  ', count(*)
434           from $DATABASE..$TABLE\n$GO" \
435    |$SYBASE/bin/isql -c$GO -U$USER -P$PASSWORD -S$SERVER | \
436    grep -v "^ name                           $" | \
437    grep -v "^ ------------------------------ $" | \
438    grep -v "^$" |
439    $PAGER
440 }
441
442 # This function displays the active databases in the Sybase
443 # instance by looking at the master's sysdatabases table.  The user
444 # is then prompted to enter a valid database.  The open_db function
445 # determines if the entered string is correct.
446 select_database () {
447    echo $CLEAR
448    for db_name in `echo "set nocount on
449    \n$GO
450    select name
451    from master..sysdatabases where name != 'master' and name != 'model'
452    and name != 'sybsystemprocs' and name != 'tempdb'\n$GO" \
453   | $SYBASE/bin/isql -c$GO -U$USER -P$PASSWORD -S$SERVER | \
454   grep -v "^ name                           $" | \
455   grep -v "^ ------------------------------ $" | \
456   grep -v "^$"
457   `
458   do
459     echo $db_name
460   done
461   echo "
462        Database select:  \c"
463        read DATABASE
464   open_db
465 }
466 # End Function Declaration Section
467
468 # Main Processing Section
469 open_db # open the database
470 while true
471 do
472    echo $CLEAR
473    display_db
474    display_main
475
476    read main_choice
477    case $main_choice
478    in
479      D|d) select_database;;
480      Q|q) do_query;;
481      T|t) do_table;;
482      A|a) do_admin;;
483      E|e) echo "Leaving SYBACCESS"
484        sleep 1
485        exit;;
486      *) echo "Bad Choice!"
487         sleep 1;;
488   esac
489 done
490
491 # clean up temp files
492 if [ -f "$edit_file" ]
493 then
494    rm $edit_file
495 fi
496 if [ -f "$run_file" ]
497 then
498    rm $run_file
499 fi