Hi,
I am trying to use mysql select query in a shell script but i need to know how to store a particular value from the output of the query in a script variable.
my script file is given below.
I need to store the value under field CheckSum in the script variable $CHECKSUM_ORIG .There is only 1 entry in the db. The line
CHECKSUM_ORIG=$sqlquery;
doesnt work and gives a error. Please let me know the correct syntax/how to do it. If you have any link to a book on that u can share it as this is the 1st time i m using sql from scripts..
The program below is supposed to calculate the current check sum of a file and compare it with the checksum value already stored in mysql. If the value is same that means that the file is not corrupted.

        #!/bin/sh
        # Run using ./computeChecks.sh <filename>
        # Shell script to compare cksum difference between two files 

        sqluser="root"
        sqlpasswd=""
        db="CHECKSUM_DB"

        FILE=$1
        CHECKSUM_ORIG=""

        start () {

            CHECKSUM=`cksum "$FILE" | cut -f1 -d" "`

            sqlquery="select CheckSum from DbCheckSumTbl where DbName='$FILE'"
            #echo -e "$sqlquery"

            if [ -n "$sqlpasswd" ]; then
            connect=" mysql -u "$sqluser" -p "$sqlpasswd" "
            else
            connect=" mysql -u "$sqluser" "
            fi
            eval $connect <<EOF
            use $db;
            CHECKSUM_ORIG=$sqlquery; 
        EOF
        #**** the line b4 EOF doesnt work *****#
        #CHECKSUM_ORIG=$sqlquery;
        #CHECKSUM_ORIG=`$CHECKSUM_ORIG| cut -f1 -d ""`
        #echo $CHECKSUM_ORIG
        #echo $CHECKSUM

            if [ "$CHECKSUM_ORIG" != "$CHECKSUM" ]; then
                    echo "corrupted file: $FILE"
                    exit 0
            else
                    echo "file ok: $FILE"
                    exit 1
            fi

        }
        start

hi,

that should work better

#!/bin/sh
# Run using ./computeChecks.sh <filename>
# Shell script to compare cksum difference between two files

sqluser="root"
sqlpasswd=""
db="CHECKSUM_DB"
file="$1"
checksum=$(cksum "$file")
checksum="${cheksum##* }"
sqlquery="select CheckSum from DbCheckSumTbl where DbName=\"$file\""

connect(){ mysql -u "$sqluser";}
test -z "$sqlpasswd" && connect(){  connect -p "$sqlpasswd";}
checksum_orig=$(printf '%s\n' "use $db" "$sqlquery" | connect)

if [ "$checksum_orig" != "$checksum" ]; then
   echo "corrupted file: $file"
   exit 0
else
   echo "file ok: $file"
   exit 1
fi

don't use uppercase variable names, they are for environmental variables only
don't put commands in variables, use functions
don't use external command (cut) to remove matching prefix pattern

How do you verify for negative cases like the return values for connect (if password is given wrong) and if the sql query fails?? Means how do u check for return values??
That $sqlquery will return the column name (CheckSum) and its value, I used cut so that only the value is returned.
On running the script using bash -x i get the following trace...

checksum_orig='CheckSum
1616473790'

i.e., checksum_orig is containing both column name and field. So u might need to do some more modifications..

Also do let me know of any useful ebooks/links on this topic..

IIRC, mysql has an option so column headers are hidden.

if something goes wrong with connect, then "$checksum_orig" != "$checksum"
there, if $checksum_orig is not a number, then it's probably an error message from mysql; isn't it?

I have modified the sql syntax as ...

    result=`mysql -u $sqluser $db -e "select CheckSum from DbCheckSumTbl where DbName=\"$FILE\""`

    CHECKSUM_ORIG=`echo $result |cut -f2 -d ' '`

and the value is getting stored properly. Do let me know if there is a better way.
The return status can be checked using ...

if [ $? -eq 0 ]; then
echo "Success"
else
echo "Failure"
fi

if something goes wrong with connect, then "$checksum_orig" != "$checksum"
there, if $checksum_orig is not a number, then it's probably an error message from mysql; isn't it?

$checksum_orig and $checksum both variables are strings. so when the query fails, $checksum_orig will contain ntg and the prog will report the file as corrupted.

Also there is a error in the line . whose trace is given under that.
checksum_orig=$(printf '%s\n' "use $db" "$sqlquery" | connect)

  • sqluser=root
  • sqlpasswd=
  • db=CHECKSUM_DB
  • file=test.db
    ++ cksum test.db
  • checksum='134664090 92 test.db'
  • checksum='134664090 92 test.db'
  • sqlquery='select CheckSum from DbCheckSumTbl where DbName="test.db"'
  • test -z ''
    ++ printf '%s\n' 'use SGRAN_CHECKSUM_DB' 'select CheckSum from DbCheckSumTbl where DbName="test.db"'
    ++ connect
    ++ connect -p ''
    ++ connect -p ''
    ++ connect -p ''
    ..

tq.

Do let me know if there is a better way.
man mysql says: --skip-column-names, -N

Also there is a error in the line
that should be:

test -n "$sqlpasswd" && connect(){ connect -p "$sqlpasswd";}

using -n for "$mysqlpasswd" exists, and is non empty.

$checksum_orig and $checksum both variables are strings
cksum prints two numbers (CRC checksum, and number of bytes in the file), and the file name.

thanks for your help..

one more query ..

result=`$connect $db -e "select CheckSum from DbCheckSumTbl where DbName=\"$FILE\""`

works, but ..

sqlquery="select CheckSum from DbCheckSumTbl where DbName=\"$FILE\""
result=`$connect $db -e $sqlquery`

doesnt work .. any bright ideas??

quote sqlquery variable

result=`$connect $db -e "$sqlquery"`

thanks!.. that works ..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.