UDF in MySQL

[tanha]

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

[tanha]

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

[peter_budo]

Something like this? http://en.oreilly.com/mysql2008/public/schedule/detail/128

[tesuji]

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

[tanha]

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

[tesuji]

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

[tesuji]

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.

[tanha]

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

Thanks

[tanha]

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

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.

[tesuji]

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

[tanha]

Thanks again for the replying and nice saying...

[tanha]

Hi tesuji,
Hope doing fine, I tried much, but still I am lost how to write UDF for MySQL, I don't like to write complex function now, I need just one sample example written in C/C++, and then use that in MySQL working...

I need this example and guide as my start point...

Therefore kindly request you, if possible post here a sample UDF written in C, and then the steps how to use of that in MySQL...
That would be very kind of you...
Thanks

[tanha]

Hi, Hi, Hiiiiiiiiiiiiiiiiiiiiiiii everyone,

to be honest I studied much, but still I dont know how to create UDF in MySQL, so there should be some one did this for him/her self, plz any idea ???

[tanha]

Just I need a simple UDF in MySQL something like uppercase firstletter ...

[tanha]

Although this post is viewed by more than 800 persons, still not solved. As I mentioned I am using WAMP package, whuch has mysql verion 5.0.4 and also PhpMyAdmin and I dont know what is wrong with the following procedure:

CREATE PROCEDURE procedure1 /* name */
(IN parameter1 INTEGER) /* parameters */
BEGIN /* start of block */
DECLARE variable1 CHAR(10); /* variables */
IF parameter1 = 17 THEN /* start of IF */
SET variable1 = 'birds'; /* assignment */
ELSE
SET variable1 = 'beasts'; /* assignment */
END IF; /* end of IF */
INSERT INTO table1 VALUES (variable1);/* statement */
END /* end of block */

[peter_budo]

Although this post is viewed by more than 800 persons, still not solved.

Any idea how many people of this 800 are actually daniweb members? Or how many come accross this post by google search? You do not know, neither me...

[tesuji]

Hi tanha,

till this day you have been asking for UDF sample. Now you post stored procedure. I HOPE you have not mistaken UDF for stored procedure. As for UDFs, I have sent you some information how such thinks can be done with MySQL, and as you may have seen it's really a heavy job there as compared to standard SQL 1999 UDF's where it s absolutely simple. You have got such a SQL1999 sample from me. Back to your stored procedure, I believe it's also a good idea to tell us the respective error message you might have received when trying to execute this procedure.

krs,
tesu

Although this post is viewed by more than 800 persons, still not solved. As I mentioned I am using WAMP package, whuch has mysql verion 5.0.4 and also PhpMyAdmin and I dont know what is wrong with the following procedure:

CREATE PROCEDURE procedure1 /* name */
(IN parameter1 INTEGER) /* parameters */
BEGIN /* start of block */
DECLARE variable1 CHAR(10); /* variables */
IF parameter1 = 17 THEN /* start of IF */
SET variable1 = 'birds'; /* assignment */
ELSE
SET variable1 = 'beasts'; /* assignment */
END IF; /* end of IF */
INSERT INTO table1 VALUES (variable1);/* statement */
END /* end of block */

[tanha]

Yes, you are right that it is not that what I wanted ... and I tried much to know writing a sample UDF in C/C++ and then implement it in MySQL, but I could not....

about the procedure problem, I dont know how tell MySQL that those semicolons ';' are not separate statements, but MySQL consider each line which terminate with semicolons as separate statement and execute it, this cause the error ...

NOTE:
if again, possible to help me writing UDF in C/C++ , that would be very kind of u

Thanks