I wanted to create a procedure that can register the user with some very very basic security. I made a procedure, and it worked fine:
DELIMITER //
CREATE PROCEDURE registriraj (vime varchar(50), vpriimek varchar(50), vemail varchar(100), vgeslo varchar(50), OUT ok varchar(1))
BEGIN
IF NOT EXISTS
(SELECT * FROM uporabniki WHERE email = vemail) THEN
IF (vime != '') AND (vpriimek != '') AND (vemail != '') AND (vgeslo != '')
THEN
INSERT INTO uporabniki (ime,priimek,email,geslo) VALUES (vime,vpriimek,vemail,vgeslo);
SET ok = 1;
ELSE SET ok = 0;
END IF;
END IF;
END; //
and i guess in returned a value as output(ok varchar(1)), but when i called it
CALL vstavi('ime','priimek','iemail','geslo',@out);
and
SELECT @out;
it retuned null in both cases - true or false;
Then i thought i would make a function that returns and here it is:
DELIMITER **
CREATE FUNCTION register (vime varchar(50), vpriimek varchar(50), vemail varchar(100), vgeslo varchar(50))
RETURNS INT
BEGIN
IF NOT EXISTS (SELECT * FROM uporabniki WHERE email = vemail) THEN
IF (vime != '') AND (vpriimek != '') AND (vemail != '') AND (vgeslo != '') THEN
INSERT INTO uporabniki (ime,priimek,email,geslo) VALUES (vime,vpriimek,vemail,vgeslo);
RETURN 1;
ELSE RETURN 0;
END IF;
END IF;
END; **
But it gave me this error, which i cannot figure out.
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
I did my research and figured out, that the best way to solve this is with a sp, udf-s are more for selecting data and so on...
Also tried this: 0 46 15:10:14 SET GLOBAL log_bin_trust_function_creators = 1 Error Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation 0.031 sec but it said i need superuser permisions...
My guess is i have to use a sp.
How do i get the value from the outgoing parameter of the sp?
I am accessing to this through a c# windows app. How to i get it from the CALL of the sp?
Please help :) Much apreciated.
You have insert statement in the function which is not allowed..you cannot modify data inside a function. Function has to be deterministic thats what error says.
To relax\suppress this check, you need run following script before executing your function
SET GLOBAL log_bin_trust_function_creators = 1;