LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - General
User Name
Password
Linux - General This Linux forum is for general Linux questions and discussion.
If it is Linux Related and doesn't seem to fit in any other forum then this is the place.

Notices

Reply
 
Search this Thread
Old 04-13-2006, 01:46 PM   #1
aunquarra
LQ Newbie
 
Registered: Jan 2005
Distribution: Slackware 10.1
Posts: 28

Rep: Reputation: 15
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?
 
Old 04-14-2006, 12:08 PM   #2
druuna
LQ Veteran
 
Registered: Sep 2003
Posts: 10,532
Blog Entries: 7

Rep: Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371
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.
 
Old 04-17-2006, 07:46 AM   #3
aunquarra
LQ Newbie
 
Registered: Jan 2005
Distribution: Slackware 10.1
Posts: 28

Original Poster
Rep: Reputation: 15
Smile

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;
 
Old 04-17-2006, 08:38 AM   #4
druuna
LQ Veteran
 
Registered: Sep 2003
Posts: 10,532
Blog Entries: 7

Rep: Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371
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.
 
Old 04-17-2006, 09:08 AM   #5
druuna
LQ Veteran
 
Registered: Sep 2003
Posts: 10,532
Blog Entries: 7

Rep: Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371
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.
 
Old 04-26-2006, 07:26 AM   #6
aunquarra
LQ Newbie
 
Registered: Jan 2005
Distribution: Slackware 10.1
Posts: 28

Original Poster
Rep: Reputation: 15
Thumbs up

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?
 
Old 04-26-2006, 09:23 AM   #7
druuna
LQ Veteran
 
Registered: Sep 2003
Posts: 10,532
Blog Entries: 7

Rep: Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371Reputation: 2371
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.
 
Old 05-02-2006, 06:43 AM   #8
aunquarra
LQ Newbie
 
Registered: Jan 2005
Distribution: Slackware 10.1
Posts: 28

Original Poster
Rep: Reputation: 15
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!
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Chess Piece Icons ninjabob7 Programming 5 11-24-2005 12:51 PM
retrievin a certain piece of text rharris72 Programming 3 11-22-2005 05:41 PM
Looking for a piece of software jkruer01 Linux - Software 5 07-23-2004 03:52 PM
what is this funny piece of hardware? andzerger Linux - Hardware 3 03-10-2004 10:33 PM
Piece of Linksys Cra*! cclavey Linux - Newbie 21 06-05-2003 02:21 AM


All times are GMT -5. The time now is 09:33 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration