counter customizable free hit

Tuesday, July 18, 2006

Spotlight on MySQL

One of my "part time" roles is a sysop on the Quest MySQL Pipelines, as I have mentioned on here before we don't get many posts unfortunately and I can only assume that's because MySQL have such great forums of their own.

Anyway, Quest produce a product called Spotlight on MySQL which is a rather cool looking monitoring tool for MySQL. Personally I haven't had the chance to use it but the development team are currently looking for feedback on Spotlight on MySQL, with particular interest in if there is need for replication support.

So if you have anything to add pop over to the forum and let the guys know in the Spotlight on MySQL section.

http://pipetalk.quest-pipelines.com/default.asp?boardid=mysql


Wednesday, July 12, 2006

Regular Expressions in Oracle 10g - Part 1

Before my current role I generally worked in a windows environment, I would occasionally copy a file or log on to a Unix box to does something only when absolutely necessary. But at my last job and this current one I have had to spend most of my time connected to a Unix server, for as long as I could I'd edit in Windows and copy across to do what I needed but it soon became apparent that I'd need to get with the times ;) and take the plunge into Unix.

I have found Unix to be really powerful and it didn't take anywhere near as long to get used to vi as I feared. One of the things I have most useful is regular expression, now I know that they are available in Windows in various places but in all my time with computers I had only a minimal amount of exposure to them.

This then leads on to today's blog, In Oracle 10g a number of new functions were introduced that allow you to search and update using regular expressions. Over the next few days I'll be looking at what can be done but lets start with some simple examples. Let's take the following data set.

SQL> select * from reg_test;

VAL_1
------------------------------
My name is Andrew
My name is Dave
My name is dave
His name is dave
His name is dave.
He dave me andrew

Before 10g we had to use like to search on sections of a column, this wasn't necessarily a problem but often involved using other functions to format the data in a way that we could search on. For example lets say we wanted to get a count of all of the records with Dave at the end, including those which were upper or lower case. Using like we would do something like this.

SQL> select count(*) from reg_test
where upper(val_1) like ('%DAVE');

COUNT(*)
----------
3

That's fine but it requires us to covert the whole of the val_1 to upper case so we can pick out the required fields. Also we have a count of 3 when realistically speaking we have 4, one of the rows has a full stop. We could to this..

SQL> select count(*) from reg_test
where upper(val_1) like ('%DAVE%');

COUNT(*)
----------
5

Now we are getting 5, because we have added the second %. To get round this in 10g we can now use REGEXP_LIKE, this allows us to use regular expressions as the comparison.

I'll assume some knowledge of regular expressions (given you possibly know more about them than I do).

SQL> select count(*) from reg_test
where regexp_like(val_1,'[Dd]ave\.*$');

COUNT(*)
----------
4

This time we get the right result.

Monday, July 10, 2006

Adventures in PHP and Oracle

Recently we have been asked to develop some of our future sites in PHP. Traditionally we have worked in an in house language which fits in nicely with HTML but there is pressure from our clients to use something a little more "available" so that they can customise their sites themselves.

The in house language connects to Oracle and we have a large library of Oracle Packages designed to get our information from the database in a structured way. One thing we do a lot is return data in collections rather than via ref cursors. The problem is that the current version of the Oracle Call Interface doesn't support returning arrays (collections) which have been defined within the header of a package.

This means either rewriting some 500 packages/procedures or adding another layer of complexity to convert the returns into a data type PHP can handle.