urldecode for mysql

11 April 2007

It is possible to get the urldecode() or urlencode function in MySql, the same way it exist in PHP as url_decode and url_encode.

You just have to install the User Defined Function : UDF collection for MySQL created by Michael Wallner from IWorks.

There are just a few points to help the installation :

  • Decompress the sources : tar xf libmysqludf-0.3.tar.gz
  • Go into the directory cd libmysqludf-0.3 and read the INSTALL file !
  • If you don’t have them get MySql sources as well : yum install mysql-devel
  • On a RPM style MySql install do a ./configure --prefix=/usr/lib/mysql --includedir=/usr/include/mysql --libdir=/usr/lib (not sure if it is all really needed but it worked on a Fedora Core 5).
  • make
  • sudo make install
  • sudo /etc/init.d/mysqld restart (maybe not needed)
  • Open a mysql client as root if you don’t have the INSERT privilege for the mysql database (CREATE FUNCTION Syntax) : mysql -u root -p
  • There is the trick : CREATE FUNCTION urldecode RETURNS STRING SONAME 'libmysqludf_urldecode.so'; Notice the function name is lower case !!!
  • If you want to remove the function use DROP and then sudo make uninstall

Then you are free to url decode or url encode right in you SQL query ! It is very helpfull when you work on Apache log files !
I search for a Linux command line tool to urldecode, but I couldn’t find one. Still here are some good ressources :

Maybe a solution would be (from Perl FAQ ) :

The best source of detailed information on URI encoding is RFC 2396. Basically, the following substitutions do it :

    s/([^\w()'*~!.-])/sprintf '%%%02x', ord $1/eg;   # encode

    s/%([A-Fa-f\d]{2})/chr hex $1/eg;                # decode
	s/%([[:xdigit:]]{2})/chr hex $1/eg;          # same thing

java 6 on Linux and JPackage

27 March 2007

I wanted a clean install that use alternatives. So I go to JPackage … the point is that they are a bit outdated on a few points :

  • The recommended method “Rebuild the package” doesn’t fit anymore with Java 6. There is only a sun-compat packaeg and no “no-src” package.
  • The repository for Fedora “ready for use” is using mirrors pointing to the 1.6 JPackage version that doesn’t features Java 6. You have to manually write the yum.d/jpackage.repo to use JPackage 1.7.
  • The RPM for java-1.6.0-sun-compat is in the non-free section of 1.7 beta. (Note : there is a 5.0 that is supposed to match Java 5, but no 6.0 so far.

Anyway, I installed the JDK 6.0 for AMD64 from Sun and then the java-1.6.0-sun-compat and it went well. Installed Eclipse 3.2 with yum to try out. It seems OK so far.


Nulls

22 August 2005

Comments on Nulls : Nothing to worry about by Phil Zoio as published in Oracle mag (link to global post)
First you should ask what it means from a business point of view to have a null ! Information missing ? not applicable ?
Then COALESCE, IS NULL, IS NOT NULL are the most obvious functions to avoid problems.
Would you feel safe by using only “NOT NULL” fields in your tables ? No : an outer join will bring them back easily.
In scalar expression : SAL+NULL results in NULL, but in boolean expression : SAL (SELECT MAX(SAL) FROM A) and WHERE SAL > ALL (SELECT SAL FROM A) if A is empty, the first query returns nothing (UNKNOWN) but the second will always be TRUE, because SAL > ALL (NULL) is always true.
Is SUM(SAL+COMM) the same as SUM(SAL)+SUM(COMM) ?? No : Nulls are ignored by aggregates functions !
More tricky : WHERE EMPNO IN (SELECT MGR FROM A) if a MGR is NULL : what do you expect ? Nothing : since EMPNO IN (…, NULL, …) is UNKNOWN or FALSE, the query returns nothing.
Conclusion : there are more than one way to write a query : choose the right one …