LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - General > LinuxQuestions.org Member Success Stories
User Name
Password
LinuxQuestions.org Member Success Stories Just spent four hours configuring your favorite program? Just figured out a Linux problem that has been stumping you for months?
Post your Linux Success Stories here.

Notices

Reply
 
Search this Thread
Old 06-11-2004, 11:59 AM   #1
mchirico
LQ Newbie
 
Registered: Mar 2004
Distribution: Redhat and direct source
Posts: 27

Rep: Reputation: 15
FYI: Creating User Defined Functions for MySQL


I found the following interesting. For better formatting or other Tips please reference the following (TIP 27) :
http://prdownloads.sourceforge.net/s...l.txt?download



Adding and Compiling a User-Defined function for Linux

Here are some examples that can be compiled and used with MySQL 4.1. See below for how to install.
First, just a taste of what they look like:


mysql> select lookup("www.yahoo.com");

+----------------------------+
| lookup("www.yahoo.com") |
+----------------------------+
| 216.109.118.71 |
+----------------------------+
1 row in set (0.02 sec)

and

mysql> select reverse_lookup("216.109.118");
+-------------------------------------+
| reverse_lookup("216.109.118.79") |
+-------------------------------------+
| p16.www.dcn.yahoo.com |
+-------------------------------------+
1 row in set (0.02 sec)


Also

mysql> select sequence(3);

+-------------+
| sequence(3) |
+-------------+
| 4 |
+-------------+
1 row in set (0.01 sec)

mysql> create table junk (a varchar(1));
mysql> insert into junk (a) values ('a'),('b'),('c'),('d'),('e');



mysql> select sequence(),a from junk;
select sequence(),a from junk;
+-----------------+------+
| sequence() | a |
+-----------------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+-----------------+------+
5 rows in set (0.00 sec)





An example of an aggregate function:


mysql> create table cost (inum int, cost double);

mysql> insert into cost (inum,cost) values (3,4.5);

mysql> insert into cost (inum,cost) values (10,21.3);

mysql> select avgcost(inum,cost) from cost;
select avgcost(inum,cost) from cost;
+--------------------+
| avgcost(inum,cost) |
+--------------------+
| 17.4231 |
+--------------------+
1 row in set (0.00 sec)


OK, HOW TO COMPILE:

For best results mysql-4.1.2-alpha or above configured with the following option:

--with-mysqld-ldflags=-rdynamic

The full line of my configure is as follows since I use openssl as well.

STEP A:

./configure --with-openssl --enable-thread-safe-client --with-mysqld-ldflags=-rdynamic

make

make install

/etc/init.d/mysql restart

( Note: if "/etc/init.d/mysql" does not exist see TIP 20,
http://prdownloads.sourceforge.net/s...l.txt?download )



STEP B:

Under "mysql-4.1.2-alpha/sql" issue the following command.

# make udf_example.so

STEP C:

As root copy the file to a directory where mysql can find it.

# cp udf_example.so /usr/lib/udf_example.so


STEP D:

Load mysql. You may want to look at udf_example.cc, since it as instructions
as well. From here issue the following commands:



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";


And the functions can be deleted by the following:

mysql> DROP FUNCTION metaphon;
mysql> DROP FUNCTION myfunc_double;
mysql> DROP FUNCTION myfunc_int;
mysql> DROP FUNCTION lookup;
mysql> DROP FUNCTION reverse_lookup;
mysql> DROP FUNCTION avgcost;


That's it. Just run the functions above. These can serve as templates for writing
your own.

Anyway, I found the following interesting, since it comes with the Linux src and seems to
work well on 4.1.2.

Regards,

Mike Chirico
 
Old 06-11-2004, 08:51 PM   #2
trickykid
Guru
 
Registered: Jan 2001
Posts: 24,133

Rep: Reputation: 197Reputation: 197
Moved: Seems more like a Success Story rather than a question, moved to more appropiate forum.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to send user defined signal in linux feetyouwell Linux - Software 6 12-26-2007 04:40 AM
User defined protocols with Guarddog Shafted Linux - Security 4 09-01-2005 12:56 PM
FYI: mysql setup tutorial inl. chrooting markus1982 Linux - Security 0 03-07-2004 10:53 AM
user defined variables linuxdev Linux - Newbie 4 02-09-2004 12:43 PM
C++: How to use a namespace in a user-defined library? Gethyn Programming 9 08-15-2003 05:29 AM


All times are GMT -5. The time now is 05:56 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration