MySQL Forums
Forum List  »  UDF

Does MySQL UDF give Different Result Across Concurrent Connections?
Posted by: Abhishek Alney
Date: October 14, 2015 10:21AM

I had a Client Requirement wherein i had to write a MySQL UDF to generate AlphaNumeric Values. The input parameters for the Function where 1.Prefix Character 2.The Length of the AlphaNumeric Value 3. The client ID.

The following is The required Table and the Function.

drop table if exists AutoIDMembers;
create table AutoIDMembers (
id bigint unsigned not null,
prefix varchar(20) not null,
orgid smallint not null,
unique key (id,prefix,orgid)
);

DELIMITER //
DROP FUNCTION IF EXISTS MembersAlphaNumeric;
CREATE FUNCTION MembersAlphaNumeric(type varchar(10),number int,oid int ) RETURNS varchar(400)
MODIFIES SQL DATA

BEGIN

DECLARE num,orid,tylen,fieldlength,un int;
DECLARE ty,val,res,msg varchar(400);

SET @ty = type;
SET @num = number;
SET @tylen = CHAR_LENGTH(@ty);
SET @fieldlength = 0;
SET @orid = oid;

IF @tylen > @fieldlength and @ty <> '' and @ty REGEXP '[[:alpha:]]' and @num > @fieldlength and @orid > @fieldlength THEN

IF @num > @tylen and @num <= 20 THEN

SELECT 1 INTO un FROM AutoIDMembers WHERE orgid=@orid AND prefix=@ty LIMIT 1;
IF un = 1 THEN

UPDATE AutoIDMembers SET id = id + 1 WHERE orgid=@orid AND prefix=@ty ;
SELECT CONCAT(prefix,LPAD(id,@num-@tylen,0)) INTO val FROM AutoIDMembers WHERE orgid=@orid AND prefix=@ty;
SET res = val;

ELSE

INSERT INTO AutoIDMembers (id,prefix,orgid) values (1,@ty,@orid);
SELECT CONCAT(prefix,LPAD(id,@num-@tylen,0)) INTO val FROM AutoIDMembers WHERE orgid=@orid AND prefix=@ty;
SET res = val;

END IF;
RETURN res;

ELSE

SET msg = 'ERROR 1: Second Parameter Value should be more than Prefix Length OR ERROR 2: Second Parameter Value should be <=20';
RETURN msg;

END IF;

ELSE

SET msg = 'ERROR 1: Parameters Value should not be equal to Zero OR ERROR 2: First Parameter should not be Empty and should have only Alphabets';
RETURN msg;

END IF;

END//
DELIMITER ;

How can i make sure where two queries are not getting same AlphaNumericID from the same function? I know that MySQL Functions does not support Start Transaction Commit; So do i need to convert it into a Stored Procedure? or What other option do i have.?...Urgent please Help

Options: ReplyQuote


Subject
Views
Written By
Posted
Does MySQL UDF give Different Result Across Concurrent Connections?
1222
October 14, 2015 10:21AM


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.