UDF Solution for MySQL in Windows 7

User-Defined functions (UDF) are functions in MySQL that behaves just like a native (included in the core) of MySQL, such as MAX() or SUM() functions. The main advantage is that UDFs are much faster than stored procedures and the usage is exactly the same. The disadvantage is that UDF are written in C or C++, so it is necessary to compile separately and then install it in MySql. This tutorial is about how to create a new solution for compiling UDFs in x64 and x86 machines and how to install it in MySQL.


Introduction

The chosen environment is Visual Studio 2005 (other versions may be used as well), Windows 7 and, for unit testing, the plugin Visual Assert cfix but this will be explained in other post.

Prerequisites

- Visual Studio 2005 Team Studio. Remember to enable the x64 features in the installation.

- Microsoft Windows SDK for Windows 7 and .NET Framework from http://www.microsoft.com/en-us/download/details.aspx?id=3138.

- Install MySql and the source from here http://mirror.neu.edu.cn/mysql/doc/refman/5.1/en/installing-source-tree.html.

- CMake from http://www.cmake.org. (Version 2.4.2 or later is required).

Generate solution

1.- In the source repository, look in the sql directory. There are files named udf_example.def udf_example.c there. Copy both files from this directory to your working directory.

2.- Create a CMake makefile (CMakeLists.txt) with these contents:

PROJECT(udf_example)

# Path for MySQL include directory
INCLUDE_DIRECTORIES("c:/mysql/include")

ADD_DEFINITIONS("-DHAVE_DLOPEN")
ADD_LIBRARY(udf_example MODULE udf_example.c udf_example.def)
TARGET_LINK_LIBRARIES(udf_example wsock32)

3.- cmake -G "Visual Studio 2005"
Invoking cmake --help shows you a list of valid Generators.

4.- Open the generated solution in Visual Studio 2005 and add the necessaries libraries to compile: In “udf_example” -> Properties -> Configuration Properties -> c/c++ -> General. In Additional Include Directories, enter the below two lines (check if the paths are correct in your system):

C:\Program Files\Microsoft SDKs\Windows\v7.0\Include
C:\mysql\include

5.- For C or C++, check the advanced property in “udf_example” -> Properties -> Configuration Properties -> c/c++ -> Advanced. In “Compile As” field, select “Compile as C Code (/TC).

6.- Finally, using the combo in Visual Studio, change between x86 and x64. And, then, build the “udf_example” project. Then, you should be able to compile the solution.

Deploy to MySQL

1. Stop MySQL.
2. Copy the generated library to "c:\mysql\libs\plugin".
3. Start MySQL.
5. Enter in MySQL:

DROP FUNCTION metaphon;CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.dll';

6. And the functions should already be ready to play with them.

And that's it, if someone has any doubt, mail me :).

Happy coding!