dbq
An SQL Command Line Utility

Last update on October 8, 1997
Send questions, comments and corrections to MDMS




Introduction

The dbq Environment

Command Syntax

Command Line Examples

Using Aliases With dbq

Embedding dbq In Perl Scripts

Supplying Database Values At Run Time


Appendix: Perl Scripts

Perl Script: dbqServer


#! /usr/local/bin/perl
#
# Usage:
#    dbqServer
#
# Description:
#    The package contains the following subroutines:
#
#    connect - Does a fork and exec on dbq with appropriate parameters
#    for a secure connection. Two pipes are established. Command are sent
#    on CMD_OUT. Data is read on DATA_IN.
#
#    cmd - Take a database command and sends it to dbq using CMD_OUT.
#    Results are received on DATA_IN. Error detection is included.
#
#    disconnect - Closes the pipes and kills dbq.
#
#    Each time cmd is executed, the rows returned are left in the array
#    @dbqServer'rows. If there are no rows or an error occured,
#    @dbqServer'rows is undefined. Also, if an error occured $dbqServer'error
#    has a positive value.
#
# Written by:
#    John Rector
#    Jet Propulsion Laboratory
#    jar@next1.jpl.nasa.gov
#
# History:
#    June, 1995        1.0 - initial release
#
# Version:
#    1.0

package dbqServer;

# Create a dbq process that can talk to its parent. Commands are sent
# from the parent to dbq with CMD_OUT. Data is received with DATA_IN.

sub connect {
    if ($#_ < 2) {
        print "Too few arguments passed to dbqServer'connect.\n";
        last;
    }
    $dbSrv = @_[0];
    $db = @_[1];
    $logFile = @_[2];

    # Open two pipes: one for piping commands from the parent process to dbq
    # and a second for piping data from dbq to the parent.

    pipe (CMD_IN, CMD_OUT);
    pipe (DATA_IN, DATA_OUT);

    # Fork and exec dbq. The dbq's pid is returned to the parent. The
    # dbq process's pid is defined as 0.

    $dbqPid = fork ();
    if ($dbqPid != 0) {    # parent

        # Close the ends of the pipe not used and force non-buffered I/0
        # on the others. Reselect STDOUT as the default file descriptor
        # used by the print function.

        close (CMD_IN);
        select (CMD_OUT); $| = 1;
        select (DATA_IN); $| = 1;
        close (DATA_OUT);
        select (STDIN); $| = 1;
        select (STDOUT);
    }
    elsif (defined $dbqPid) {    #child: dbq

        # Close the ends of the pipe not used and force non-buffered I/0
        # on the pipes used.
    
        select (CMD_IN); $| = 1;
        close (CMD_OUT);
        close (DATA_IN);
        select (DATA_OUT); $| = 1;

        # Redirect STDIN, STDERR and STDOUT to the pipe descriptors
        # so they'll be used when we exec dbq. Again, non-buffered IO.
    
        close (STDIN);
        open (STDIN, "<&CMD_IN");
        select (STDIN); $| = 1;
        
        close (STDERR);
        open (STDERR, ">&DATA_OUT");
        select (STDERR); $| = 1;
        
        close (STDOUT);
        open (STDOUT, ">&DATA_OUT");
        select (STDOUT); $| = 1;
    
        # Exec the dbq command with the following command line arguments.
            
        $dbqCmd = "-s $dbSrv -d $db -h no -c stdin >>$logFile";
        exec "$dbqCmd" || die "Can't exec dbq: $!\n";
        exit 0;
    }
    else {    # weird fork error
        die "Can't fork: $!\n";
    }
}

# First argument is the command to send to dbq. Result rows and error
# messages are returned to the array @rows. Command ends with a row
# containing nothing, terminated with a newline character.

sub cmd {
    local ($row);

    $error = 0;
    @rows = ();
    
     # Remove any newlines from the command and send it as a single
     # string. Follow that with the dbq end-of-command signal.
     
    $_[0] =~ s/\n/ /g;
    print CMD_OUT @_[0];
    print CMD_OUT "\ngo\n";
    
     # Get a row. Check for an error - string beginning with MDMS.
     # If no error, push the row onto the stack until last-row indicator -
     # an empty row. If error, print the message and undef @rows; there's
     # no data to access. Test for error is on the value of $error; 1 if
     # error.
     
    while (1) {
        $row = ;
        $error = 1 if ($row =~ /^MDMS/);
        last if $row =~/^\n/;
        push (@rows, $row);
    }
    if ($error) {
        open (LOG, ">$logFile");
        print LOG @rows;
        close LOG;
        undef @rows;
    }
}

# Close the pipes and kill this instance of dbq.

sub disconnect {
    close (CMD_OUT);
    close (DATA_IN);
    kill 15, $dbqPid if defined $dbqPid;
}

1; # The require function will fail without this.

Perl Script: sleepTill

