counter customizable free hit

Monday, October 31, 2005

Oracle Database XE

Latest news from the Oracle world is that they have just released a new beta version of their database product, Oracle 10g Express Edition (Oracle Database XE). What's so special about this release? Well it's free, Oracle have offered their databases for download for free to developers for as long as I can remeber but the important difference with XE is that you can use it in production environments without any charge also. This seems in direct competition with the current "free" databases on the market such as MySQL.

The database is limited in that it can only be installed on single processor machines with 4Gb of disk memory and 1Gb of RAM. Andrew Mendelsohn, senior vice president of Oracle's server technologies division has said

"There is definitely a market there (for low-end databases) and a demand. And we want them to be using Oracle and not MySQL or SQL Server Express"

The theory is that students will be able use XE for free which will hopefully lead them on to the full Oracle database products. I suppose the question is, is it all a little too late, MySQL already has a great reputation in the area Oracle are targeting and IBM has, and Microsoft are, looking to release free lite versions of their database offerings, DB2 Express and SQL Server Express.

Versions for Windows and Linux are available for download from the Oracle website

http://www.oracle.com

Tuesday, October 25, 2005

Where did my comments go?

Following on from post about the MySQL forums, I answered a question this morning which might be of use to others. It relates to comments in MySQL Stored Routines (SQL/PSM).

I suppose a quick mention of how to write comments in SQL/PSM would be usefull. There are two types of comments in SQL/PSM firstly there is a comment characteristic value we can use to store general comments about the routine, we will look at that later, then there are in-line comments, thesw will be similar to comments you may have used in other procedural languages or programming in general.

The first is a single line comment, when ever you wish to simply show a single line comment you can use the following syntax

-- This is a comment.

MySQL will ignore anything after the -- when it compiles the SQL/PSM code. The second type of in-line comment is the /* combination. This can be used to make comments on multiple lines of code like so.

/* This is the first line of the commnet
and this the second line. */

MySQL will ignore any text between the /* and */, you must make sure that if you use multiple line comments that a closing */ is present. It's up to you which style you use, personally I tend to use -- as it's a habit I picked up years ago, I also tend to add ++ after the first comment character to denote the start of a section of comments, but thats very much a style choice on my point and doesn't offer anything interms of compilation.

--++ This is the first line of the commnet
-- and this the second line

OK so we know how to write comments in SQL/PSM now on to the problem. Let's create a very simple SQL/PSM procedure.

create procedure CommentTest()
begin
-- this is a comment
select 'Comment Test';
end;
//
Query OK, 0 rows affected (0.00 sec)

Now here comes the issue, we can issue a command to view the SQL/PSM code stored in the database like so.

mysql> show create procedure commenttest//
+-------------+------------------------------------+
| Procedure | Create Procedure |
+-------------+------------------------------------+
| commenttest | CREATE PROCEDURE `commenttest`() |
| | begin |
| | |
| | select 'Comment Test'; |
| | end |
+-------------+------------------------------------+
1 row in set (0.00 sec)

The SQL/PSM code is shown but there are no comments, this can also be seen if you are
using the MySQL Administrator, when you click the "Edit Stored Proc" button the code is shown, but no comments are displayed.

Where have they gone? well mysql removes any inline comments when the procedure is loaded into the database. This isn't such a problem for somebody like me who creates scripts to create procedures as I have the comments retained in the script, but if you create your procedures in something like MySQL administrator then those comments will be gone for good.

Unfortunatley there isn't anything that can be done about this at present. The comments have passed on! They are no more! They have ceased to be! they have expired and gone to meet 'their maker! Bereft of life, they rest in peace! their metabolic processes are now 'istory! off the twig! they kicked the bucket, they shuffled off 'their mortal coil, run down the curtain and joined the bleedin' choir invisibile!! THEY ARE EX-COMMENTs!!

The MySQL Forum

Every now and again I browse the MySQL forums, it's a great way to help others, hone your own skills and also to learn something new. It's also a great inspiration for this blog, many of the topics I mention are directly related to message I see posted. I don't refer to messages directly as I wouldn't want to point out peoples mistakes publicly, which is often the reason behind a blog entry, I see a lot of the same sort of problems or assumptions and so write about it here to try and spread the knowledge around a bit.

Making mistakes or assumptions is never something to be ashamed of, as you well know I make them all the time, it's obvious people are coming to MySQL from other databases and trying to do things the same way they have in the past. Rather than worry about them we should embrace them, use them as a method for learning. The hard part comes in actually asking for help, it can often be a little difficult asking other people to look at your code, we want people to help with the immediate problem but we don't want to expose our code to the eyes of others.

