Sunday 4 February 2007

MySQL Stored Proceedures - You just need a maestro


And I am not talking about an 80's talking car, seriously Austin (went bust surprise surprise), night riders car might have talked to him but that didn't make it right.

No the Maestro I am talking about is the rather nifty new MySQL administration tool which makes creating stored procedures so much easier. There has been a lot of discussion on the web about whether MySQL has what it takes to run enterprise level databases for real world companies and despite the fact that there are lots of examples of huge companies using it I feel that the feeling of nervousness stems from the fact that although the database is rock solid the management apps are definitely not. Lots of good progress has been made of late particularly by the people at MySQL but most of their GUI apps are recently out of alpha and I do not like the fragmented approach they have adopted, ie. mange your permissions and backup with this app, analyse your queries with this one and extract data with this one. I end up having 4 applications open to do a days work. Well not any more, sqlmaestro.com is the place to get the only MySQL management tool you will ever need, and all for only $149!

Cheesy plug over - the reason I came across this software was because I wanted to use the new MySQL stored procedures and to see how they compare to Microsoft's offering, which I have been recently introduced to as you know (because you read this every day don't you). I turns out that Stored procedures have only recently been introduced into MySQL in version 5.1 and documentation and examples of their use are a little sparse at present. This particular piece of software however has its own really good stored procedure manager which is why i downloaded it and found the rest of the tools it offers as a very nice surprise.

I will go into how to actually write a stored procedure tomorrow, when I actually write a useful one, but to anyone who is just looking around to see how they can get into this there are a couple of things you have to do first it turns out. Unless you have just installed the latest build of Fedora or some other main stream Linux build you will have to upgrade both PHP and MySQL (assuming you want to use PHP of course). Stored procedures are supported in MySQL 5.1 but you cannot actually get at your stored procedures from PHP without the mysqli addon. As we use Centos as our weapon of choice on the Linux servers this means having a whole scale muck out. It turns out the safest way to upgrade either of these programs is to backup - remove - and reinstall which frankly appeals not at all (he he). I have tried it locally here but tomorrow I will be reorganising these programs, when its all over I will write some proper stored procedures and tell you all about it :o)

l8r
k

1 comment:

Harish said...

can u explain me the step from the scratch... how to write the procedures and how we can call this trough php

A view from the rack is the personal blog of an IT manager who works for a pub company - hence beer