Hello all.

I'm attempting to create a procedure that calls the function
character_length(), to give me the length of a string of characters
that is a variable of the function.
However, while mysql allows the function to be created, when used it
returns the error message 1305.
I'm running SuSE Linux 10.2 with MySQL 5.0.26

Here's the code of the function :
<<
drop function fct_CalculDureeRappel;
DELIMITER $$
CREATE FUNCTION fct_CalculDureeRappel (f_time TIMESTAMP, f_room_id
INT(11))
RETURNS TEXT

BEGIN
DECLARE my_appmod_time TIMESTAMP DEFAULT NULL;
DECLARE check_roomcalled_time TIMESTAMP DEFAULT NULL;
DECLARE str_rappel_time VARCHAR(20) DEFAULT '000000';
DECLARE delta_time_rappel TEXT DEFAULT NULL;
DECLARE my_place_in_str INT(3) DEFAULT NULL;

SELECT time INTO my_appmod_time FROM events_backups WHERE time <
f_time AND room_id = f_room_id AND event LIKE 'APPEL-MODULE';
SELECT time INTO check_roomcalled_time FROM events_backups where time
> my_appmod_time AND room_id = f_room_id AND event LIKE 'ROOM-CALLED';
IF f_time = check_roomcalled_time THEN
SET delta_time_rappel = f_time - my_appmod_time;
SELECT char_length (delta_time_rappel) INTO my_place_in_str;
SET my_place_in_str = 7 - my_place_in_str;
SELECT INSERT ('000000', my_place_in_str, 6, delta_time_rappel) INTO
str_rappel_time;
return str_rappel_time;
ELSE
return NULL;
END IF;
END;
$$
DELIMITER ;
>>

Here's the line used to test the function, along with the error
message :
<<
mysql> select fct_CalculDureeRappel('2007-02-09 10:01:18', 314);
ERROR 1305 (42000): FUNCTION systemplus.char_length does not exist
>>

I don't understand why it gives me this error message, since using the
function directly works fine :
<<
mysql> select character_length('ttt');
+-------------------------+
| character_length('ttt') |
+-------------------------+
| 3 |
+-------------------------+
1 row in set (0.00 sec)
>>

If someone has an idea or a clue as to why it isn't working, the help
would be much appreciated.
If someone has a better idea for formatting a duration of time, it
would also be welcome.

On 18 May, 11:59, Mad Ant <anton.har...@gmail.com> wrote:
> Hello all.
>
> I'm attempting to create a procedure that calls the function
> character_length(), to give me the length of a string of characters
> that is a variable of the function.
> However, while mysql allows the function to be created, when used it
> returns the error message 1305.
> I'm running SuSE Linux 10.2 with MySQL 5.0.26
>
> Here's the code of the function :
> <<
> drop function fct_CalculDureeRappel;
> DELIMITER $$
> CREATE FUNCTION fct_CalculDureeRappel (f_time TIMESTAMP, f_room_id
> INT(11))
> RETURNS TEXT
>
> BEGIN
> DECLARE my_appmod_time TIMESTAMP DEFAULT NULL;
> DECLARE check_roomcalled_time TIMESTAMP DEFAULT NULL;
> DECLARE str_rappel_time VARCHAR(20) DEFAULT '000000';
> DECLARE delta_time_rappel TEXT DEFAULT NULL;
> DECLARE my_place_in_str INT(3) DEFAULT NULL;
>
> SELECT time INTO my_appmod_time FROM events_backups WHERE time <
> f_time AND room_id = f_room_id AND event LIKE 'APPEL-MODULE';
> SELECT time INTO check_roomcalled_time FROM events_backups where time> my_appmod_time AND room_id = f_room_id AND event LIKE 'ROOM-CALLED';
>
> IF f_time = check_roomcalled_time THEN
> SET delta_time_rappel = f_time - my_appmod_time;
> SELECT char_length (delta_time_rappel) INTO my_place_in_str;
> SET my_place_in_str = 7 - my_place_in_str;
> SELECT INSERT ('000000', my_place_in_str, 6, delta_time_rappel) INTO
> str_rappel_time;
> return str_rappel_time;
> ELSE
> return NULL;
> END IF;
> END;
> $$
> DELIMITER ;
>
>
>
> Here's the line used to test the function, along with the error
> message :
> <<
> mysql> select fct_CalculDureeRappel('2007-02-09 10:01:18', 314);
> ERROR 1305 (42000): FUNCTION systemplus.char_length does not exist
>
>
>
> I don't understand why it gives me this error message, since using the
> function directly works fine :
> <<
> mysql> select character_length('ttt');
> +-------------------------+
> | character_length('ttt') |
> +-------------------------+
> | 3 |
> +-------------------------+
> 1 row in set (0.00 sec)
>
>
>
> If someone has an idea or a clue as to why it isn't working, the help
> would be much appreciated.
> If someone has a better idea for formatting a duration of time, it
> would also be welcome.

One thing I notice is that you are calling char_length in one and
character_length in another.
However, that sholdn't make a difference. Try putting the function
name in upper case.

I tried the following functions in upper and lower case : length,
char_length and character_length, and each time it gives the same
error message.
I really don't understand why it looks in the current database, and
not in the global functions. As far as I know, from looking at the
MySQL documentation and examples, there doesn't seem to be a need to
explicitly specify the global functions.

I found the problem.
I had the line :
SELECT char_length (delta_time_rappel) INTO my_place_in_str;
I need to change it to :
SELECT char_length(delta_time_rappel) INTO my_place_in_str;
Basically, there shouldn't be a space between the name of the function
and the opening parenthesis.