DaniWeb IT Discussion Community

DaniWeb IT Discussion Community (http://www.daniweb.com/forums/)

- MySQL (http://www.daniweb.com/forums/forum126.html)

- - UDF in MySQL (http://www.daniweb.com/forums/thread123247.html)

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

Re: UDF in MySQL

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

Re: UDF in MySQL

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:

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

Re: UDF in MySQL

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

Re: UDF in MySQL

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

Re: UDF in MySQL

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

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.

Re: UDF in MySQL

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

Thanks

Re: UDF in MySQL

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

Quote:

Originally Posted by tesuji (Post 604449)

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

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.

Re: UDF in MySQL

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