Mysql Script Problem

[nycepete]

Below is my test.sh and my referral.sql now i want the .txt file to be named
the systemdate and the timestamp .txt

ex 20051009235959.txt somthing like that


test.sh
#!/bin/bash
/usr/bin/mysql -u root ehpadmin </usr/etc/referral.sql


referral.sql
SELECT * INTO OUTFILE '/tmp/referral.txt'
FIELDS TERMINATED BY ';' LINES TERMINATED BY '\N' FROM Referral_Authorization

my test.sh calls my referral.sql
thanks for help

 

[KarveR]

here , should work

test.sh

#!/bin/sh
#set -x # for debugging

#-- set the datetime string
DATE=`date +%Y%m%d%H%M%S`
#-- set the filename
FILE="$DATE.txt"

#-- cat the .sql thru sed and substitute dynamic filename 
#-- then pipe thru mysql
cat refferal.sql| sed -e "s/FILE/$FILE/g" |mysql

referral.sql

SELECT * INTO OUTFILE '/tmp/FILE'
FIELDS TERMINATED BY ';' LINES TERMINATED BY '\N' FROM Referral_Authorization

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[TonyGroves]

Alternatively, you could eliminate referral.sql:
[tt]
mysql -u root expadmin <<END
SELECT *
INTO
OUTFILE '/tmp/$(date +%Y%m%d%H%M%S).txt'
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
FROM Referral_Authorization
END
[/tt]

 

[nycepete]

Thanks no one else could help me thanks

 

[KarveR]

the only reason I did it as two is because I use a slighytly more complicated version to run multiple reports which are then emailed (how primative I hear you yell!).. but what aint broke ...

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[nycepete]

ok heres what i did and this does nothing. i know i have some syntax errors plaease help thanks

test.sh
#!/bin/bash
date='date +%Y%m%d%H%M%S'
file= "$date.txt"
cat /usr/etc/referral.sql | sed -e "s/file/$file/g" |/usr/bin/mysql -u root ehpadmin


referral.sql
SELECT * INTO OUTFILE '/tmp/file'
FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' FROM Referral_Authorization

 

[KarveR]

if you log in to mysql, does

SELECT * INTO OUTFILE '/tmp/file'
FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' FROM Referral_Authorization

work ?

*note: mysql will not write out to a file if it already exists.

Also, whats wrong with good 'ole bin/sh?

Possibly also you may need a space after the |, had funniies from that before.

If you get really stuck, add in the set -x to watch what the scriupt gets up to when its running.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[TonyGroves]

It might save a bit of time if I mention that nycepete is using quotes instead of your backticks, KarveR.

 

[KarveR]

Thanks Tony, I think the beer goggles are clouding me reading ability

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.