Archive for MySQL
MySQL equivalent of PATINDEX
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
digg
MySQL connection error
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.
Reporting Tools for MySQL
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.
Annoying MySQL Fatal error
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.
MySQL equivalent of Top
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.