# Usage: # sleepTill # # Description: # Each time the subroutine tomorrow is called, the program will sleep # until the time defined by the variable $batchTime. $batchTime is # passed as the first parameter to the subroutine tomorrow. # The syntax for the $batchTime value is: # # : # # For example: 2:00 - two o'clock in the morning. 14:00 - two o'clock # in the afternoon. # # Written by: # John Rector # Jet Propulsion Laboratory # jar@next1.jpl.nasa.gov # # History: # June, 1995 1.0 - initial release # # Version: # 1.0 package sleepTill; sub tomorrow { # Store the time at which the program should awake each day in $batchTime. $batchTime = @_[0]; # In seconds for today, what time is it now? ($sec, $min, $hour) = localtime (time); $now = $min * 60 + $hour * 3600; # In seconds for the day, when do we start? unless (defined $then) { $batchTime =~ /(\d+):(\d+)/; $then = $2 * 60 + $1 * 3600; } # Compute the number of seconds to sleep for now til then; then # being the next time a batch job should start. Sleep for that # number of seconds. if ($now > $then) { $seconds = 86400 - $now + $then; } else { $seconds = $then - $now; } $seconds = 5; # for testing only. sleep $seconds; } 1; # Needed so the require function works. Package: mail.pl package mail; sub init { open (LOG, ">$_[0]") || die "Can't open $_[0]: $!\n"; $useLog = 1; } sub message { chop ($date = `date`); print LOG "\n", $date, " $_[0]" if defined $useLog; open (MESSAGE, "| mail -s \"filterProc.p failure report\" jar@next1"); print MESSAGE "$msgString"; close MESSAGE; } sub wrapUp { close LOG if defined $useLog; }

Perl Script: batch

#! /usr/local/bin/perl
#
# Usage:
#   batch targetDatabase startAt [filesLaterThan]
#
# Description:
#    Retrieves file names, filter values and ert times from gllSsiEdr
#    for all files with a receivedAt time value greater than the value
#    of filesLaterThan. To retrieve the database information the
#    package dbqServer is used. For each row returned, the program testProc
#    is invoked with the row's parameters as testProc command line arguments.
#    Once processing is complete, batch sleeps untill startAt time arrives
#    the following day. Sleep is done in the package sleepTill.
#
#    The format for the argument startAt is:
#
#        :
#
#    Where hours is for a 24 hour clock. For example: 2:00 is two o'clock in
#    the morining and 14:00 is two o'clock in the afternoon.
#
# The format for filesLaterThan is:
#
#        mm/dd/yyyy [hh/mm/ss]
#
# for example:
#
#        6/12/1995
#        6/12/1995 12:00:30
#
#
# Written by:
#    John Rector
#    Jet Propulsion Laboratory
#    jar@next1.jpl.nasa.gov
#
# History:
#    June, 1995        1.0 - initial release
#
# Version:
#    1.0

# Define the signals handled by this program.

$SIG{'INT'} = 'sigHandler';
$SIG{'TERM'} = 'sigHandler';

# Add the name of the directory where Perl Packages (libraries) are located
# at the beginning of the Perl standard array @INC. The require statment
# allows us to access subroutines in the dbqServer and sleepTil packages.

unshift (@INC, "/home/jar/Perl/Dbq");
require "dbqServer.pl" || die "Can't locate dbqServer.pl package\n";
require "sleepTill.pl" || die "Can't locate sleepTill.pl package\n";
require "mail.pl" || die "Can't locate mail.pl package\n";

# Check the number of parameters supplied on the command line.

if ($#ARGV < 1) {
    print "\nToo few arguments supplied on command line.\n";
    print "Usage: $0 targetDatabase startAt [filesLaterThan]\n\n";
    exit 1;
}

# If the $filtesLaterThan values was supplied on the command line, use it.
# Otherwise set the time to Jan 1, 1990, i.e., get all the files currently
# defined for the file type. Notice that $filesLaterThan is used in
# our query. Whenever we execute the statement, the current value for
# $filesLaterThan will be used.

if (defined $ARGV[2]) {
    $filesLaterThan = $ARGV[2];
}
else {
    $filesLaterThan = "1/1/1900";
}
$sql =
"Select fileName, filter, ert, receivedAt
from gllSsiEdr
where receivedAt > \"$filesLaterThan\"";

# Define the log file we'll be using.

$log = "batch.log";

# Start a dbq process and have it connect to a database server. $ARGV[0]
# is the name of the database to use. Note the package name "dbqServer"
# in front of this subroutine name and those that follow.
# If something went wrong, $dbqServer'dbqPid should not be defined, so
# exit on that test.

&dbqServer'connect ("MIPSDB1", $ARGV[0]);
exit(1) unless (defined $dbqServer'dbqPid);
    
# Loop until a SIGINT or SIGTERM signal is received. At that time,
# $notDone is set to 0, or false, and the loop will exit.

$notDone = 1;
while ($notDone) {

    # sleep until the time supplied in $ARGV[1]. Then do some work.
    
    &sleepTill'tomorrow ($ARGV[1]);

    undef $cmd;
    while (1) {
        print "CMD> ";
        $input = ;
        last if $input =~ /^go/;
        last if $input =~ /^exit/;
        $cmd .= $input;
    }
    last if $input =~ /^exit/;

    # Execute the query. If @dbqServer'rows is defined, get the values
    # for each row returned and process it. If an error occured. write
    # it to the log file.
    # Note: when a row is returned, a new value is assigned to
    # $filesLaterThan which is defined in our query command ($sql).
    # The next time the query is run, the last value returned will
    # be used, so only files later than that value are returned.
    
    &dbqServer'cmd ($cmd);
    if (defined @dbqServer'rows) {
        foreach $row (@dbqServer'rows) {
            ($fileName, $filter, $ert, $filesLaterThan) = split ('\t', $row);

            $message = "batch.p: Processing file: $fileName\n";
            &mail'message ($message);
                
            system ("testProc", "$fileName", "$filter", "$ert");
            if ($? >> 8) {
                $message = "testProc failed with status: " . ($? >> 8);
                &mail'message ($message);
            }
        }
    }
    elsif (defined @dbqServer'error) {
            print LOG @dbqServer'error;
    }
}

$dbqServer'disconnect;
exit 0;

#######################
# subroutine sigHandler
#######################

# Captures SIGINT and SIGTERM. Sets the main loop variable, so the program
# exits on the next pass through the loop.

sub sigHandler {
    $notDone = 0;
}


Copyright © 1997 The California Institute of Technology