But if you do need help with a MySQL related problem, be it installation, simply SQL or stored procedures do checkout the MySQL forums, http://forums.mysql.com/. But don't let that be your only reason for visiting, get involved, have a go at answering questions yourself.

I for example know a fair amount about stored procedures but I know very little about performance issues. So I visit the performance forum from time to time to see the sort of thing people are asking about, this gives me a chance to look at things I might never have seen before.

So give it a go, get involved.

Finding (another) name for MySQL Stored Procedures

If you checked my blog yesterday you may have seen a post titled "Finding a name for MySQL Stored Procedures". The general theme was trying to find a smaller and more snappy method of refering to stored procedures within MySQL. I got a fair few comments from people about the blog which were generally positive and welcomed the idea.

However, I recieved an email from MySQL AB requesting that we didn't use the name I had decided apon. The reason begin that it included a My prefix and MySQL AB feel that doing so dilutes the trademark. This can be seen in action for example in the renaming of MyODBC to Connector/ODBC.

I of course don't have a problem with this, the intention of giving stored procedures a name was more to do with keeping things short and also removing the ambiguity of the word procedure when actually talking about functions.

The big question is do MySQL stored procedures actually need a name? They conform to the ANSI Standards, or at least a subset of it, so perhaps when refering to stored procedures we can use the ANSI title of SQL/PSM (Persistant Stored Modules).

Monday, October 24, 2005

A MySQL Mantra

Having been in a previous incarnation an Oracle developer I kept a close eye on a site called AskTom. The Tom of AskTom is Tom Kyte who is an Oracle employee with a great passion for helping people with their Oracle problems. He's also an author of some great Oracle books which I recommned anybody developing with Oracle either directly via PL/SQL or with Oracle as the backend database read before they type a single line of code.

Tom has a mantra with regard to developing with Oracle. It goes like this.

o if you can do it in a single sql statement - do so.
o if you cannot, do it in as little PLSQL as you can.
o if you cannot, because plsql cannot do it, try some java (eg: sending an email
with an attachment)
o if you cannot do it in java (too slow, or you have some existing 3gl code
already) do it in C as an extproc.

This is great advice, at least for Oracle developers, in that it's making the most of each method at the appropriate stage. An SQL statement for example is far superior for data manipulation than say PL/SQL because Oracle has been built from the ground up to be as fast as possible when using SQL. But it's also true that the feature set is limited when it comes to decision making, this means there are often times when you need to revert to PL/SQL to get the job done. The third stage is somewhat particular to Oracle in that it has a JVM built into the database, therefore you can call Java as you would a stored procedure and execute this right inside the database.

One thing to be clear on here is we are not talking about developing whole applications in Oracle, we are talking about manipulating data, this means that we really won't be getting much passed stage 2 in 99% of the situations we might come across. The only time we might need to move on to the Java stage is if we need to interact with the outside world, Tom's email example being the obvious case (having said that in Oracle there are PL/SQL packages available to send email).

So how does this tie in with MySQL, it got me thinking about what a suitable Mantra would be for MySQL. I've come up with the following, based very much on Tom's Oracle Mantra, but it's by no means something set in stone, I'd really appreciate feedback on what people think.

o if you can do it in a single sql statement - do so.
o if you cannot, do it in as small a stored procedure as you can.
o if you cannot, because a stored procedure cannot do it, try a UDF
o if you cannot, because a UDF isn't suitable take the code out of the database

I should say with regard to the last stage it's likely that if you have exhausted the first three stages then you're most likely no longer dealing with data exclusively and therefore the code may live more comfortably in your application layer.

Wednesday, October 19, 2005

MySQL 5.0 Stored Procedures : Part 4 (update)

As long ago as last week I said that on the following day I'd post a new blog entry on Stored Procedures, yet again the day job has beaten me. It's now a week later and I still haven't found a spare 20-30 minutes to devote enough time to write the next instalment.

So as way of an update I'll just briefly go over the homework I set. I asked "what happens if you don't use INTO to pass the value into a variable", I gave a clue or at least mentioned that in Oracle trying to load a procedure with a select statement without an into clause results in a compile error (PLS-00428 to be exact).

But MySQL exhibits some interesting behaviour, I'm not exactly sure if it's by design but when issuing a 'normal' select statement in a procedure rather than an error MySQL returns the result set as if you have issued the command directly from the command line.

IE Problems

It's seems as if this blog was having problems displaying correctly in IE, thanks to Roland for pointing that out. It was to do with the code samples and my use of <pre> to format them in a consistent way.

As a Mac user I tend to write this blog using Safari and when I view from work it's done via FireFox so the problems didn't show up for me. In future I'll test on IE just to make sure everythings OK, a new layout may be in order to address the problem going forward.

Wednesday, October 12, 2005

