Re: There has to be a way to do this
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 [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
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]
Re: There has to be a way to do this
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
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
There has to be a way to do this
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]
Re: There has to be a way to do this
You could create a User defined Function that contains this logic. At the present time, an UDF needs to be coded in C. Depending on how your `users` interface with the application, you could write a C/C++ or java command line interface as well. Original Message On 2/9/04, 10:53:00 AM, Mike Tuller [EMAIL PROTECTED] wrote regarding There has to be a way to do this: 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
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]
RE: There has to be a way to do this
If you are always updating the entire row you could delete the record (ignoring failures) and then insert the record. Not efficient but it would work. If you are able to trap errors in your shell script and there is a unique index on the ethernet_address field then you can do this: Update Record If update fails Insert record -Original Message- From: Mike Tuller [mailto:[EMAIL PROTECTED] Sent: Monday, February 09, 2004 11:53 AM To: MySql List Subject: There has to be a way to do this 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
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
Mike Tuller [EMAIL PROTECTED] wrote on 09/02/2004 16:53:00:
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;
You probably want the REPLACE command (
http://www.mysql.com/doc/en/REPLACE.html )
which provides exactly this functionality:
REPLACE INTO hardware_assets VALUES ($ethernet_address, ) ;
where the ethernet_address column has been specified to be UNIQUE.
The MySQL IF is an operator, not a flow control structure: It would map
more closely to the C A?x:y operator rather than the if () then {} else
{}.
Alec
--
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
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
When I run this, it always updates the record, not matter what the value for $ethernet_address is. I have tested this by changing the line under else to echo Update Every time the script is run, it comes back with update. I then we back and commented out the whole if/else statement and had only the line for RESULT=, and then did echo $RESULT. It returns nothing. Not a 0 or a 1, nothing. 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
Try something like this: MYSQLSTRING=/usr/local/mysql/bin/mysql -u $username -p $password -h $server cetechnology RESQUERY=SELECT COUNT(*) FROM hardware_assets WHERE ethernet_address='$ethernet_address' RESULT=`echo $RESQUERY; | $MYSQLSTRING` INSERTQUERY=INSERT INTO hardware_assets (ethernet_address) VALUES ($ethernet_address) UPDATEQUERY=UPDATE hardware_assets SET Operating_system='10.3.3' if [ $result -eq 0 ]; then echo $INSERTQUERY; | $MYSQLSTRING else echo $UPDATEQUERY; | $MYSQLSTRING fi Thanks, Ryan Yagatich ,_, \ Ryan Yagatich [EMAIL PROTECTED] \ / Pantek Incorporated (877) LINUX-FIX / \ http://www.pantek.com/security(440) 519-1802 \ / Are your networks secure? Are you certain? / \___F49F3365CFC4F2613AD204C58E50C1AD73F23DC722666BD4___\ On Mon, 9 Feb 2004, 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
Ignore this as aparently someone already posted almost the exact same thing :) Thanks, Ryan Yagatich ,_, \ Ryan Yagatich [EMAIL PROTECTED] \ / Pantek Incorporated (877) LINUX-FIX / \ http://www.pantek.com/security(440) 519-1802 \ / Are your networks secure? Are you certain? / \___F49F3365CFC4F2613AD204C58E50C1AD73F23DC722666BD4___\ On Mon, 9 Feb 2004, Ryan Yagatich wrote: Try something like this: MYSQLSTRING=/usr/local/mysql/bin/mysql -u $username -p $password -h $server cetechnology RESQUERY=SELECT COUNT(*) FROM hardware_assets WHERE ethernet_address='$ethernet_address' RESULT=`echo $RESQUERY; | $MYSQLSTRING` INSERTQUERY=INSERT INTO hardware_assets (ethernet_address) VALUES ($ethernet_address) UPDATEQUERY=UPDATE hardware_assets SET Operating_system='10.3.3' if [ $result -eq 0 ]; then echo $INSERTQUERY; | $MYSQLSTRING else echo $UPDATEQUERY; | $MYSQLSTRING fi Thanks, Ryan Yagatich ,_, \ Ryan Yagatich [EMAIL PROTECTED] \ / Pantek Incorporated (877) LINUX-FIX / \ http://www.pantek.com/security(440) 519-1802 \ / Are your networks secure? Are you certain? / \___F49F3365CFC4F2613AD204C58E50C1AD73F23DC722666BD4___\ On Mon, 9 Feb 2004, 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]
