I am trying to write a custom propercase user defined function for mysql so i took the str_ucwords function from http://www.mysqludf.org/index.php as 开发者_Go百科an example to build my own.
my_bool str_ucwords_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
/* make sure user has provided exactly one string argument */
if (args->arg_count != 1 || args->arg_type[0] != STRING_RESULT || args->args[0] == NULL)
{
strcpy(message,"str_ucwords requires one string argument");
return 1;
}
/* str_ucwords() will not be returning null */
initid->maybe_null=0;
return 0;
}
char *str_ucwords(UDF_INIT *initid, UDF_ARGS *args,
char *result, unsigned long *res_length,
char *null_value, char *error)
{
int i;
int new_word = 0;
// copy the argument string into result
strncpy(result, args->args[0], args->lengths[0]);
*res_length = args->lengths[0];
// capitalize the first character of each word in the string
for (i = 0; i < *res_length; i++)
{
if (my_isalpha(&my_charset_latin1, result[i]))
{
if (!new_word)
{
new_word = 1;
result[i] = my_toupper(&my_charset_latin1, result[i]);
}
}
else
{
new_word = 0;
}
}
return result;
}
This works fine if I try select str_ucwords("test string");
but if I try to select a fields from a database like select str_ucwords(name) from name;
I get nothing returned.
How do I change this function so it allows me to pull data from fields on the database?
I have already tried removing args->arg_type[0] != STRING_RESULT
from the init function.
The issue isn't the type of the argument, it's that it's NULL
when str_ucwords_init
is called (since str_ucwords_init
is called before retrieving any rows). To get str_ucwords
to work with fields, you have to support NULL values by setting initid->maybe_null
to 1 in the _init
function, and setting *null_value
to 1 (and result
to NULL, though that may not be necessary) in str_ucwords
when the actual argument is null.
my_bool str_ucwords_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
unsigned long res_length;
/* make sure user has provided exactly one argument */
if (args->arg_count != 1) {
snprintf(message, MYSQL_ERRMSG_SIZE, "wrong number of arguments: str_ucwords requires one string argument, got %d arguments", args->arg_count);
return 1;
}
/* make sure user has provided a string argument */
if (args->arg_type[0] != STRING_RESULT) {
snprintf(message, MYSQL_ERRMSG_SIZE, "str_ucwords requires one string argument (expected type %d, got type %d)", STRING_RESULT, args->arg_type[0]);
return 1;
}
res_length = args->lengths[0];
if (SIZE_MAX < res_length) {
snprintf(message, MYSQL_ERRMSG_SIZE, "res_length (%lu) cannot be greater than SIZE_MAX (%zu)", res_length, (size_t) (SIZE_MAX));
return 1;
}
initid->ptr = NULL;
if (res_length > 255) {
char *tmp = (char *) malloc((size_t) res_length); /* This is a safe cast because res_length <= SIZE_MAX. */
if (tmp == NULL) {
snprintf(message, MYSQL_ERRMSG_SIZE, "malloc() failed to allocate %zu bytes of memory", (size_t) res_length);
return 1;
}
initid->ptr = tmp;
}
initid->maybe_null = 1;
initid->max_length = res_length;
return 0;
}
char *str_ucwords(UDF_INIT *initid, UDF_ARGS *args,
char *result, unsigned long *res_length,
char *null_value, char *error)
{
int i;
int new_word = 1;
if (args->args[0] == NULL) {
result = NULL;
*res_length = 0;
*null_value = 1;
} else {
if (initid->ptr != NULL) {
result = initid->ptr;
}
// copy the argument string into result
memcpy(result, args->args[0], args->lengths[0]);
*res_length = args->lengths[0];
// capitalize the first character of each word in the string
for (i = 0; i < *res_length; i++) {
if (my_isalpha(&my_charset_latin1, result[i])) {
if (new_word) {
new_word = 0;
result[i] = my_toupper(&my_charset_latin1, result[i]);
} else {
result[i] = my_tolower(&my_charset_latin1, result[i]);
}
} else {
new_word = 1;
}
}
}
return result;
}
Later releases of lib_mysqludf_str should support NULL values in the functions without alteration, which means they should also work with table columns.
精彩评论