UNIX and Linux Applications

hi, unix and mysql gurus.

i'm trying to create a simple ksh script that accesses the mysql system database table, user. the idea is that the user can call the script with a regular expression to see some users, or call it without any options to see all users. this is what i have so far:


*****************************************************
mysql -BCNnqs --disable-pager -u system -pmanager << EOJ
use mysql;

set @regex = '$1';
select user from user where user REGEXP @regex;

quit
EOJ
*****************************************************


the problem, of course, is that if $1 is null, i get this error:

==> ERROR 1139 (42000) at line 8: Got error 'empty (sub)expression' from regexp

i changed my set statement to this, but it didn't work:


*****************************************************
set @regex = if(@regex is null, '^[a-z]','$1');
*****************************************************


does anyone know what i'm doing wrong? i'd appreciate your input. thanks.

I don't have access to this at the moment to test it, but you can put the entire select statement inside a variable:

Code:

QUERY="select user from user"
if [ -n "$1" ]; then
  QUERY="$QUERY where user REGEXP '$1'"
fi

mysql -BCNnqs --disable-pager -u system -pmanager << EOJ
use mysql;
$QUERY ;
quit
EOJ

And actually, you can specify the database on the command line, so that you pipe the command into mysql:

Code:

echo $QUERY | 
mysql -BCNnqs --disable-pager -d mysql -u system -pmanager

(I think it's the -d option. It might be -D).

thank you, otheus! i really appreciate your help.

Or you can pass in ${1-.} which evaluates to $1 unless it's empty, in which case the value is ".". (I imagine that's a better "catch-all" regex than the one you were using.)

fantastic. thanks, era.