MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Thomas Spahni Date:February 11 2004 1:18pm
Subject:Re: There has to be a way to do this
View as plain text  
Mike,

you are close: you want the mysql client to give back just the data, no
column description. Change this line to read:

MYSQL="/usr/local/mysql/bin/mysql  --user=$username --password=$password
 --host=$server --skip-column-names cetechnology"

On Tue, 10 Feb 2004, Mike Tuller wrote:

> Ok. I think I am close to getting this. Here is what I have.
>
>
> MYSQL="/usr/local/mysql/bin/mysql  --user=$username --password=$password
> --host=$server cetechnology"
>
> RESULT=$(echo "select count(*) from hardware_assets where
> ethernet_address='$ethernet_address' " | $MYSQL)
>  if [ "$RESULT" = "0" ] ; then
>      echo "INSERT INTO hardware_assets (ethernet_address) VALUES
> ('$ethernet_address');" | $MYSQL
>  else
>      echo "UPDATE hardware_assets SET operating_system='10.3.3';" | $MYSQL
> fi
>
> echo $RESULT
>
> When I run this, it always does an update, and updates all records, not just
> the ones that are matching $ethernet_address. I added the 'echo $RESULT' to
> see what it was returning. It comes back with 'count(*) 1'. I change the if
> statement to read 'if["$RESULT" = "count(*) 0" and the value for
> $ethernet_address to a number that I do not have in the database. It does
> not add the new ethernet address, and updates all of the records.
>
> So that tells me that there is something wrong the value of $RESULT and the
> comparison in the if statement. Further, if I change the value of $RESULT to
> '12345' and change the if line to
> if [ "$RESULT" = "12345" ] ; then
> It adds a record to the database. So there is something wrong with what is
> returned. Neither "0" or "count(*) 0" seem to work. So, does anyone have an
> idea as to what I need to put in for the comparison?
>
>
> Mike
>
> > From: gerald_clark <gerald_clark@stripped>
> > Date: Mon, 09 Feb 2004 14:28:27 -0600
> > To: Mike Tuller <mtuller@stripped>
> > Cc: MySql List <mysql@stripped>
> > Subject: Re: There has to be a way to do this
> >
> > This is NOT a script that can run under mysql.
> > It  is a bash script that calls mysql.
> >
> > MYSQL="/usr/local/mysql/bin/mysql  --user=$username --password=$password
> > --host=$server cetechnology"
> >
> > RESULT=`echo "select count(*) from hardware_assets where
> > ethernet_address='$ethernet_address'" | $MYSQL
> > if [ "$RESULT" = "0" ] ; then
> >   echo "INSERT INTO hardware_assets (ethernet_address) VALUES
> > ($ethernet_address);" |$MYSQL
> > else
> >   echo "UPDATE hardware_assets SET operating_system='10.3.3';" | $MYSQL
> > fi
> >
> >
> > Mike Tuller wrote:
> >
> >> I changed my script to this:
> >>
> >> /usr/local/mysql/bin/mysql  --user=$username --password=$password
> >> --host=$server
> >>
> >> RESULT=`echo "select count(*) from hardware_assets where
> >> ethernet_address='$ethernet_address'" | cetechnology'
> >> if [ "$RESULT" = "0" ] ; then
> >>    echo "INSERT INTO hardware_assets (ethernet_address) VALUES
> >> ($ethernet_address);"
> >> else
> >>    echo "UPDATE hardware_assets SET operating_system='10.3.3';"
> >> fi
> >>
> >> Where cetechnology is the database. All the variables are set.
> >>
> >> When I run this, it starts the mysql client application,  with the
> mysql>
> >> prompt. Nothing is inserted or updated in the database though.
> >>
> >> This is the same problem I had when I tried to do it this way, but I am not
> >> knowledgeable in shell scripting yet to know what I am doing wrong.
> >>
> >>
> >>
> >>
> >>
> >>
> >>> From: gerald_clark <gerald_clark@stripped>
> >>> Date: Mon, 09 Feb 2004 11:11:24 -0600
> >>> To: Mike Tuller <mtuller@stripped>
> >>> Cc: MySql List <mysql@stripped>
> >>> Subject: Re: There has to be a way to do this
> >>>
> >>> IF works on the selections not on the query.
> >>> Select  IF(lastname='clark','Correct',''Incorrect'), firstname from
> >>> namefile;
> >>>
> >>> You need to do the checking in your script.
> >>> For example in bash:
> >>> RESULT=`echo "select count(*) from manefile where lastname='clark'" |
> >>> mysql database`
> >>> if [ "$RESULT" = "0" ] ; then
> >>>  echo "insert into namefile .......
> >>> else
> >>>  echo "update namefile ......
> >>> fi
> >>>
> >>>
> >>> Mike Tuller wrote:
> >>>
> >>>
> >>>
> >>>> I have posted this question a few times, and have not seen the
> answer that
> >>>> I
> >>>> need.
> >>>>
> >>>> I have a shell script, that gathers information from systems, and I
> want
> >>>> that info to be entered into a database. I want it to check first to
> see if
> >>>> the data is already entered, and if not, add it. If it has already
> been
> >>>> entered, then update the record.
> >>>>
> >>>> I would think that some type of if/else statement would work, but I
> can't
> >>>> get the IF statement http://www.mysql.com/doc/en/IF_Statement.html
> to work
> >>>> correctly in MySql.
> >>>>
> >>>> Here is what I have:
> >>>>
> >>>> "IF SELECT * FROM hardware_assets WHERE
> >>>> ethernet_address='$ethernet_address'
> >>>> IS NULL\
> >>>>   THEN INSERT into hardware_assets (ethernet_address) VALUES
> >>>> ($ethernet_address)\
> >>>> ELSE\
> >>>>   UPDATE hardware_assets SET operating_system='10.3.3'\
> >>>> END IF;"
> >>>>
> >>>> I get back that I have an error in my SQL syntax. $ethernet_address
> is set,
> >>>> so that is not my problem.
> >>>>
> >>>> Does anyone know a way to go about this in SQL, or in a shell
> script? I
> >>>> don't want to do it in Perl or PHP.
> >>>>
> >>>>
> >>>> Thanks,
> >>>> Mike
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>
> >>> --
> >>> MySQL General Mailing List
> >>> For list archives: http://lists.mysql.com/mysql
> >>> To unsubscribe:
> >>> http://lists.mysql.com/mysql?unsub=1
> >>>
> >>>
> >>>
> >>
> >>
> >>
> >>
> >
> >
>
>
>

Thread
There has to be a way to do thisMike Tuller9 Feb
  • Re: There has to be a way to do thisvpendleton9 Feb
  • Re: There has to be a way to do thisgerald_clark9 Feb
    • Re: There has to be a way to do thisMike Tuller9 Feb
      • Re: There has to be a way to do thisgerald_clark9 Feb
        • Re: There has to be a way to do thisMike Tuller9 Feb
        • Re: There has to be a way to do thisMike Tuller10 Feb
          • Re: There has to be a way to do thisgerald_clark10 Feb
          • Re: There has to be a way to do thisEamon Daly10 Feb
          • Re: There has to be a way to do thisThomas Spahni11 Feb
      • Re: There has to be a way to do thisRyan Yagatich9 Feb
        • Re: There has to be a way to do thisRyan Yagatich10 Feb
  • Re: There has to be a way to do thisAlec.Cawley9 Feb
RE: There has to be a way to do thisEvelyn Schwartz9 Feb