Adding a New User-Defined Function
2818
1. Obtain the development source for MySQL 5.0. See
Section 2.5, “How to Get MySQL”
.
2. Obtain the
CMake
build utility, if necessary, from
http://www.cmake.org
. (Version 2.6 or later is
required).
3. In the source tree, 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.
4. 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)
5. Create the VC project and solution files:
cmake -G "<Generator>"
Invoking
cmake --help
shows you a list of valid Generators.
6. Create
udf_example.dll
:
devenv udf_example.sln /build Release
After the shared object file has been installed, notify
mysqld
about the new functions with the following
statements. If object files have a suffix different from
.so
on your system, substitute the correct suffix
throughout (for example,
.dll
on Windows).
mysql>
CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';
mysql>
CREATE FUNCTION myfunc_double RETURNS REAL SONAME 'udf_example.so';
mysql>
CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME 'udf_example.so';
mysql>
CREATE FUNCTION sequence RETURNS INTEGER SONAME 'udf_example.so';
mysql>
CREATE FUNCTION lookup RETURNS STRING SONAME 'udf_example.so';
mysql>
CREATE FUNCTION reverse_lookup
->
RETURNS STRING SONAME 'udf_example.so';
mysql>
CREATE AGGREGATE FUNCTION avgcost
->
RETURNS REAL SONAME 'udf_example.so';
To delete functions, use
DROP FUNCTION
:
mysql>
DROP FUNCTION metaphon;
mysql>
DROP FUNCTION myfunc_double;
mysql>
DROP FUNCTION myfunc_int;
mysql>
DROP FUNCTION sequence;
mysql>
DROP FUNCTION lookup;
mysql>
DROP FUNCTION reverse_lookup;
mysql>
DROP FUNCTION avgcost;
The
CREATE FUNCTION
and
DROP FUNCTION
statements update the
func
system table in the
mysql
database. The function's name, type and shared library name are saved in the table. You must
have the
INSERT
[577]
or
DELETE
[577]
privilege for the
mysql
database to create or drop functions,
respectively.
You should not use
CREATE FUNCTION
to add a function that has previously been created. If you
need to reinstall a function, you should remove it with
DROP FUNCTION
and then reinstall it with
CREATE FUNCTION
. You would need to do this, for example, if you recompile a new version of your
function, so that
mysqld
gets the new version. Otherwise, the server continues to use the old version.
An active function is one that has been loaded with
CREATE FUNCTION
and not removed with
DROP
FUNCTION
. All active functions are reloaded each time the server starts, unless you start
mysqld
with
the
--skip-grant-tables
[420]
option. In this case, UDF initialization is skipped and UDFs are
unavailable.
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...