DELIMITER $$

USE `screenpc`$$

DROP FUNCTION IF EXISTS `getUID`$$

CREATE DEFINER=`root`@`%` FUNCTION `getUID`(strKey VARCHAR(32), iQty INT) RETURNS BIGINT(20)
    MODIFIES SQL DATA
BEGIN
# NOTE: In order to compile, the system variable
#         log_bin_trust_function_creators=1
#
#       must be set.  Since binary logging is ON, they error out if
#       this variable is OFF.
#
  DECLARE biVal BIGINT;
  DECLARE rowCount BIGINT;
  DECLARE logMsg VARCHAR(255);
  IF iQty < 1 THEN
    RETURN 0;
  END IF;
  LOOP
    SELECT uidUidId INTO biVal FROM lclUID WHERE uidStrKey = strKey;
    #
    # If this strKey does not yet exist, create it
    IF biVal IS NULL THEN
        # Note: Don't care if this fails on concurrency.  One or the
        #       other caller will create the row.  Then the UPDATE
        #       will succeed for only one & the other will loop
        #       around and try again.
        #
      INSERT INTO lclUID(uidStrKey, uidUidID) VALUES(strKey, 1);
      SELECT 1 INTO biVal;
      SELECT CONCAT("getUID create entry for key = ", strKey) INTO logMsg;
      INSERT INTO spcLog(logMessage) VALUES(logMsg);
    END IF;
    UPDATE lclUID SET uidUidId = (uidUidId + iQty)
     WHERE uidStrKey = strKey
       AND uidUidID = biVal;
    #
    # If exactly 1 row was updated, then no other process has updated
    # this row since we issued the 'select', so we're OK.
    #
    SET rowCount = ROW_COUNT();
    IF rowCount=1 THEN
      RETURN biVal;
    END IF;
    SELECT CONCAT("getUID key = ", strKey, " biVal = ",  CAST(biVal AS CHAR), " rowCount = ", CAST(rowCount AS CHAR)) INTO logMsg;
    INSERT INTO spcLog(logMessage) VALUES(logMsg);
  END LOOP ;
END$$

DELIMITER ;