Writing a Front End for Claims_DB Part 4 - add_process.sh

Tutorial Part 3 - add_record.sh

In the previous section of this tutorial we created a page allowing a user to insert a record into the database. Now we need to create the script that actually processes the input and inserts the record into the database.

This script will be the first time we have called insert_records_claim.

As ever we need to start by generating our script headers

nano add_process.sh

#!/bin/bash
#
# add_process.sh
#
# Part 
of the Claims_DB Sample DB Front End
#
# Copyright Ben Tasker 2009
# Released under the GNU GPL
# 
See http://benscomputer.no-ip.org/LICENSE
#

# Define your variables here

# Where did you 
install Claims_DB?
CLAIMS_PROGS="/home/ben/programs/Claims_DB/"

# The name of the Database we are 
using
DBNAME="Sample_DB"



# Tell the browser our MIME Type
echo Content-type: 
text/html
echo ""

This time we need to process the details sent to us. As the form method in the previous page was set to use GET we can grab this from the Request URI.

# Run /bin/env to get environment 
details
REQUEST_URI=$( /bin/env | grep "REQUEST_URI" )

# Now we need to seperate out each of the 
elements.

# Primary Key
RECORDID=$( echo "$REQUEST_URI" | sed -n 's/^.*RecordID=\([^&]*\).*$/\1/p' 
)

# Description
DESCRIPTION=$( echo "$REQUEST_URI" | sed -n 's/^.*Desc=\([^&]*\).*$/\1/p' 
)

# Part Location
LOCATION=$( echo "$REQUEST_URI" | sed -n 's/^.*Locat=\([^&]*\).*$/\1/p' 
)

# Part Number
PARTNO=$( echo "$REQUEST_URI" | sed -n 's/^.*Partno=\([^&]*\).*$/\1/p' )

# 
Number Type
NUMBERTYPE=$( echo "$REQUEST_URI" | sed -n 's/^.*NoType=\([^&]*\).*$/\1/p' )

# 
STOCKIST
STOCKIST=$( echo "$REQUEST_URI" | sed -n 's/^.*Stkis=\([^&]*\).*$/\1/p' )

# Quantity in 
pack
PPQ=$( echo "$REQUEST_URI" | sed -n 's/^.*PPQ=\([^&]*\).*$/\1/p' )

# Quantity on 
bike
QOB=$( echo "$REQUEST_URI" | sed -n 's/^.*Need=\([^&]*\).*$/\1/p' )

# Rating
RATING=$( 
echo "$REQUEST_URI" | sed -n 's/^.*Rate=\([^&]*\).*$/\1/p' )

# Bike
BIKE=$( echo "$REQUEST_URI" | 
sed -n 's/^.*bike=\([^&]*\).*$/\1/p' )

Now although we have all the information we need, we probably don't want to insert it into the database yet. The information is currently URL Encoded, and Number type still uses the coding that we gave it on the last form.

We probably could insert it into the database like this and then remove the encoding when we retrieve the information at a later date, but it does mean that should we open the table in a spreadsheet program it's going to be more or less useless to us. Let's be tidy and do the processing now, starting with the number type.

# Translate NUMBERTYPE into the value to be inserted 
into the database

if [ "$NUMBERTYPE" == "0" 
]
then
NUMBERTYPE="OEM"
else
NUMBERTYPE="Stockist"
fi

Next we'll remove URL Encoding from the variables that are likely to contain it. NUMBERTYPE won't as we have just set it, PPQ and QOB should both be numeric so they don't need changing. Everything else could contain spaces or characters, so lets run it through a list of all likely possibilities. (Note: Each of the following should occupy one line)

