Home Mysql Mysql5 MySQL User Interface


Share on Google+Share on Google+

MySQL User Interface

In this section you will read how to define the function Interface, Create function syntax , Drop function Syntax .

MySQL User Interface


In this section you will read how to define the function Interface, Create function syntax , Drop function Syntax .  

MySQL adding new user defined function 
Two ways are available to add new function to MySQL :

  • You can add function with the help of UDF (user defined function) interface. These are complied as object files. After that they can beadded to and removed from the server dynamically by using create and Drop function statements.
  • You can also add as function as native. In mysqld server these native functions can be compiled and they can be available on a permanent basis.

 There are some advantage or disadvantage of User-defined function and Native function:-

  • For user-defined functions, you have to install object files in addition to the server itself. If your function is complied into the server you need not to do that .
  •  But in Native function you have to modify a source distribution. But user-defined function does not require to modify a source distribution . But User-defined function can be add to a MySQL binary distribution.
  • After upgrading the MySQL distribution, you can continue to use previously installed user-defined function, but if in upgrade version UDF interface are changed then you can not. But for native function, you must repeat you modification each and every time you upgrade.

  MySQL user defined function Interface 
 MySQL user-defined function  interface provided the following feature:-

  • The MySQL User-defined function can return string, integer, or real values.
  • User defined function can operate on a single row at a time or aggregate function which operate on groups of rows.
  • You can tell MySQL to force arguments to a given type before passing them to a function.
  • User defined functions check the number and types of the arguments passed to them.

MySQL create function Syntax 
The user-defined function is a way to extend mysql with new function. It is work like a native mysql function as ABS( ) or CONCAT( ).
Example :

SONAME shared_library_name

The "function_name" means this function is identified by this name. And by this name SQL statements can invoke the function  The RETURNS clause is used to define the function return value. Decimal function return the string value and write should be string value. The function "shared_library_name" it is a base name of the shared object file. It contains the code that is implement by the function. For creating a function, you must have the INSERT and privilege for the mysql database because in mysql.func system table a new row is added by the CREATE FUNTION that records the name of function, function type and shared library name. But if this table is not available then run the mysql_fix_privilege_tables script to create it.

MySQL AGGREGATE function works like a native function as SUM( ) or COUNT( ). But your mysql.func table have to contain type column, if it is not available then run the mysql_fix_privilege_tables script to create it.

MySQL DROP Function Syntax
By this statement you can drop the user-defined function named function_name.

DROP FUNCTION function_name

For using this statement you must have the DELETED privilege for the MySQL database. Because this function remove a row from the "mysql.func" system table that record is the function name, function type, and share library name.

MySQL Adding new user defined function 
There are different type of new user-defined functions are given below :-

  • UDF Calling Sequences for simple function
    MySQL user-defined function calling describes the order in which MySQL calls these function. This function return type parameter depend on declare the SQL function xxx( ) to be return string, integer, or real is the create function statement.
    Example for string function
    char *xxx(UDF_INIT *initid, UDF_ARGS *args,
    char *result, unsigned long *length,
    char *is_null, char *error);

    Example of Integer function

    long long xxx(UDF_INIT *initid, UDF_ARGS *args,
    char *is_null, char *error);

    Example of real function

    double xxx(UDF_INIT *initid, UDF_ARGS *args,
    char *is_null, char *error);

The initid parameter points to a UDF_INIT structure, which used to communicate information between functions.

  • UDF Calling Sequence for Aggregate function
    This section describes the order in which MySQL calls these function. This function call the different type of functions are given below :
  • xxx_reset ( )
    It is called when the MySQL finds the first row in a new group. This function should reset any internal summary variables and after that use the functions argument UDF_ARGS as the first value in your internal summary value for the group. 
  • char *xxx_reset(UDF_INIT *initid, UDF_ARGS *args,
    char *is_null, char *error);

Before MySQL 4.1.1 version xxx_reset() function is used but after MySQL 4.1.1User-defined function interface uses xxx_clear( ) instead of xxx_reset().

  • xxx_clear( )
    The xxx_clear( ) function is called to reset the summary result. And this function is called at the beginning for every new group. Here are the some example of xxx_clear( ) function: 
    char *xxx_clear(UDF_INIT *initid, char *is_null, char *error);

Here "Is_null" is set to point to char(0) before the calling xxx_clear( ) function. If something happen wrong then the value can be stored in the variable to which the error argument points. and it points to a single byte variable not a string buffer.

  • xxx_add( )
    The xxx_add( )function is used to call for the all row, which belongs to same group but not the first row. The function can be used to add the value in UDF_ARGS argument to your internal variable. Here are the some example of xxx_add( ) function. 
    char *xxx_add(UDF_INIT *initid, UDF_ARGS *args,
    char *is_null, char *error);

The user-defined function use the pointer argument to is_null and error are the same call function for the xxx_reset( ), xxx_clear( ) and xxx_add( ) . 

  • UDF Argument processing 
    The user-defined function argument is a parameter points to a function UDF_ARGS structure. There are some topic is follow here:-
  • unsigned int arg_count ( )
    This unsigned int_count( ) function is check the number of value in the initialization function if you need the function to be called with a particular number of argument.  Here are the some example of unsigned int arg_count ( ) function. 
    if (args->arg_count != 3)
    strcpy(message,"XXX() requires three arguments");
    return 1;
  • enum Item_result *arg_type 
    The enum Item_result *arg_type function is a pointer array have the types for each argument. There are some type values are possible like STRING_RESULT, INT_RESULT, REAL_RESULT and DECUMAL_RESULT. You have to confirm about the arguments are the same of given type but if not it return an error. Here are the some example of the enum Item_result *arg_type function.
    if (args->arg_type[0] != string_result ||
    args->arg_type[1] != int_result)
    strcpy(message,"XXX( ) requires a string and an integer");
    return 1;
  • UDF Return values and Error Handling
    If the error occurred then the initialization function should return 0 and else it return 1. At the situation of error, xxx_init() should store a null terminated error message in message argument. This message returns to the client. The message buffer length is equal to MYSQL_ERRMSG_SIZE characters, but you have to try to keep the message length less than 80 characters. The main function xxx() returns the function value, for long long and double functions. The string function should return a pointer to the result set *result and *length .
    memcpy(result, "result string", 13); *length = 13;

The xxx() have the result buffer and its length is 255 bytes. If your result fit in the buffer then you don?t have to allocate more memory for results.
But if your string length is more than 255 bytes, then you have to allocate the space for it by malloc() in your xxx_init() or xxx() function and free it in your xxx_deinit() function. The allocated memory can be stored in the ptr slot in the UDF_INIT structure for reuse by future xxx() calls.