Using mysql select in shell scripts

[srinivas88]

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

[Watael]

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

[srinivas88]

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..

[Watael]

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?

[srinivas88]

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.

[Watael]

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.

[srinivas88]

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??

[Watael]

quote sqlquery variable

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

[srinivas88]

thanks!.. that works ..