Creating a simple string reversing user defined function(UDF) in C, for MySQL and running on CentOS

For an upcomming project, I need to use user defined functions in MySQL. Therefore I decided to figure out how it works, and what the optimal method of compiling an extention with the desired function, on CentOS would be.

To acquire the includes etc, download and install the MySQL source.

You need to customize the header called mysql_version.h according to your version of MySQL:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

#ifndef _mysql_version_h #define _mysql_version_h #ifdef _CUSTOMCONFIG_ #include <custom_conf.h> #else #define PROTOCOL_VERSION 5 #define MYSQL_SERVER_VERSION "5.0.77" #define MYSQL_BASE_VERSION "mysqld-5.0" #define MYSQL_SERVER_SUFFIX_DEF  "" #define FRM_VER 0 #define MYSQL_VERSION_ID 0 #define MYSQL_PORT 3306 #define MYSQL_PORT_DEFAULT 3306 #define MYSQL_UNIX_ADDR "/tmp/mysql.sock" #define MYSQL_CONFIG_NAME "my" #define MYSQL_COMPILATION_COMMENT ""   #endif /* _CUSTOMCONFIG_ */   #ifndef LICENSE #define LICENSE GPL #endif /* LICENSE */   #endif /* _mysql_version_h */ </custom_conf.h>

Now you can include mysql.h to your C-file, and start coding your UDF, I've coded a simple function for reversing a string, code:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

#include <stdlib.h> #include <stdio.h> #include <string.h> typedef unsigned long long ulonglong; typedef long long longlong; #include <mysql.h> #include <ctype.h>     my_bool Inverse_init( UDF_INIT* initid, UDF_ARGS* args, char* message ); void Inverse_deinit( UDF_INIT* initid ); char* Inverse( UDF_INIT* initid, UDF_ARGS* args, char* result, unsigned long* length, char* is_null, char* error );     my_bool Inverse_init( UDF_INIT* initid, UDF_ARGS* args, char* message ) {  if ( ( args->arg_count != 1 ) || ( args->arg_type[0] != STRING_RESULT ) )  {   strcpy( message, "Wrong argument type" );   return 1;  }    return 0; }     void Inverse_deinit( UDF_INIT* initid ) { }     char* Inverse( UDF_INIT* initid, UDF_ARGS* args, char* result, unsigned long* length, char* is_null, char* error ) {  const char* arg = args->args[0];    (*length) = strlen( arg );    int i;  for ( i = (*length); i >= 0; i-- )  {   result[(*length) - i - 1] = arg[i];  }    return result; }

 I have not made this code to be compatible with Unicode, but I intend to make a post in the near future, explaining Unicode.

We need to compile this, so let's fire up our CentOS and go the the directory where we saved the C-File(save it in your MySQL source directory somewhere), and compile using the following command:
gcc -shared -o filenamehere.so filenamehere.c -I ../include/

Obviously you want to customize the include directory parameter to lead to the include directory, from the directory where your file is, the include directories is: */mysqlsource/include/

So now that we have the shared object(.so) file, we can go ahead and put it in the following directory: */usr/lib/ 
then restart our mysqld service:
service mysqld restart

You can now load up your SQLyog, and define your newly created function using:
CREATE FUNCTION Inverse RETURNS STRING SONAME 'filenamehere.so'


And now your UDF is ready for use!