mysql erratic output

[leghorn]

Hi
I have written a shell script to execute my data base query

mysql -uroot -pshruti2 -B -e "use WebNmsDB;select HEADERINDEX from tekelec_meas_headers where CLLI=$clli and IVALSTART>="$ivalstart" and IVALSTART<"$ivalend" and RPTTYPE="$rpttype";" > win.txt

But the output in win.txt is

/mysql Ver 14.12 Distrib 5.0.44, for unknown-linux-gnu (x86_64) using EditLine wrapper
Copyright (C) 2002 MySQL AB
This is commercial software, and use of this software is governed
by your applicable license agreement with MySQL
Usage: /Tekelec/WebNMS/mysql/bin/mysql [OPTIONS] [database]

and more ... all the help commands

[leghorn]

where is it going wrong ?

[pritaeas]

Your query needs to use single quotes around your values, not double.

IIRC you can also specify the database with one of the command line options, so you don't have to use "USE".

[leghorn]

thanks,it works (the single quote thingy)

tell me more on "USE",if it please.

[pritaeas]

IIRC you can do this:

mysql -uroot -pshruti2 -B -e "select HEADERINDEX from tekelec_meas_headers where CLLI=$clli and IVALSTART>='$ivalstart' and IVALSTART<'$ivalend' and RPTTYPE='$rpttype';" WebNmsDB > win.txt

See the mysql command line manual for more information. Or type mysql -h

[leghorn]

ok,thanks
:)
also,the solution u recommended for the previous problem is not working well
the win.txt is now empty.
If I fire the same command frommysql comm. line , its does return a table

[pritaeas]

Are you sure the variables are entered correctly into that? Try with fixed values first, or with outputting to file.

[leghorn]

I tried .. same thing
Fixed values would create empty file win.txt
same command fired from mysql cl yeilds a table

[leghorn]

Hi,this doesnt give any output

mysql -uroot -phruti2 -B -e "use WebNmsDB;select HEADERINDEX from meas_headers where CLLI= '$clli' and IVALSTART>'$ivalstart' and IVALSTART<'$ivalend' and RPTTYPE='$rpttype';" > asdf.txt

This generates "good" output

mysql -uroot -phruti2 -B -e "use WebNmsDB;select HEADERINDEX from meas_headers where CLLI='tklc9010801' and IVALSTART>='01:30:00' and IVALSTART<'02:00:00' and RPTTYPE='COMPONENT MEASUREMENTS ON LINK';" > win.txt

Why so ???

the outputs of
echo $ivalstart
echo $ivalend
echo $clli
echo $rpttype

respectively are

01:30:00
02:00:00
tklc9010801
COMPONENT MEASUREMENTS ON LINK

[pritaeas]

There must be something wrong in your assumption how variables are parsed. What shell script are you using?

[leghorn]

bash script

[leghorn]

I was debugging and I found if shell script uses
mysql -uroot -phruti2 -B -e "use WebNmsDB;select HEADERINDEX from meas_headers where CLLI= '$clli';
the output is generated.

the moment I add things to make it
mysql -uroot -phruti2 -B -e "use WebNmsDB;select HEADERINDEX from meas_headers where CLLI= '$clli' and IVALSTART>'$ivalstart' and IVALSTART<'$ivalend' ;
the output generated is nil

What do I do ??
the output generated by these shell variables are sure as hell fine

Hi leghorn,

your bash command is almost correct. After -e follows the complete sql statement surrounded with double quotes. Double quotes ensure that your four bash variables will be expanded into their values.

Therefore your $-variables must not be quoted, neither single (') nor double quotes ("). Please, use this syntax:

mysql -uroot -pshruti2 -B -e "use WebNmsDB;select HEADERINDEX from tekelec_meas_headers where CLLI=$clli and IVALSTART>=$ivalstart and IVALSTART<$ivalend and RPTTYPE=$rpttype;" > win.txt

I have assumed that all bash variables are properly defined, say clli ="'clli-char-value'", rpttype=10, etc.

Please consider when defining string variable their values must be outside sourrounded with double quotes and then inside with single qoutes, if such bash variables are uses within sql statements. (There are also other methods to create a single quoted string inside a double quoted one for sql.)

Example:

Defining a bash variable which is used as an integer value within a sql statement:
rpttype=10

Defining a bash variable which is used as a varchar value within a sql statement:
clli="'clli-char-value'"

Defining a bash variable which is used as a date value within a sql statement:
ivalend="'2013-08-27'"

Now you can execute your bash command:

mysql -uroot -pshruti2 -B -e "use WebNmsDB;select HEADERINDEX from tekelec_meas_headers where CLLI=$clli and IVALSTART>=$ivalstart and IVALSTART<$ivalend and RPTTYPE=$rpttype;" > win.txt

You can also test whether your bash variables will be correctly expanded by echoing the complete command:

echo mysql -uroot -pshruti2 -B -e "use WebNmsDB;select HEADERINDEX from tekelec_meas_headers where CLLI=$clli and IVALSTART>=$ivalstart and IVALSTART<$ivalend and RPTTYPE=$rpttype;" > win.txt

File win.txt should now contain:

mysql -uroot -pshruti2 -B -e use WebNmsDB;select HEADERINDEX from tekelec_meas_headers where CLLI='clli-char-value' and IVALSTART>= and IVALSTART<'2013-08-27' and RPTTYPE=10;

Attention: For I haven't define bash variable $ivalstart, it is empty and its value is not shown in file win.txt (that would lead to a syntax error later when running on mysql).

There could also be another potential error, for IVALSTART is obviously compared with a time datatype, as shown in your example (echo $ivalstart, echo $ivalend, 01:30:00, 02:00:00 ...).

Here it depends on the sql datatype of IVALSTART. If it is datetime or timestamp you can extract the time part with MySQL TIME()-function. TIME()-function returns time as a string, for example:

select now() as "Now", TIME(now()) as "Time";
+---------------------+----------+
| Now                 | Time     |
+---------------------+----------+
| 2013-08-27 15:56:55 | 15:56:55 |
+---------------------+----------+