why i can`t use rand() i functions within ?

like that:

create function fn_rand() returns float

begin

return rand()

end



Msg 443, Level 16, State 1, Procedure fn_rand, Line 3

Invalid use of side-effecting or time-dependent operator in 'rand' within a
function.



what is a problem ?



thanks for help

Re: why i can`t use rand() i functions within ?

the syntax is wrong. the function must be compiled in a shared library
first an declared with SONAME.
27.2.2. CREATE FUNCTION/DROP FUNCTION Syntax
CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|INTEGER|REAL}
SONAME shared_library_name

DROP FUNCTION function_name
maybe this has changed in version 5. I am using 4.1."rokshill"
wrote in message
news:dn9oa5$nrb$1@news.dialog.net.pl...
> like that:
>
> create function fn_rand() returns float
>
> begin
>
> return rand()
>
> end
>
>
>
> Msg 443, Level 16, State 1, Procedure fn_rand, Line 3
>
> Invalid use of side-effecting or time-dependent operator in 'rand' within
> a function.
>
>
>
> what is a problem ?
>
>
>
> thanks for help
>
>

Re: why i can`t use rand() i functions within ?

Jim Michaels wrote:
> the syntax is wrong. the function must be compiled in a shared library
> first an declared with SONAME.

>> create function fn_rand() returns float
>>
>> begin
>>
>> return rand()
>>
>> end
>>


-- for just in case it's off
SET GLOBAL log_bin_trust_function_creators =1;

-- the function
delimiter //
CREATE FUNCTION fn_rand() RETURNS DECIMAL
BEGIN
RETURN RAND();
END;
//
delimiter ;


//Aho

Re: why i can`t use rand() i functions within ?

I am on MySQL 4.1:
SET GLOBAL log_bin_trust_function_creators =1;
Unknown system variable 'log_bin_trust_function_creators'
-- the function
delimiter //
CREATE FUNCTION fn_rand() RETURNS DECIMAL
BEGIN
RETURN RAND();
END;
//
delimiter ;

so I took off the first line.

You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'delimiter //
CREATE FUNCTION fn_rand() RETURNS DECIMAL
BEGIN
RETURN RAND();
' at line 2

so I took off the dlimiters.

You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '() RETURNS
DECIMAL
BEGIN
RETURN RAND();
END' at line 1

MySQL 4.1 only allows functions in shared library files. I know what it's
going to expect next. SONAME and a shared library filename. maybe it's time
I upgrade to mysql 5. is it production yet? [just checked] yup. upgrade
complete.

"J.O. Aho" wrote in message
news:42h3o3F1ietvjU1@individual.net...
> Jim Michaels wrote:
>> the syntax is wrong. the function must be compiled in a shared library
>> first an declared with SONAME.
>
>>> create function fn_rand() returns float
>>>
>>> begin
>>>
>>> return rand()
>>>
>>> end
>>>
>
>
> -- for just in case it's off
> SET GLOBAL log_bin_trust_function_creators =1;
>
> -- the function
> delimiter //
> CREATE FUNCTION fn_rand() RETURNS DECIMAL
> BEGIN
> RETURN RAND();
> END;
> //
> delimiter ;
>
>
> //Aho