LinuxQuestions.org - building shell command piece by piece

- Linux - General (https://www.linuxquestions.org/questions/linux-general-1/)

- - building shell command piece by piece (https://www.linuxquestions.org/questions/linux-general-1/building-shell-command-piece-by-piece-434872/)

building shell command piece by piece

I'm trying to put together a shell script to query a mysql database, with the WHERE section being based on the shell arguments. It seemed like the easiest thing to do would be to make a string variable of the full mysql command, piece by piece, and then execute it at the end of the script.

Well, it didn't work. So I tried it with other commands. Same story. To prevent overcomplicating what is probably a simple problem of ignorance, I'll use my second test in the example below:

Code:

#!/bin/sh



CMD="grep";



if [ -z "$1" ]

then

  CMD="$CMD"" 'foobar'";

else

  CMD="$CMD"" '$1'";

fi



CMD="$CMD"" /var/log/somerandom.log";



$CMD;



exit 1;

There's easier ways to accomplish the function of this example, I know. But it was the easist way to build my mysql query. I think what I'm trying to do is pretty well conveyed.

I learned about 'sh -x' this morning, so I thought I'd try it. I'm not sure if this helps or not, but here it is:

Quote:

~$ sh -x ./test foo
+ CMD=grep
+ '[' -z foo ']'
+ CMD='grep '\''foo'\'''
+ CMD='grep '\''foo'\'' /var/log/somerandom.log'
+ grep ''\''foo'\''' /var/log/somerandom.log
+ exit 1

If I 'echo "$CMD;"', and then copy/paste the command, it works great, both in the test and the mysql script. But when I execute $CMD, the mysql script outputs the 'mysql --help' info, and the grep script just takes me back to the prompt.

Can someone tell me what I'm doing wrong?

Hi,

There are probably better ways to solve your original problem (executing a mysql statement from within a script), HERE documents being the first that comes to mind.

But, you can do it the way you tried, only thing you did wrong was the quoting:

Code:

#!/bin/sh



CMD="grep"



if [ -z "$1" ]

then

  CMD="$CMD foobar"

else

  CMD="$CMD $1"

fi



CMD="$CMD /var/log/somerandom.log"



$CMD



exit 0

Hope this helps.

Hmmm, then I guess my grep test doesn't demonstrate the same problem. Maybe I just need to approach this with a different method.

While I google for 'HERE documents', here's the basic gist of what I was trying to do. If you can give me a quick example of how I could do this better, that would be sweet.

Code:

# initiate the $CMD variable

CMD="mysql --user=\"usernam3\" --password=\"pa55word\" --database=\"testdb\" --execute=\"SELECT * from footable where";



LIMIT=15;



# now loop through the arguments and add the appropriate wheres

while [ -n "$1" ]; do

case $1 in



  -l | --limit)

    if [ -z "$2" ]

    then

      missing;

      shift;

    else

    LIMIT="$2";

    shift 2;

    fi;;



  --field1)

    if [ -z "$2" ]

    then

      missing;

      shift;

    else

    AND="$AND field1 like '%$2%' and ";

    shift 2;

    fi;;



  --field2)

    if [ -z "$2" ]

    then

      missing;

      shift;

    else

    AND="$AND field2 like '%$2%' and ";

    shift 2;

    fi;;



##### there's several more of these field1/field2 things #####



  -*) invalid;;

  *) break;;

esac

done



# add the wheres to the $CMD variable and finish it

CMD="$CMD $AND id is not null order by date desc LIMIT $LIMIT\";";



# execute it

$CMD;



exit 1;

Hi,

Basic mysql from script using a HERE document:

Code:

#!/bin/bash



some_table="footable"

other_table="handable"

token="somestring"

sortorder="whatever"



mysql <<HERE

<usernaem>/<passwd>

select * from ${some_table}

      where ${other_table} like '${token}' 

      order by ${sortoder};

quit

exit

HERE

You can set variables outside the HERE document and use them inside the HERE document.

Hope this helps.

Hi again,

Took a better look at your example and came up with this code snippet (HERE document part only):

Code:

fieldOne="first"

fieldTwo="last"

limit="15"



mysql --user=\"usernam3\" --password=\"pa55word\" --database=\"testdb\" <<MYSQL

select * 

      from footable

      where field1 like ${fieldOne} 

      and field2 like ${fieldTwo}

      and id is not null

      order by date desc  

      LIMIT ${limit};

MYSQL

A few points:
- You could place the username/password inside the HERE document, but you don't have to.
- fieldOne, fieldTwo and limit need to be checked/set before starting the mysql command. I hardcoded them to simplify things.

Hope this clears things up some more.

Okay, I finally got back to getting this running, and it's handling the arguments gloriously.

But for some reason, it's stripping the table ascii (the "|"s, "-"s, etc.) from the output, which makes it difficult to read.

So, I naturally thought to run the output through awk, and my thought was to do this:

Code:

mysql --user="user2" --password="foopass" --database="footable" <<MYSQL

  SELECT ... LIMIT ${LIMIT};

quit

MYSQL | awk { /* awk script here */ }

exit 1

And, yeah. That's not having an effect... Any suggestions?

Hi,

It's been a while I used mysql, but one of these two does what you want (cannot remember which :) ).

mysql -t
or
mysql -vvv

Normally mysql only shows table layout when using it interactively (from the mysql prompt). All other output is given without the table layout. The -t (or -vvv) options tells mysql to to the table layout even if not interactive.

Hope this gets you going again.

That was it. Figures it would be something in mysql. I very rarely use the mysql command line, and it shows. :P

Thanks for all your help!