Improving your SQL skills

I used to be almost exclusively a database developer, writing complex queries and stored procedures in Oracle and MySQL. Of late I've been doing more and more web based work, HTML, JavaScript, CSS, PHP and PERL, this means of course that I'm doing less and less SQL especially the more complex stuff that I would have been doing in the past.

Personally I've been using SQL long enough that while it might not flow from my fingers as it did previously with a bit of thinking it comes back fairly quickly. I tend to think of it like fitness, if you go to the gym regularly your fitness levels improve quickly, slack off and they drop back down almost as fast.

To keep my SQL skills "fit" I visit another sort of gym, the MySQL Forums, to be exact the Newbie forum. In that forum there are plenty of people offering you the chance of a mental work out at all skill levels, because of it's very nature there are all sorts of questions which often need a clever solution. While it’s a pain to most people the lack of support for sub-queries in MySQL 4.0 is great for a work out as it often makes you think a little harder about how to tackle a problem you might normally throw a sub-query at.

If you ever become too fit for the Newbie forum there are plenty of others, just like a gym has different equipment to work different areas of your body, the MySQL Forum has different sections to raise you SQL fitness levels further, the General and Performance being good ones to put mass on those SQL muscles.

The great thing about these questions is that during your normal working year you might never get to use some of the features needed to answer them, there is nothing worse that your boss dumping a set of reports requirements on your desk and having no idea how to even start constructing the SQL. But if you have been working out at the SQL gym you’re likely to have come across a few methods to get started.

So why not do what the government health departments keep telling us and spent 30 minutes a day in the gym.

XML - Using MySQL with AJAX (part 2)

When I suggested MySQL had a big part to play in the X of AJAX it was more of a case throwing ideas into the ether than any solid or contrived plan. My initial thoughts were simply to allow the web server to talk directly with MySQL and return the XML needed for a give select statement. The goal was to avoid the normal middle stage of using some sort of scripting to accept the statement, call a standard MySQL select and process this into an XML format. Something along the lines of MySQL being an XML factory where you pass the select and MySQL directly passes back the XML.

Some great ideas have been suggested, mainly around using --xml to format the output directly from the command line, which would interact with Apache by a module. I've been able to knock up a Perl program which is capable of doing this but it's raised more concerns than solid answers.

When I looked at this more closely the immediate thing was how unsecured the whole thing would be, not so much unsecured as completely open in fact. The original idea was to essentially call MySQL directly from JavaScript (via the web server) but that would mean having the select statement, or at least the table and column names visible in the JavaScript. There would be no way to secure that without some level in between.

The original post in fact wasn't suggesting that we interface directly with MySQL as it currently is, it was more of a look into the future to see where for example stored procedures could be taken. The idea would have been to use stored procedures to accept parameters which were then turned in select statements and XML returned, that way we could at least avoid the potential of SQL injection attacks, or people simply calling SQL statements at will.

Don't get me wrong the ideas have been great and I'm still confident MySQL can be central in an AJAX system in the way suggested, but maybe not with the minimal number of levels I might have first envisaged.

WiFi Access

An article on the BBC News website today suggests that WiFi is "slow to enthuse consumers". The report by Toshiba suggests that only 11% of people used WiFi away from home, while at home over 50% used WiFi to access the internet.

Personally I don't think for a second that it's a case of lack of enthusiams by consumers which is stopping people getting on line on their laptops when away from home. I'd love to be able to logon when I'm out and about especially since I just got a WiFi enabled mobile phone. I did a little bit of research using the WiFi alliance web site which lists WiFi hot spots in the UK (as well as the rest of the world). Within a 2 mile radius of my home there are 6 hotspots, all of which in pubs. While I enjoy going to the pub it's the last place I want to take my £1000 laptop for the evening. Extending the search to 20 a mile radius and I'm up to 251 and this time there are a few coffee shops but few in a place I'm actually likely to be.

