Recent Postings

Recent Comments


RSS2 feed


Most Popular Posts

Archive for MySQL

MySQL equivalent of PATINDEX

Thursday, 31st May 2007 1:17pm
One of the Transact-SQL expressions that I use quite often in SQL Server is PATINDEX which I use to find the starting position of the first occurrence of a pattern in a specified expression.

To find the starting position of say pattern 'ev' in 'web development' just as an example, you would use:

SELECT PATINDEX('%ev%', 'web development')

In MySQL you have a choice of two functions to use, POSITION and LOCATE. Using POSITION in MySQL the above example would be :

SELECT POSITION('ev' IN 'web development');

I think LOCATE is a better function because it allows you to specify an optional starting position to begin the search and also because its usage is similar to the T-SQL equivalent.
Using LOCATE without specifying the starting position above example would like this:

SELECT LOCATE('ev', 'web development');

and specifying a starting point of say 4 as an example:

SELECT LOCATE('ev', 'web development', 4);

delicious delicious | digg digg

Category: MySQL | Comments : 3

MySQL connection error

Saturday, 12th May 2007 9:12pm
Tonight when I tried to run the MySQL Query Browser on Windows I got the following error message:

MySql Error Number 2003, can't connect to MySQL server on 'localhost'

When I pinged the server as suggested by the dialog box, everything looked alright but still I could not connect to MySQL. I then checked to see if the MySQL service was running in the control panel's Administrative Tools/Services section. For some reason the MySQL service had stopped running and on manually re-starting the service I managed to once again connect to MySQL as normal.

delicious delicious | digg digg

Category: MySQL | Comments : 3

Reporting Tools for MySQL

Friday, 8th December 2006 7:30pm
Over the past week or so I have been looking at free reporting tools to use with MySQL. I have just managed to find two which are LGX Report and Agata Report. but I think there could a lot more out there.

LogiXML offers their cut down report version for free and normally I don't go for cut down versions but this one is a useable and good cut down version. This tool only works on Windows and requires the IIS web server to be running because that's the web server the report engine uses and you also need the .Net framework 1.1 or 2.0 installed on your machine.

Let me point out that LGX caters for a number of other databases and SQL server could be its main target but it works pretty well with MySQL producing some good reports in no time at all with features including cross-tab reports, drill-down and drill-through reports.

The other tool that I tried out is Agata Report. Agata is open source and runs on both Windows and Linux and I found that although it lacks the fanciness of LGX Report you are able to produce your reports with relative easy.

You can also export the reports to pdf, xml etc which I think is good and the fact that you can go through the php classes was a winner for me.

The Agata website is in both Portuguese and English and may be a bit confusing at times(especially if you don't know Portuguese) but with time you can easily navigate to the areas you want once you get to know the site a little bit more.

The other tool that I never got time to play with but which looks good is Ariacom Business Reports which has a free version for personal use.

delicious delicious | digg digg

Category: MySQL | Comments : 10

Annoying MySQL Fatal error

Tuesday, 25th July 2006 9:38pm
Following my recent re-installation of Windows I had to install a new instance of Apache. I got PHP working alright but on trying to access my MySQL database through a login script I got this error: Fatal error: Call to undefined function mysql_connect() in C:\webroot\MySqlConnector.php on line 26.

To resolve this I had to copy the libmysql.dll to the Apache Group\Apache2\bin\ folder. I found this to be very unusual since I have never needed to do this before. The most important thing is that the issue is now gone.

delicious delicious | digg digg

Category: MySQL | Comments : 0

MySQL equivalent of Top

Thursday, 23rd March 2006 6:08am
How many times have I have tried to use a SQL server command in MySql and it has failed? Quite a few. This time the command involved was 'top'. Something you would normally use in SQL server like 'select top 8' doesn't work in MySQL but you should use the equivalent of 'top' which is 'limit'.

So while for SQL server you would say:

select top 8 * from products

the equivalent of this in MySQL would be:

select * from products limit 8

Please note that limit can have two arguments, the first one indicates the first row to return in the returned result set and the other argument the maximum number of rows to return but I will not discuss that in this posting.

delicious delicious | digg digg

Category: MySQL | Comments : 0