Databases Reference
In-Depth Information
Even though this is an integer, it is enclosed in quotes, so it is treated as a string and
fails appropriately.
mysql> SELECT udf_intexample(2.2);
ERROR 1123 (HY000): Can't initialize function 'udf_intexample'; udf_
intexample() argument has to be an integer
As the argument has a decimal point in it, it is a decimal type, thus failing the integer
check again.
mysql> SELECT udf_intexample(2543,3452);
ERROR 1123 (HY000): Can't initialize function 'udf_intexample'; udf_
intexample() can only accept one argument
We have supplied two integers here. However, our UDF can only take one argument.
mysql> SELECT udf_intexample(2543);
+----------------------+
| udf_intexample(2543) |
+----------------------+
| 2543 |
+----------------------+
1 row in set (0.00 sec)
And there we have it! We supplied a single integer and the UDF behaved
exactly as expected!
mysql> SELECT udf_intexample(10*3-CAST(2.2 AS UNSIGNED));
+--------------------------------------------+
| udf_intexample(10*3-CAST(2.2 AS UNSIGNED)) |
+--------------------------------------------+
| 28 |
+--------------------------------------------+
1 row in set (0.00 sec)
Naturally, expressions are fine too, as long as the result type is an integer.
A simple static text output UDF
Returning text in a MySQL UDF can be slightly more complex than dealing with
numbers. MySQL gives us a memory buffer to use when returning a string but it is
only 766 bytes long. This is fine for some tasks, but not big enough to hold a long
string or a blob, so what we do is allocate our own buffer for this purpose. Either
way, we need to tell MySQL how long the resulting string is.