DESCRIPTION=$( echo "$DESCRIPTIONA" | sed 
"s/%0D%0A/<br>/g" | sed "s/+/ /g" | sed "s/%21/\!/g" | sed "s/%40/@/g" | sed "s/%20/ /g" | sed 
"s/%26/\&/g" | sed "s/%3B/[SEMICOLON]/g" | sed "s/%22/[DOUBLEQUOTES]/g" | sed "s/%2F/\//g" | sed 
"s/%5C/\\\/g" | sed "s/%24/$/g" | sed "s/%A3/?/g" | sed "s/%27/\'/g" | sed "s/%23/\#/g" | sed "s/%3A/\:/g" | sed 
"s/%5B/\[/g" | sed "s/%40/@/g" | sed "s/%5D/\]/g" | sed "s/%25/\%/g" | sed "s/%5E/\^/g" | sed "s/%28/(/g" | sed 
"s/%29/)/g" | sed "s/%2B/+/g" | sed "s/%2C/,/g" )
LOCATION=$( echo "$LOCATIONA" | sed "s/%0D%0A/<br>/g" 
| sed "s/+/ /g" | sed "s/%21/\!/g" | sed "s/%40/@/g" | sed "s/%20/ /g" | sed "s/%26/\&/g" | sed 
"s/%3B/[SEMICOLON]/g" | sed "s/%22/[DOUBLEQUOTES]/g" | sed "s/%2F/\//g" | sed "s/%5C/\\\/g" | sed "s/%24/$/g" | 
sed "s/%A3/?/g" | sed "s/%27/\'/g" | sed "s/%23/\#/g" | sed "s/%3A/\:/g" | sed "s/%5B/\[/g" | sed "s/%40/@/g" | 
sed "s/%5D/\]/g" | sed "s/%25/\%/g" | sed "s/%5E/\^/g" | sed "s/%28/(/g" | sed "s/%29/)/g" | sed "s/%2B/+/g" | 
sed "s/%2C/,/g" )
PARTNO=$( echo "$PARTNOA" | sed "s/%0D%0A/<br>/g" | sed "s/+/ /g" | sed "s/%21/\!/g" 
| sed "s/%40/@/g" | sed "s/%20/ /g" | sed "s/%26/\&/g" | sed "s/%3B/[SEMICOLON]/g" | sed 
"s/%22/[DOUBLEQUOTES]/g" | sed "s/%2F/\//g" | sed "s/%5C/\\\/g" | sed "s/%24/$/g" | sed "s/%A3/?/g" | sed 
"s/%27/\'/g" | sed "s/%23/\#/g" | sed "s/%3A/\:/g" | sed "s/%5B/\[/g" | sed "s/%40/@/g" | sed "s/%5D/\]/g" | sed 
"s/%25/\%/g" | sed "s/%5E/\^/g" | sed "s/%28/(/g" | sed "s/%29/)/g" | sed "s/%2B/+/g" | sed "s/%2C/,/g" 
)
STOCKIST=$( echo "$STOCKISTA" | sed "s/%0D%0A/<br>/g" | sed "s/+/ /g" | sed "s/%21/\!/g" | sed 
"s/%40/@/g" | sed "s/%20/ /g" | sed "s/%26/\&/g" | sed "s/%3B/[SEMICOLON]/g" | sed "s/%22/[DOUBLEQUOTES]/g" 
| sed "s/%2F/\//g" | sed "s/%5C/\\\/g" | sed "s/%24/$/g" | sed "s/%A3/?/g" | sed "s/%27/\'/g" | sed "s/%23/\#/g" 
| sed "s/%3A/\:/g" | sed "s/%5B/\[/g" | sed "s/%40/@/g" | sed "s/%5D/\]/g" | sed "s/%25/\%/g" | sed "s/%5E/\^/g" 
| sed "s/%28/(/g" | sed "s/%29/)/g" | sed "s/%2B/+/g" | sed "s/%2C/,/g" )
RATING=$( echo "$RATINGA" | sed 
"s/%0D%0A/<br>/g" | sed "s/+/ /g" | sed "s/%21/\!/g" | sed "s/%40/@/g" | sed "s/%20/ /g" | sed 
"s/%26/\&/g" | sed "s/%3B/[SEMICOLON]/g" | sed "s/%22/[DOUBLEQUOTES]/g" | sed "s/%2F/\//g" | sed 
"s/%5C/\\\/g" | sed "s/%24/$/g" | sed "s/%A3/?/g" | sed "s/%27/\'/g" | sed "s/%23/\#/g" | sed "s/%3A/\:/g" | sed 
"s/%5B/\[/g" | sed "s/%40/@/g" | sed "s/%5D/\]/g" | sed "s/%25/\%/g" | sed "s/%5E/\^/g" | sed "s/%28/(/g" | sed 
"s/%29/)/g" | sed "s/%2B/+/g" | sed "s/%2C/,/g" )
BIKE=$( echo "$BIKEA" | sed "s/%0D%0A/<br>/g" | sed 
"s/+/ /g" | sed "s/%21/\!/g" | sed "s/%40/@/g" | sed "s/%20/ /g" | sed "s/%26/\&/g" | sed 
"s/%3B/[SEMICOLON]/g" | sed "s/%22/[DOUBLEQUOTES]/g" | sed "s/%2F/\//g" | sed "s/%5C/\\\/g" | sed "s/%24/$/g" | 
sed "s/%A3/?/g" | sed "s/%27/\'/g" | sed "s/%23/\#/g" | sed "s/%3A/\:/g" | sed "s/%5B/\[/g" | sed "s/%40/@/g" | 
sed "s/%5D/\]/g" | sed "s/%25/\%/g" | sed "s/%5E/\^/g" | sed "s/%28/(/g" | sed "s/%29/)/g" | sed "s/%2B/+/g" | 
sed "s/%2C/,/g" )