Be here comes the point, I then check for free hotspots, there are just 2 in a 20 miles radius. Checking the price plans for the pay services reveal that it's going to cost me cira £30 a month to get connected. Add on top of that the £2 for a coffee or £3 for a pint of beer I'll have to pay to sit in the pub of cafe while surfing and that soon starts to add up. Add that on to the £20 I'm already paying for internet access at home and thats a hell of a lot of money.

Perhaps the headline should have been....

"Cost of WiFi fails to enthuse consumers"

BBC News : Wi-fi slow to enthuse consumers

MySQL Stored Procedure Programming (The Book) - 2

This is the second time* I've mentioned the O'Reilly MySQL Stored Procedure Programming book on this blog. I'm mentioning it again because I suddenly realised that the release date of March 2006 isn't anywhere near as far away as I thought it was, when your in 2005 any date in 2006 seems like a bit of time away but on reflection and now being half way through the first month of the year it seems to be creeping up with even increasing speed.

With that in mind I placed my order with Amazon.co.uk today, it's avaliable on pre order and with a 30% discount on the cover price.

Click here to view the book on Amazon.co.uk

I have all 7 of Steven Feuerstein's Oracle books and as I said previously if it's half as good as those it will be a great book.

*I have no vested interest or association with the book, O'reilly or the authors. I just know it's going to be a great book.

Don't be afraid to get it wrong.

While it may not be a great thing to admit but some of my best lessons have come at the result of initial failure, taking a backup before deleting all the data in a table comes to mind. When I first started this blog and even more so when it was added to PlanetMySQL I made a decision that not being 100% sure about something wasn't a reason not to post it. Don't get me wrong I always make every effort to check my facts and in the Tom Kyte mode I'll always (or at least try) and give real world examples to prove my point.

But there are often times when I'll post something which is inaccurate or just plain wrong. There are broadly two things you can do in such circumstances when people correct you (and believe me they will), you can accept you made a mistake, correct any errors and learn from it. Or you can just give up and never post again for fear of looking a total fool. The fact I've made mistakes and I'm still blogging will give you some indication of which group I fit into.

This slightly confessional blog is in response to a post I made a few days ago with regard to XML and MySQL. The central theme was a vision of MySQL as a key component in AJAX where rather than the added complexity of a language such as PHP, PERL etc MySQL took on the role of serving the XML directly from the web server. In it I pointed to Oracles SET MARKUP HTML command and longed for a MySQL equivalent, or at least an equivalent XML version. If you read the comments on that post you will see that MySQL does support such a feature, it's not exactly what I was talking about but it's close enough to have been mentioned. I'll say that I was at least in the past aware of the feature but when I was checking out my facts prior to posting I couldn't find mention of it on the MySQL site, I just thought I was mistaken and it was a feature of some other software.

The point is that you shouldn't sit in the shadows fearful of getting something wrong, if you have been worried about blogging or answering questions on forums for fear of being wrong you won't get very far.

Yesterdays mistake makers may well be tomorrows experts*

*Providing they learn from those mistakes.

XML - Using MySQL with AJAX

There has been a few posts on PlanetMySQL recently with regard to an XML, firstly the new XML functions for extracting information from XML stored in standard tables and more recently an XML storage engine.

It got me thinking, I've posted recently about working with AJAX and I've been developing some AJAX functionality for our clients. It seems to me that part of the goal with AJAX is to give a better user experience but also to reduce the amount of traffic to the server by only loading what's needed, when it's needed. Not as in our case a refresh of the page (along with the associated images). There is a balancing act between making the page functional and also keeping the amount of Javascript down, it's easy to shift alot of HTML from the initial page only to replace it with complex JavaScript functionality to manipulate XML which is returned in the xmlHttpRequest calls.

I've used Oracle's HTML DB which is an Apache Module which calls Oracle stored procedures and returns the output to the requesting browser, this allows people who are comfortable with PL/SQL to develop web applications from within Oracle, no need for PHP,PERL etc. In fact this is the very reason I became involved with MySQL, looking for an equivalent technology in MySQL.

What would be great is if we could use an Apache module to return the contents of a MySQL stored procedure, this procedure could accept parameters and return an XML document straight from Apache, no additional PHP layer just an HTTP response directly from the webserver interacting with MySQL.

