Re: There has to be a way to do this

gerald_clark Tue, 10 Feb 2004 11:07:11 -0800

Why do you keep changing the script?

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

RESULT=`echo "se;ect count(*) from hardware_assets where ethernet_address='$ethernet_address' " | $MYSQL` the first and last quotes are actually back-ticks ( to the left of the `1' Key )

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 <[EMAIL PROTECTED]>
Date: Mon, 09 Feb 2004 14:28:27 -0600
To: Mike Tuller <[EMAIL PROTECTED]>
Cc: MySql List <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]>
Date: Mon, 09 Feb 2004 11:11:24 -0600
To: Mike Tuller <[EMAIL PROTECTED]>
Cc: MySql List <[EMAIL PROTECTED]>
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/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Re: There has to be a way to do this

Reply via email to