All our variables should now be ready for insertion into the database, so lets put them in.

# Insert the record into the 
DatabaseCOL1="$RECORDID" COL2="$DESCRIPTION" COL3="$LOCATION" COL4="$PARTNO" COL5="$NUMBERTYPE" COL6="$STOCKIST" 
COL7="$PPQ" COL8="$NEED" COL9="$RATING" COL10="$BIKE" DBROOT="$DBNAME" TABLE="Catalogue" 
"$CLAIMS_PROGS"/bin/insert_record_claims.sh -insert > /tmp/STATUS.tmp

STATCODE=$( cat 
/tmp/STATUS.tmp )

Now we are going to generate some HTML to tell the user whether we were successful or not. To save a few lines of code, we'll generate the HTML header and then process the event.

# Generate the HTML header
/bin/cat << EOM
<html><head><title>Claims_DB Sample Database Front End</title></head>
<body bgcolor="white><center>Claims DB Sample Database Front End - Add Record Stage 
2</center><br><br>
EOM

Now lets check whether the insertion was successful. If it was then Claims_DB will have returned the status "SUCCESS", if it returns anything else then something went wrong.

# Check whether it worked
if [ "$STATCODE" != "SUCCESS" ]
then
# Something went wrong
/bin/cat << EOM
Your record was <b><font color="red">NOT</font></b> inserted into the Database due to an 
error. The status code returned by the database was<br>
$STATCODE<br><br>
Please report this error to your Database 
Administrator.<br><br>
Sorry for the inconvenience.<br><br>
<a href="/Index.sh">Return to the Index Page</a>
EOM


else
# It worked!

/bin/cat << EOM 

Your record <b><font color="green">WAS</font></b> 
inserted into the database.<br><br>
<a href="/Index.sh">Return to the Index 
Page</a>
<br><br>
EOM

fi


# Generate the HTML footer

/bin/cat << EOM
</body></html>
EOM

# Tidy up and exit - make sure no errors are sent to the browser
rm -f /tmp/STATUS.tmp 2> /dev/null
exit

With that we have a script that process the input sent to it from our add_records form. Again, aesthetically not the greatest work in the world, but the necessary functionality is there for you to expand upon.

You can download a copy of this source here.


Tutorial Part 5 - gen_report.sh