building shell command piece by piece

aunquarra · 04-13-2006, 01:46 PM

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?

druuna · 04-14-2006, 12:08 PM

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.

aunquarra · 04-17-2006, 07:46 AM

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;

druuna · 04-17-2006, 08:38 AM

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.

druuna · 04-17-2006, 09:08 AM

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.

aunquarra · 04-26-2006, 07:26 AM

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?

druuna · 04-26-2006, 09:23 AM

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.

aunquarra · 05-02-2006, 06:43 AM

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!