 |
|
 |
| |
Developer on focus
Anton Zamov is a dipl. engineer with more than 6 years of active professional
experience and many awards ...
read more>>
|
|
 |
 |
 |
|
|
|
|
| |
|
MySQL: Adding and Compiling a User-Defined function
|
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");
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
select reverse_lookup("216.109.118.79");
+----------------------------------+
| reverse_lookup("216.109.118.79") |
+----------------------------------+
| p16.www.dcn.yahoo.com |
+----------------------------------+
1 row in set (0.02 sec)
Also
mysql> select sequence(3);
select sequence(3);
+-------------+
| sequence(3) |
+-------------+
| 4 |
+-------------+
1 row in set (0.01 sec)
mysql> select sequence(sequence(3));
select sequence(sequence(3));
+-----------------------+
| sequence(sequence(3)) |
+-----------------------+
| 5 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select sequence(3.4);
select sequence(3.4);
+---------------+
| sequence(3.4) |
+---------------+
| 4 |
+---------------+
1 row in set (0.00 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);
create table cost (inum int, cost double);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into cost (inum,cost) values (3,4.5);
insert into cost (inum,cost) values (3,4.5);
Query OK, 1 row affected (0.00 sec)
mysql> insert into cost (inum,cost) values (10,21.3);
insert into cost (inum,cost) values (10,21.3);
Query OK, 1 row affected (0.00 sec)
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:) )
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.
TIP 28:
Loading Data into Tables from Text Files.
Assume you have the following table.
CREATE TABLE loadtest (
pkey int(11) NOT NULL auto_increment,
name varchar(20),
exam int,
score int,
timeEnter timestamp(14),
PRIMARY KEY (pkey)
);
And you have the following formatted text file as shown
below with the unix "tail" command:
$ tail /tmp/out.txt
'name22999990',2,94
'name22999991',3,93
'name22999992',0,91
'name22999993',1,93
'name22999994',2,90
'name22999995',3,93
'name22999996',0,93
'name22999997',1,89
'name22999998',2,85
'name22999999',3,88
NOTE: loadtest contains the "pkey" and "timeEnter" fields which are not
present in the "/tmp/out.txt" file. Therefore, to successfully load
the specific fields issue the following:
mysql> load data infile '/tmp/out.txt' into table loadtest
fields terminated by ',' (name,exam,score);
|
About the author of this programming example or tutorial:
Mike Chirico (mchirico@users.sourceforge.net)
Copyright (c) 2004 (GPU Free Documentation License)
Last Updated: Tue Jul 20 12:14:51 EDT 2004
|
|
|
|
|
|
|
|
