MySQL

UDF in MySQL

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Aug 2007

Posts: 199

Reputation: tanha is an unknown quantity at this point

Solved Threads: 0

tanha

Offline

Junior Poster

UDF in MySQL

May 8th, 2008

Hi.
I want to know how it is possible to write your own function "UDF" in MySQL? and how use that UDF in MySQL?

•

Join Date: Aug 2007

Posts: 199

Reputation: tanha is an unknown quantity at this point

Solved Threads: 0

tanha

Offline

Junior Poster

Re: UDF in MySQL

May 9th, 2008

hi again,
any idea how to make UDF in MySQL ???

•

Join Date: Dec 2004

Posts: 4,190

Reputation: peter_budo has much to be proud of

Solved Threads: 483

peter_budo peter_budo is offline

Offline

Code tags enforcer

Re: UDF in MySQL

May 9th, 2008

Something like this? http://en.oreilly.com/mysql2008/publ...ule/detail/128

Learn to see in another's calamity the ills which you should avoid.
Publilius Syrus
(~100 BC)
LJC - London Java Community, Graduate & Undergraduate Software Development Community, JAVAWUG (Java Web User Group), The London Android Group

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

Re: UDF in MySQL

May 9th, 2008

hi,
MySQL does not support user defined functions (UDF) in the sense of standard SQL, which, for example, allow such a declaration in its own PSM language:

 Help with Code Tags 
  sql Syntax (Toggle Plain Text) 
CREATE FUNCTION avgg(*)
RETURNS FLOAT
ap FLOAT
SELECT avg(price) FROM goods INTO ap
return ap
 
-- usage
SELECT id FROM goods WHERE price < avgg(*)
create function avgg(*)
returns float
ap float
select avg(price) from goods into ap
return ap

-- usage
select id from goods where price < avgg(*)

In MySQL kind of UDF can be defined as a plain C program. Such C-coded program must be linked dynamically, there is also special header handling. This approach is rather complicated and error prone. Once Borland's interbase had had same approach.

brs,
tesu

•

Join Date: Aug 2007

Posts: 199

Reputation: tanha is an unknown quantity at this point

Solved Threads: 0

tanha

Offline

Junior Poster

Re: UDF in MySQL

May 10th, 2008

Thanks for replying both...
Respected tesuji tesuji,
could you please guide me how to write a sample UDF, like Uppercase first letter in C, and then how to configure that UDF using in MySQL.

NOTE:
I know that the Ucase function exist in MySQL, but as an example for me to learn writing UDF...

Thanks

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

Re: UDF in MySQL

May 10th, 2008

well tanha,
though i ve written UDFs for Sybase Databases a good many times, it s rather time-consuming doing that within MySQL. You may have a look at
http://dev.mysql.com/doc/refman/5.0/en/adding-udf.html
to get an impression of the hard work to be done for it (as opposed to the convenient way when programming UDF by using SQL1999 standards).
krs,
tesu

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

Re: UDF in MySQL

May 10th, 2008

Here is an UDF example which runs on SQL Anywhere 9:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
CREATE FUNCTION upperFirstChar(IN isc VARCHAR(1000))
  RETURNS VARCHAR(1000)
/* Usage:
   select upperFirstChar('hello!') AS 'Capitalized 1st Character';
   Result:
   Capitalized 1st Character
   -------------------------
   Hello!
*/
  BEGIN
     DECLARE rtc VARCHAR(1000);
     SELECT UPPER(SUBSTRING(isc, 1, 1)) + LOWER(SUBSTRING(isc, 2, 
         LENGTH(isc)-1)) INTO rtc;
     RETURN rtc;
  END;
CREATE FUNCTION upperFirstChar(IN isc VARCHAR(1000))
  RETURNS VARCHAR(1000)
/* Usage:
   select upperFirstChar('hello!') AS 'Capitalized 1st Character';
   Result:
   Capitalized 1st Character
   -------------------------
   Hello!
*/
  BEGIN
     DECLARE rtc VARCHAR(1000);
     SELECT UPPER(SUBSTRING(isc, 1, 1)) + LOWER(SUBSTRING(isc, 2, 
         LENGTH(isc)-1)) INTO rtc;
     RETURN rtc;
  END;

I use select upper(substring(... to capitalize first char. You can also use this select within MySQL because all char functions are also available there.

•

Join Date: Aug 2007

Posts: 199

Reputation: tanha is an unknown quantity at this point

Solved Threads: 0

tanha

Offline

Junior Poster

Re: UDF in MySQL

May 10th, 2008

Thanks again,
I will go through the link you posted, and if still could not get the idea, then will post again...

Thanks

•

Join Date: Aug 2007

Posts: 199

Reputation: tanha is an unknown quantity at this point

Solved Threads: 0

tanha

Offline

Junior Poster

Re: UDF in MySQL

May 11th, 2008

sir,
I can not create the below function in MySQL...
what I should I do to just create the below function in MySQL 5.0.41. could you just guide through that

•

Originally Posted by tesuji

Here is an UDF example which runs on SQL Anywhere 9:
Help with Code Tags

sql Syntax (Toggle Plain Text)
CREATE FUNCTION upperFirstChar(IN isc VARCHAR(1000))
  RETURNS VARCHAR(1000)
/* Usage:
   select upperFirstChar('hello!') AS 'Capitalized 1st Character';
   Result:
   Capitalized 1st Character
   -------------------------
   Hello!
*/
  BEGIN
     DECLARE rtc VARCHAR(1000);
     SELECT UPPER(SUBSTRING(isc, 1, 1)) + LOWER(SUBSTRING(isc, 2, 
         LENGTH(isc)-1)) INTO rtc;
     RETURN rtc;
  END;
CREATE FUNCTION upperFirstChar(IN isc VARCHAR(1000)) RETURNS VARCHAR(1000) /* Usage: select upperFirstChar('hello!') AS 'Capitalized 1st Character'; Result: Capitalized 1st Character ------------------------- Hello! */ BEGIN DECLARE rtc VARCHAR(1000); SELECT UPPER(SUBSTRING(isc, 1, 1)) + LOWER(SUBSTRING(isc, 2, LENGTH(isc)-1)) INTO rtc; RETURN rtc; END;
I use select upper(substring(... to capitalize first char. You can also use this select within MySQL because all char functions are also available there.

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

Re: UDF in MySQL

#10

May 11th, 2008

Hi tanha,
the given UDF should only be an example on how to program UDF using SQL Standard features. Since SQL 2003 (not 1999) there are UDFs defined, and they can be created with a rather new procedural language called PSM (Persistent Storage Moduls now part of SQL Standard, somehow weird name for programming language), and today, nearly all databases support this PSM, e.g. MS SQL Server, Oracle (within PL/SQL), DB/2, Sybase Databases etc. So far, MySQL 5 does not support PSM, maybe further version will do so.

Therefore, it is impossible to create this UDF example on a MySQL Database.

As already stated, in to-day MySQL there is an other approach for programming UDF, which is based on C programming and dynamically loaded libraries.

http://dev.mysql.com/doc/refman/5.0/en/adding-udf.html[/url] explains much about those UDFs.

http://www.mysqludf.org/lib_mysqludf_str/index.php[/url] has a great collection of already programmed UDF.

There you can find a function str_ucfirst (makes uppercase the first character of the string) which does exactly the task you are looking for. You can download the source, then take a look at the three programming-parts the function is made up. You should carefully follow the instructions on how to create and install such library. The most important step is that the dynamically loaded library must be installed on the computer where the MySQL server is running. I hope, you have access to your server.

krs,
tesu

Last edited by tesuji; May 11th, 2008 at 8:13 am.