counter customizable free hit

Thursday, November 10, 2005

Writing SQL Results to a File

Having been somewhat concerned with MySQL 5.0 in recent posts (and the iPod now being won :)), today I'm going to be going back to looking at some of the differences between MySQL and Oracle.

Today it's the turn of output to a file from an SQL statement. Many of us old skool Oracle developers still prefer to use the good old SQL*Plus command line client, on of the reasons being that no matter where we are, be it on our desk top, on a clients site or even remotely via an ssh client we can be pretty sure a version is available. The great thing about SQL*Plus is that like the MySQL Command Line tool it's consistent across platforms, however one of the difficult things is that in it's standard mode you need to remember all manner of commands to get information to display the way you want.

But the great thing about SQL*Plus is it's easy to save the results of your SQL to a file. All you need to do is issue the following command.

SPOOL location/filename

From then on any command issued in SQL*Plus is exported to that file. This is great but also at times rather annoying, for example the standard page size setting in SQL*Plus is 14, this means the column headers are reprinted after every 14 rows returned, the standard line size is also only 80 characters wide. It also displays the SQL command you used and a summary of the rows you returned.

All these things are great when you need them but not so when you don't. SQL*Plus is very powerfull when it comes to reporting but most of the time my boss just wants a list of all the products which are out of stock, he doesn't care what the column name is (and certaily doesn't want it every 14 rows), he's not concerned with the SQL I used to get the data, and in many cases doesn't want to know how many rows it returned. So I then have to remember any number of SQL*Plus settings to suppress the information.

To be honest I'm making a meal of it, it's not that hard. Once you have run your SQL and stored the results in the SPOOL file we then just issue the following command to tell SQL*Plus to stop sending output to the file.

SPOOL OFF

That's all there is to it.

So what about MySQL, well it's a lot easier to get what we want with MySQL than Oracle. We can embed our file output requirements right inside our SQL. This is done using the INTO OUTFILE command like so.

mysql> select emp_id, emp_name from emps
into outfile 'c:/test.txt';
Query OK, 4 rows affected (0.03 sec)

This simply produces the file without any fuss, each column output as is in the table.

With SPOOL we can manipulate the file by simply formatting the output of the SQL, so if we wanted to output each column in the table with a comma delimiter we would have to concatenate the values together in the SQL to produce the required result. In MySQL there are a number of options we can use with INTO OUTFILE to change the way data is written to the file. These are FIELDS ESCAPED BY, FIELDS ENCLOSED BY, FIELDS TERMINATED BY and LINES TERMINATED BY. So if we wanted our output to be enclosed in double quotes, with comma delimiters and a new line termination for each row we would use...

SELECT emp_id, emp_name
INTO OUTFILE 'c:/result.text'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM emps;

This would produce the following file.

"0","Barry"
"1","Paul"
"2","John"
"3","Alan"

There are of course some advantages to both, you can't for example continue to output to the same file across multiple results when using INTO OUTFILE, which you can using spool, but when using INTO OUTFILE you can concentrate on what you want selected and let MySQL deal with the formatting of the output rather than having to write it long hand when using SPOOL.

Note: The MySQL documentation shows the INTO OUTFILE as coming after the column list and before the table clause (as with SELECT INTO when using SQL/PSM) but it seems to work just as well if you have it after the from clause as I did in my first example.

0 Comments:

Post a Comment

<< Home