MySQL Forums
Forum List  »  Newbie

Stored procedures and functions in MySql
Posted by: Ulf Malmros
Date: September 18, 2004 05:40AM

Hi

I am pretty new at MySql DB, but has worked in Oracle since some years, and still do.
Now I have started to use MySql and rapidly got problems.
Since I always try to do the work in the database I tried to create a function in MySql that returns a users number from the database. This is a very simple function that should be no problems, but there where.

This is what i wrote in MySql:
CREATE FUNCTION getuser (vxUID VARCHAR, vxPWD VARCHAR)
BEGIN
DECLARE userno SMALLINT UNSIGNED DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLSTAT ‘42000’ SET userno = 0;

SELECT sl_login_no
INTO userno
FROM sl_login
WHERE sl_login_name = vxUID
AND sl_login_pwd = vxPWD;

RETURN userno;
END
//

This is how should wrote it in Oracle (I tried and it works)
CREATE OR REPLACE FUNCTION getuser (vxUID VARCHAR2,vxPWD VARCHAR2) IS
userno NUMBER (8);

BEGIN
SELECT sl_login_no
INTO userno
FROM sl_login
WHERE sl_login_name = vxUID
AND sl_login_pwd = vxPWD;

RETURN userno;

EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;
/

This works, but the question is why it does not work in MySql. I only get a syntax error but the syntax is correct according to documentation.

Thanks in advance

Ulf

Options: ReplyQuote


Subject
Written By
Posted
Stored procedures and functions in MySql
September 18, 2004 05:40AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.