Formating Output of SQL

I mentioned a few posts ago about Oracle's SQL*Plus client program and how great it was at formatting output in different and novel ways, providing you know the commands to do it. One of the biggest problems is that the default linesize (the width of the screen buffer used to display rows of data in a table) is small and only actually displays something if you have small amount of columns. About 95% of the SQL I write breaks this default and displays something which is totally unfathomable.

The answer of course is to change the size of the linesize parameter, it's easily done and in fact with SQL*Plus you can set up a file to set this on log in, but I'm often connecting to servers where this file doesn't exist.

Anyway getting to the point I found a nifty way to format data in MySQL into a more readable format, this isn't some great secret but I wasn't aware of it until recently. Let's take a normal SQL statement and result from the MySQL command line.

mysql> select * from emps where emp_id = 1;
+--------+----------+---------+--------+--------+
| emp_id | emp_name | dept_id | salary | bonus  |
+--------+----------+---------+--------+--------+
|      1 | Paul     |       1 | 100.00 | 100.00 |
+--------+----------+---------+--------+--------+
1 row in set (0.08 sec)

MySQL actually does a very good job of formatting the data in a readable format, but if we have a table with a large number of columns things get a little crowded, we have to keep scrolling left to right to see which data at one end relates to the other.

But help is at hand, in MySQL you can format the returned results to be returned vertically rather than horizontally. This makes it easy to see data which is relevant to a single row only. To do this you simply append \G to the end of your select statement like so..

mysql> select * from emps where emp_id = 1\G
****************** 1. row *****************  
  emp_id: 1
emp_name: Paul
 dept_id: 1
  salary: 100.00
   bonus: 100.00
1 row in set (0.00 sec)

Check Constraints in MySQL 5.0?

First let me point out MySQL doesn't support check constraints, I just wanted to point that out up front before you run off looking for them. When I was writing content on Triggers for my site www.mysqldevelopment.com, I was looking at ways to use triggers in the real world. People were asking me why they might want to use them. I came up with a few but one I mentioned just didn't seem to work. That was implementing check constraints via triggers. A check constraint is a constraint on a column in a table which limits what the column will accept, Oracle for example allows the use of check constraints when creating a table. MySQL itself has constraints such as primary key and unique but the features are very much limited to what MySQL wants you to do. A check constraint allows more flexibility, you can dictate what you want to allow in the column.

For me it was obvious that triggers would be a great place to implement such a thing but the more I looked the less happy I was about implementing them. The first reason was that prior to 5.0.10 you couldn't access SQL from within a trigger. One of the things I would have needed to do was raise an error in the trigger to stop processing the insert/update, MySQL SQL/PSM doesn't currently allow you to raise an error manually, one way to do that would be to artificially raise one but because of the lack of support for SQL in triggers this wasn't possible without doing some potentially damaging things to the database.

However come release 5.0.10 I still wasn't happy, the sort of error messages we could raise didn't relate to the actual error at all. So I gave up on the idea in the short term.

But then I got an email from Scott Maxwell who came up with a suggestion on how this might be done. He's written a short article on the subject which can be seen here....

Emulating Check Constraints using Triggers.

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.

What hope for MySQL 5.0 Hosting

One of the great things about MySQL is its support from web hosting companies, many people use MySQL as a backend database for their websites, there are literally hundreds of web hosting companies offering MySQL and PHP and there are dozens of PHP/MySQL books on the market.

I've written a fair amount about the new features of MySQL 5.0 and in particular Stored Procedures and Views. But there's nothing worse than people telling you to do one thing and doing another themselves. My site www.mysqldevelopment.com runs on PHP 4 and MySQL 4.0, simply because that's what my hosting company offers, they are cheap and offer a great service. I recently asked them what their plans were for upgrading accounts to MySQL 5.0, given that they are still on 4.0 I didn't hold out much hope. I was right they don't have any plans to upgrade in the short or medium term and given they didn't even know MySQL 5.0 was about to be released I wouldn't expect much to happen in the long term either.

I'm about to start a new project, a simple site for me and a few friends and I thought it would be a great way to use 5.0 and also to include my observations in this very blog. But where could I get 5.0 hosting, I looked around the web and found nothing, well nothing apart from people offering to build be a server just the way I wanted and to be honest this site isn't anywhere near important enough for me to spend any money on it.

Then I hit apon an idea, you may already be aware of db4free.net, they offer access to MySQL 5.0 which is hosted on their server, it's not web hosting but it's possible to remotely connect and therefore use your existing host to serve the pages.

So all I need to do is use my hosting account to serve the PHP but connect remotely to my account on db4free.net.....

... but alas even that doesn't work, because to connect to MySQL 5.0, as least to use stored procedures you need to use the MySQLi extensions, these need to be compiled specifically under PHP4.

I suppose it's back to do as I say and not do as I do.