Another great feature of Oracle is SQL*Plus' ability to export in HTML, using the command SET MARKUP HTML ON any subsequent query is output on HTML table format. Something I think would benefit MySQL would be the possiblity of outputting data in XML format, that way we could even remove the stored procedure layer in the above example.

That would fit fantastically into AJAX. Something like the following cold have uses far and above just AJAX...,

mysql> select * from emps;
+-------+---------+
| empno | ename   |
+-------+---------+
| 1     | Dave    |
+-------+---------+
| 2     | John    |
+-------+---------+
2 rows in set (0.00 sec)

mysql> set output xml 

mysql> select * from emps;

<emps>
  <row>
   <empno>1</empno>
   <ename>Dave</ename>
  </row>
  <row>
   <empno>2</empno>
   <ename>John</ename>
  </row>
</emps>
2 rows in set (0.00 sec)

Event Scheduler

Release 5.1 of MySQL comes with a new event scheduler, this is great news and further moves the MySQL database away from just a data store, something which began with the introduction of Stored Procedures and Triggers in 5.0.

The event scheduler will allow you to run an SQL command at a specific time, this is great for maintenance purposes. It's possible to run the command a single time or set up and interval and run it multiple times. What's also great is that you can use DDL, DML and even compound statements which allows SQL/PSM syntax to handle processing as part of the command.

I'll admit to not having downloading and installing 5.1 as yet but this is definitely something I'll be using. As an Oracle user I've been using Oracle's version DBMS_JOB for a while and this looks to be as good, if not better due to the easy method of adding events.

Having said all this often in Oracle I have often preferred to use a combination of SQL*Plus and CRON due to the ability to process the output in Unix/Linux after the job has run. For those using a MySQL version other than 5.1 CRON does offer a great way of doing a similar job to the new event scheduler.

You can see an introduction to the event scheduler here.

http://dev.mysql.com/tech-resources/articles/event-feature.html

Blue Screen of Death and other Myths.

I was half listening to a radio show yesterday about the Consumer Electronics Show, the general feeling that this year there wasn't really a wow moment and nothing earth shatteringly new had been shown. One of the guests was even rather dismissive of Apples new Intel based laptop range (which while a major shift from Apple isn't actually a major step forward in general). But one of the things that was said was about the unreliablility of Windows, the dreaded Blue Screen of Death (BSoD) was mention and how often it happens.

It got me thinking, is that really true? I've been running windows (in it's various forms) since the early 90's and while it crashed with regularity in the earlly days I personally haven't had many problems for years. At work I ran NT for almost 3 years without even turning the PC off and only twice did I get the BSoD, after a quick restart everything was fine.

At home with XP it's just as good, while the PC doesn't stay on for days I've never had a BSoD and from what I recall never had to hold the off button down to clear a crash. I got a Mac last year and while that has a great reputation for stability I have had to hold the off button on a couple of occassions, am I to infer that Macs are buggy and unreliable?

AJAX, iPods and the MySQL Guild

I took a little break over the Christmas period and avoided using the computer as much as possible, so this is my first blog of the year. I mentioned AJAX in my last blog and in the time since then my demo was completed (after a few teething problems). I gave a demo to my boss and a couple of clients on Friday and it went down really well, it's often difficult with clients as people are so used to computers these days people have a certain level of expectation and while us as technical people can see the benefits of something like AJAX it's often lost in translation somewhere along the line. Thankfully in this case they were really keen and we are in the process of setting up a test to see if it improves the clients business.

Last week I recieved my iPod Nano, the prize from the MySQL 5.0 competition. It had been a while in coming due to me not being home when the delivery company called before Christmas. It's small, very small but sounds great, it's also black (my prefered colour) and the 4Gb version which I was suprised and greatfull to MySQL for.

Finally I have had the great honour of being added to the MySQL Guild, as both a writer and expert. It's great to have my work recognised over the last year and this is the icing on the cake. You can see a list of all the guild members here http://dev.mysql.com/guilds/