MySQL queries from Shell

philipz · 05-04-2004, 07:12 AM

List,

I have a problem with querying a MySQL database from Shell in batch mode.

I have a file called 'mylist' in this format:
10.156.40.36
10.150.133.33
10.140.159.89

Now with a script I make a Class C of every IP address and put that in a tempfile:
use sitedb
SELECT C FROM sitelist WHERE A LIKE "10.156.40.0";
SELECT C FROM sitelist WHERE A LIKE "10.150.133.0";
SELECT C FROM sitelist WHERE A LIKE "10.140.159.0";

Then I merge the output with the following command:
mysql --silent -h 10.10.10.10 -u username -ppassword < tempfile | paste mylist -

This works extremely well and gives this output:
10.156.40.36 Rome
10.150.133.33 Brussels
10.140.159.89 Amsterdam

However, if for instance 10.156.40.0 (Rome) is not found in the database, I don't get a result for that query (luckily). But my output gets shifted and doesn't match anymore:

10.156.40.36 Brussels
10.150.133.33 Amsterdam
10.140.159.89

In my opinion there are 2 options:
Force MySQL to return 'nill' or something similar if the result of a query is nill (no clue how to do that)

Adapt the script one or the other way so that it checks for a result after each query. Also no clue how to do that

Any suggestion would be greatly appreciated...

Kind regards,

Phil

crabboy · 05-04-2004, 10:47 AM

You can try something like this:

Code:

SELECT '10.156.40.36', C FROM sitelist WHERE A LIKE "10.156.40.0";
SELECT '10.150.133.33', C FROM sitelist WHERE A LIKE "10.150.133.0";
SELECT '10.140.159.89', C FROM sitelist WHERE A LIKE "10.140.159.0";

philipz · 05-04-2004, 11:34 AM

Hmmm, I think I miss something here...
If its an unknow ip-address, it still gives nothing as result, while with a known address it gives 2 results?

jim mcnamara · 05-04-2004, 04:54 PM

Try using the IFNULL function -

like

Code:

SELECT  IFNULL( C,'Not found.')  FROM sitelist WHERE A LIKE "10.156.40.0";

Edit : oops used the Oracle syntax.

philipz · 05-04-2004, 05:52 PM

This works if A (10.156.40.0) exists and C is empty (NULL), but NOT if A doesn't exist...
I want 'not found' if A doesn't exist. Is there a similar syntax that does this?

Thx (we're getting close)

- Phil

philipz · 05-04-2004, 06:38 PM

Just to clarify, in 'pseudo-code', this would look like:
SELECT C FROM sitelist WHERE IFNOTFOUND( A,'unknown') LIKE "10.156.40.0";