User Name Password Register
DaniWeb IT Discussion Community
Home
Forums
Tutorials
Code Snippets
Blogs
Link Directory
All
•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 374,147 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,492 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Join our community!
Please support our MySQL advertiser:
Views: 1393 | Replies: 17
Reply
Page 1 of 2 1 2 >
•
•
Join Date: Aug 2007
Posts: 141
Reputation: tanha is an unknown quantity at this point 
Rep Power: 0
Solved Threads: 0
tanha tanha is offline Offline
Junior Poster

UDF in MySQL

  #1  
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?
AddThis Social Bookmark Button
Reply With Quote  
•
•
Join Date: Aug 2007
Posts: 141
Reputation: tanha is an unknown quantity at this point 
Rep Power: 0
Solved Threads: 0
tanha tanha is offline Offline
Junior Poster

Re: UDF in MySQL

  #2  
May 9th, 2008
hi again,
any idea how to make UDF in MySQL ???
Reply With Quote  
•
•
Join Date: Dec 2004
Location: London or Slovakia
Posts: 1,907
Reputation: peter_budo has a spectacular aura about peter_budo has a spectacular aura about peter_budo has a spectacular aura about 
Rep Power: 9
Solved Threads: 216
Moderator
Featured Poster
peter_budo's Avatar
peter_budo peter_budo is offline Offline
Posting Virtuoso

Re: UDF in MySQL

  #3  
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)
If we helped you to solve your problem, answered your question please mark your post as SOLVED.
Reply With Quote  
•
•
Join Date: Apr 2008
Posts: 275
Reputation: tesuji is an unknown quantity at this point 
Rep Power: 1
Solved Threads: 37
tesuji tesuji is offline Offline
Posting Whiz in Training

Re: UDF in MySQL

  #4  
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) 
  1. CREATE FUNCTION avgg(*)
  2. returns float
  3. ap float
  4. SELECT avg(price) FROM goods INTO ap
  5. RETURN ap
  6.  
  7. -- usage
  8. 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
Reply With Quote  
•
•
Join Date: Aug 2007
Posts: 141
Reputation: tanha is an unknown quantity at this point 
Rep Power: 0
Solved Threads: 0
tanha tanha is offline Offline
Junior Poster

Re: UDF in MySQL

  #5  
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
Reply With Quote  
•
•
Join Date: Apr 2008
Posts: 275
Reputation: tesuji is an unknown quantity at this point 
Rep Power: 1
Solved Threads: 37
tesuji tesuji is offline Offline
Posting Whiz in Training

Re: UDF in MySQL

  #6  
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
Reply With Quote  
•
•
Join Date: Apr 2008
Posts: 275
Reputation: tesuji is an unknown quantity at this point 
Rep Power: 1
Solved Threads: 37
tesuji tesuji is offline Offline
Posting Whiz in Training

Re: UDF in MySQL

  #7  
May 10th, 2008
Here is an UDF example which runs on SQL Anywhere 9:
Help with Code Tags
sql Syntax (Toggle Plain Text) 
  1. CREATE FUNCTION upperFirstChar(IN isc VARCHAR(1000))
  2.   RETURNS VARCHAR(1000)
  3. /* Usage:
  4.    select upperFirstChar('hello!') AS 'Capitalized 1st Character';
  5.    Result:
  6.    Capitalized 1st Character
  7.    -------------------------
  8.    Hello!
  9. */
  10.   BEGIN
  11.      DECLARE rtc VARCHAR(1000);
  12.      SELECT UPPER(SUBSTRING(isc, 1, 1)) + LOWER(SUBSTRING(isc, 2, 
  13.          LENGTH(isc)-1)) INTO rtc;
  14.      RETURN rtc;
  15.   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.
Reply With Quote  
•
•
Join Date: Aug 2007
Posts: 141
Reputation: tanha is an unknown quantity at this point 
Rep Power: 0
Solved Threads: 0
tanha tanha is offline Offline
Junior Poster

Re: UDF in MySQL

  #8  
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
Reply With Quote  
•
•
Join Date: Aug 2007
Posts: 141
Reputation: tanha is an unknown quantity at this point 
Rep Power: 0
Solved Threads: 0
tanha tanha is offline Offline
Junior Poster

Re: UDF in MySQL

  #9  
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 View Post
Here is an UDF example which runs on SQL Anywhere 9:
Help with Code Tags
sql Syntax (Toggle Plain Text) 
  1. CREATE FUNCTION upperFirstChar(IN isc VARCHAR(1000))
  2.   RETURNS VARCHAR(1000)
  3. /* Usage:
  4.    select upperFirstChar('hello!') AS 'Capitalized 1st Character';
  5.    Result:
  6.    Capitalized 1st Character
  7.    -------------------------
  8.    Hello!
  9. */
  10.   BEGIN
  11.      DECLARE rtc VARCHAR(1000);
  12.      SELECT UPPER(SUBSTRING(isc, 1, 1)) + LOWER(SUBSTRING(isc, 2, 
  13.          LENGTH(isc)-1)) INTO rtc;
  14.      RETURN rtc;
  15.   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.
Reply With Quote  
•
•
Join Date: Apr 2008
Posts: 275
Reputation: tesuji is an unknown quantity at this point 
Rep Power: 1
Solved Threads: 37
tesuji tesuji is offline 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 7:13 am.
Reply With Quote  
Reply
Page 1 of 2 1 2 >

•
•
•
•

Only community members can participate in forum threads. You must register or log in to contribute.

Register
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

 

•
•
•
•
•
•
•
•
DaniWeb MySQL Marketplace
•
•
•
•
Thread Tools Display Modes
Linear Mode Linear Mode

•
•
•
•
c++ linux mmorpg mysql ogre pirate
Other Threads in the MySQL Forum

Advertising Opportunities on DaniWeb
Contact Us - Newsletter Archive - Sitemap - Privacy Statement
All times are GMT -4. The time now is 3:19 am.
Forum system based on vBulletin Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
©2003 - 2008 DaniWeb® LLC