1

Writing a quick script for a temporary/repetitive task. Wrote a basic solution that works:

for thing in "$@";
        do
                /usr/mysql/bin/mysql -u xyz -p pdq <<END;
                UPDATE table
                SET table_atr = 'NW'
                WHERE record_id = $thing
END
        done

This works but forces a password check for every member of argument array (not ideal).

Tried to update it to this:

if {$# -le 1}; then
        for thing in "$@";
        do
                /usr/mysql/bin/mysql -u xyz -p pdq <<END;
                UPDATE table
                SET table_atr = 'NW'
                WHERE record_id = $thing
END
        done
else
        things = ""

        for thing in "$@";
        do
                things += "$thing"

                if {$thing == $@[$# - 1]}; then
                        things += "\n"
                        continue
                else
                        things += ",\n"
        done

        /usr/mysql/bin/mysql -u xyz -p pdq <<END;
        UPDATE table
        SET table_atr = 'NW'
        WHERE record_id IN
        (
                $things
        )
END

TLDR: If there is more than one argument: do a for loop to fill a WHERE .. IN () statement. I realize this doesn't even need to be a multiline string and maybe that is my issue but the error I'm getting is (apparently) unrealted .

The error I get is:

line 24: syntax error near unexpected token' done'

line 24: ' done'

Neither I, nor my supervisor have much experience with shell scripts but I cannot see any syntax error with the 2nd for loop. Its exactly the same as the first which executes fine.

Any help is greatly appreciated, I may just have to go to the basic version or write this as a Perl script instead. Thanks!

MagicGAT
  • 135
  • 7
  • 1
    FYI you don't need `continue` – Barmar Apr 28 '21 at 21:47
  • 5
    You're missing `fi` at the end of `if/else`. It has nothing to do with multiline strings. – Barmar Apr 28 '21 at 21:48
  • 1
    See https://stackoverflow.com/questions/1527049/how-can-i-join-elements-of-an-array-in-bash for some simpler ways to join an array with commas in bash. – Barmar Apr 28 '21 at 21:50
  • 2
    Also note that assignments in bash do not allow spaces. `a+=b` works but `a += b` is something completely different ("command" `a` with arguments `+=` and `b`). Use https://www.shellcheck.net/ to find errors like these. – Socowi Apr 28 '21 at 21:54
  • Lots of really good information here. Thank you very much – MagicGAT Apr 28 '21 at 22:11
  • 3
    There are many shell syntax errors here. For one thing, `{$# -le 1}` isn't how you do a test; use `[ $# -le 1 ]` (note that spaces are critical delimiters -- don't add or remove them unless you know what you're doing). Also, `"\n"` doesn't get you a newline, just a backslash followed by "n". If you want a newline, use `$'\n'` (the `$'...'` mode interprets ANSI C escape sequences). – Gordon Davisson Apr 28 '21 at 22:12
  • 1
    You're definitely on the right track with concatenating an `in` list. The `where elem in ( ... )` I'm familar with would choke (I think ) on multiline intput, just make it `elem in ('a','b','c','d',....)`. Getting the single quotes into a string can be tricky. Look up the ASCII code and use a printf with an octal value for the single-quotes. Good luck! – shellter Apr 28 '21 at 23:59
  • 1
    With everyone's advice i've gotten bash to stop throwing errors, just an issue with mysql syntax now. Should be able to get it working completely tomorrow. Thanks – MagicGAT Apr 29 '21 at 00:11
  • 1
    @MagicGAT : Your question still exhibits the bash errors GordonDavisson mentioned. Plesae edit your question, or delete your question and ask a new one. If you you post code, copy and paste exactly the code you are actually using. Otherwise it is hard to discusse the issue. – user1934428 Apr 29 '21 at 07:37
  • 1
    Use [mysql-config-editor](https://dev.mysql.com/doc/refman/5.7/en/mysql-config-editor.html) to save your password securely so that you don't need to type it at all. – chepner Apr 29 '21 at 11:50

1 Answers1

0

Thanks everyone for all the advice. This was an edit that worked:

#!/bin/sh

if [ $# -le 1 ]; then
        for thing in "$@";
        do
                /usr/mysql/bin/mysql -u xyz -p pdq <<END;
                UPDATE table
                SET table_atr = 'NW'
                WHERE record_id = $thing
END
        done
else
        things=""
        i=1

        for thing in "$@";
        do
                things+="$thing"

                if [ $i -eq $# ]; then
                        things+=""
                else
                        things+=", "
                fi

                ((i+=1))
        done

        /usr/mysql/bin/mysql -u xyz -p pdq <<END;
        UPDATE table
        SET table_atr = 'NW'
        WHERE record_id IN ($things)
END
fi

There were indeed many syntax errors and changing the WHERE .. IN () string construction to a single line made this a lot easier. Luckily I didn't have to worry about inserting single quotes, mysql took the query without them.

I came out of this with a much higher respect for bash scripting. It is a serious language that requires its own study and I will approach it with much more attention to detail in the future.

Thanks again.

MagicGAT
  • 135
  • 7