Okay, the lack of locking was driving me crazy. Here's my
version:
#!/usr/bin/sh
MYSQL="mysql -v test"
ethernet_address=$1
cat <<EOF | $MYSQL
LOCK TABLES hardware_assets WRITE;
SELECT @asset_id := asset_id
FROM hardware_assets WHERE ethernet_address = '$ethernet_address';
REPLACE INTO hardware_assets
(asset_id, operating_system, ethernet_address)
SELECT @asset_id, IF(@asset_id, '10.3.3', NULL), '$ethernet_address';
UNLOCK TABLES;
EOF
This locks the hardware_assets table and gets the asset_id
of any exisiting asset with the specified ethernet address.
If there's an existing asset, we update its operating system
to '10.3.3', otherwise we create a new asset with the
specified ethernet address and no operating system. FYI,
this is wildly inefficient for runs of several ethernet
addresses, because it locks the table once for each run.
____________________________________________________________
Eamon Daly
NextWave Media Group LLC
Tel: 1 773 975-1115
Fax: 1 773 913-0970
----- Original Message -----
From: "Mike Tuller" <[EMAIL PROTECTED]>
To: "gerald_clark" <[EMAIL PROTECTED]>
Cc: "MySql List" <[EMAIL PROTECTED]>
Sent: Tuesday, February 10, 2004 10:56 AM
Subject: Re: There has to be a way to do this
> 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 <[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]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
