IIUG Software Repository Files

dbpipe.sh      v1.2
=========

20.12.99
Julian Zalutsky
julian@cis.belpak.brest.by
Brest, Belarus.


This script allows to execute SQL commands through a single dbaccess from
several ksh scripts or background jobs at the same time.
ESQL/C isn't needed, awk only.

It can be useful during migration from old database (Clipper for example) to
Informix database when real-time answer from Informix database to Clipper
tasks isn't needed.

We have realized a simple "replication of two different databases" through a
self-made "middleware" with following scheme:
   Old Clipper tasks writes files with SQL commands -> ksh scripts scans
   NetWare directories and executes SQL requests (SPs, insert/update/delete).
Backwards:
   Informix database triggers writes a lot of inserted/deleted.field values
   from any tables to one work table -> ksh script checks this table and
   changes a Clipper database.
To prevent a recursion (Clipper db -> Informix db -> Clipper db) global
variable in stored procedure can be used.


Example:

. dbpipe.sh LOAD workdir                  # load "channel"
. dbpipe.sh OPEN workdir                  # open first "thread"
if dbpSQL "database ware;"; then          # connect to database

   (if . dbpipe.sh OPEN workdir; then     # open second thread
      while read cmd; do
         if ! dbpSQL "$cmd" ferr1; then   # execute SQL commands in 2nd thread
            echo "Error in SQL command from file file1:"
            cat ferr1
         fi
      done <file1 >>logerr
      . dbpipe.sh CLOSE workdir           # close second thread
   fi)&

   (if . dbpipe.sh OPEN workdir; then     # open third thread
      while read cmd; do
         if ! dbpSQL "$cmd" ferr2; then   # execute SQL commands
            echo "Error in SQL command from file file2:"
            cat ferr2
         fi
      done <file2 >>logerr
      . dbpipe.sh CLOSE workdir           # close thrid thread
   fi)&

   if [ $(jobs -p |wc -l) -eq 2 ]; then

      # execute SQL commands in 1st thread
      if ! dbpSQL "insert into logtable values($LOGNAME,CURRENT);" ferr3; then
         mail -s "Error with logging" administrator <ferr3
      fi
      . . .
   . dbpipe.sh CLOSE workdir              # close first thread
   . dbpipe.sh UNLOAD                     # unload channel



Notes:

1. RESTRICTION: all threads works with a same database opened by first thread.
2. Channel is a set of fore daemons (dbaccess, awk1, awk2, awk3) and 3 pipes.
   Thread is a set of two personal pipes and dbpSQL() function.
3. Following lines removed from dbaccess stdout:
       Error in line 1
       Near character position N
4. It isn't fact that stdout is ready after dbpSQL(), sometimes small waiting
   is needed in main script. Reason:
   End of SQL command determines by dbaccess stderr (I don't know other
   method). UNIX works with /dev/stdout ¨ /dev/stderr asynchronously.
   sleep or control SQL command are bad solutions.
   I don't need stdout usually because we perform EXECUTE PROCEDURE,
   UPDATE and so on, but not SELECT. For example:
       if ! dbpSQL N "<SQL>;" ferr; then
          <error message>
   If stdout is needed for you then:
       dbpSQL N "<SQL>;" ferr fout
       if [ $? -ne 0 ]; then
          <error message>
       else
          <any action(s): variable setting, count increment or other>
          mv fout my_file
5. With threads for different users you must replace mode in two mkfifo
   commands into dbpipe.sh.



dbptst.sh
=========

Three tests generates work files a1,a2;
works up to 15-30 seconds.


==========
That's all.