MySQL 5.0 Stored Procedures : Part 4

OK, it's been nearly 2 weeks since the last item on stored procedures, so sorry to those who had been following along, I'm sure by this point you will have gone elsewhere for your Stored Procedure fix, but I'll pickup where I stopped with Part 3.

Today we will be looking at selecting records within a stored procedure. I'm sure if you looking at MySQL 5.0.13 you may have been using MySQL 4 for sometime or at least have some experince of writing SQL statements with other database technologies. Therefore I'll assume you know what one looks like and how to construct one using your database tables.

I'll be using a table called emps in this example..
mysql> desc emps;
+----------+---------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+----------+---------------+------+-----+---------+
| emp_id | int(11) | NO | PRI | |
| emp_name | varchar(30) | YES | | NULL |
| dept_id | int(11) | YES | | NULL |
| salary | decimal(5,2) | YES | | NULL |
| bonus | decimal(12,2) | YES | | NULL |
+----------+---------------+------+-----+---------+
5 rows in set (1.72 sec)

In pervious procedure examples we used a parameter to pass in a name to include in our output string, what might be more usefully from an application persepctive is to be able to pass in an employee's ID number (in our example table this is the emp_id column) and then display the associated employee's name.

So how do we do that in a procedure, in a normal command line environment we would just issue a select statement as follows
mysql> select emp_name from emps where emp_id = 1;
+----------+
| emp_name |
+----------+
| Paul |
+----------+
1 row in set (0.14 sec)

But in our procedure we want to use the name as part of our output string, to do this we need to use INTO in our select statement. INTO passes the value of the select into a variable, "Hold on" you say "what's all this variable buisness?". In general and simplistic terms a variable is a data store, a named area of memory we can use to hold values during the execution of a procedure

You can find more information on variables here...

http://mysql.gilfster.com/page.php?parent_id=1.1&page_id=1.1.5

To keep things short I'll let you look into variables if you need to, but I'll press on. So the first thing we need to do is create the variable, then we issue the select statement passing the output into the variable using INTO, then we reference the variable in much the same way as we referenced the parameter in previous examples.
mysql> delimiter //
mysql> create procedure
-> hello_employee(IN p_emp_id INT)
-> begin
-> declare p_name varchar(30);
-> select emp_name into p_name from emps
-> where emp_id = p_emp_id;
-> select concat('Hello ',p_name);
-> end;
-> //
Query OK, 0 rows affected (0.30 sec)

mysql> call hello_employee(1)//
+-------------------------+
| concat('Hello ',p_name) |
+-------------------------+
| Hello Paul |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Great, so we can now see how to select values from a table in our procedures.
Have a go at constructing your own select statements, any select statement that's valid on the command line is valid in a procedure.

For homework, what happens if you don't use INTO to pass the value into a variable. Those of you who have worked with another procedural language such as Oracle's PL/SQL might expect it to fail, in fact not even compile, what happens in MySQL? I'll discuss what happens and the implications of this tomorrow.

Tuesday, October 11, 2005

Wow...

I got back from my vacation at 7:00 am UK time yesterday, I had a quick shower and it was straight back to work, things are busy here in the office as we just went live on a rather large ecommerce site, it's looking great so far no major problems but plenty of little things to keep us all busy over the first few weeks I'm sure.

All of this meant that I didn't have a chance to catch up with my personal email until I got home last night. I logged on expecting the odd email (I don't get many to that address) but I had loads. But of course the important one was the email from Arjen Lentz telling me I'd won an iPod Nano for my work on this blog and also over at www.mysqldevelopment.com.

I was both surprised and honored to be receiving the first weeks prize, it real was unexpected. When I started the short series on using stored procedures a few weeks a go there was a genuine desire on my part to impart at least a little knowledge to people reading this blog so they could go off and start trying to find those bugs, write blogs and win those iPods.

So thanks to MySQL, Arjen and everybody who's been following this blog or the site. There are still 7 (soon to be 6) iPods up for grabs and also those 3 Grand Prize of a pass to the 2006 MySQL Users Conference, so keep blogging and looking for those bugs.

http://dev.mysql.com/mysql_5_contest.html

Monday, October 10, 2005

Sorry

I seem to spend every few blogs issuing an apology. This week I'm saying sorry for not keeping you guys posted with regards to my absence over the last week. I'd got off to a good start with the blog entries for getting started with Stored Procedures and then I realised I had other commitments coming up fast, first a client going live at the weekend and secondly a weeks holiday in Jamaica.

Well both were a great success, I left the go live in the very capable hands of my work colleagues, but took the holiday myself. The Hotel had an internet room but I resisted the temptation and went the whole week without using a computer.

So if you have been checking back for an update to the stored procedure items, I'll be back on those very soon.