mySQL redirecting query result to a .txt file!
 

select last, email from address where email <> "";

This brings back the results as expected BUT I want the results to be piped to a .txt file.

I tried select last, email from address where email <> "" > /home/diamond/test.txt;
select last, email from address where email <> "" | /home/diamond/test.txt;

both end with same result:-

ERROR 1064: You have an error in your SQL syntax near '/home/diamond/test.txt' at
line 1

What am I doing wrong?

$ mysql < sql-batch > outfile

sql-batch holds your sql statements i.e:

use <dbname>
select * from sometable

Or from 'within' mysql (sqlprompt):

mysql> select * into outfile "/tmp/whatever" from some_table;

Can't create/write to file '/home/diamond/test.txt' (Errcode: 13)

Now I have another error code.

I have specified my mysql password using -p

You don't tell which option you tried.

Here's another option (for mysql prompt)

mysql> tee /tmp/whatever
mysql> <command>
mysql> <command>

All (!!) will be send to /tmp/whatever. You might need to clean up the output file.

With each day we get nearer!

quote "The dead stay dead and the living only wait to join them........
with less hair"

Thank you tee /home/spb/testing.txt and then my sql query works perfect.

Bit different from Oracle that I was using..

Anyway Success at last.

As I see from my new sql cookbook, page 30 Creating a tee file........introduced in MySQL 3.23.28, the Zaurus version is 3.22 that I have.

Does anyone know how to log interactive mysql sessions on a Zaurus to a text file like I have learnt to do on my desktop mavhine with tee /home/spb/testing.txt......

All your commands in mysql client are logged in ~/.mysql_history

My guess is that you were unable to select into outfile, because you were trying to write the outfile to a directory in which your MySQL server has no permission to write, e.g. your home directory. When you select into outfile, it is the server, not your client that writes the file. Whoever it runs as (daemon on my Z) has to be able to write to whereever you're trying to create your file. On my Z, when I entered just the file name, witout the path, outfile got created just fine in the /home/system/var/lib/mysql/mysql/ directory, which is probably not the best place to write files to...

Try writing to /tmp/outfile.txt The /tmp directory is supposed to be world-writable, although it is quite small on the Z

Well I found out on the Z I can use copy and paste into a text file! That was all I needed the first time.......will investigate what you say about /tmp