Re: Wrong returnvalue on userdef function

[Franz Balleis]

Hi Peter,
yes this OK is very strange, and I can not tell where this damn OK is coming from. The table has this deffinition and is filled with the current index key of the ERP system.

_____________________________________________________________________________
CREATE TABLE `synewidvalues` (
`GroupName` varchar(64) COLLATE latin1_german1_ci NOT NULL,
`LastNumber` int(18) DEFAULT NULL,
PRIMARY KEY (`GroupName`),
UNIQUE KEY `synewidvalues1` (`GroupName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci
______________________________________________________________________________

When I start a query via SQL I get the following result

SELECT synewnumber.LastNumber FROM synewnumber
WHERE UPPER(TRIM(synewnumber.TableName)) = UPPER(TRIM('sg_sys_objektdaten'));

RESULT:
742
OUTPUT:
09:00:11 SELECT synewnumber.LastNumber FROM synewnumber WHERE UPPER(TRIM(synewnumber.TableName)) = UPPER(TRIM('sg_sys_objektdaten')) LIMIT 0, 1000 1 row(s) returned 0.000 sec / 0.000 sec

exactly what I'm expecting..
_____________________________________________________________________________
Here is the Code of this function

CREATE DEFINER=`mysql`@`%` FUNCTION `getNextSysNumber`( c_number_type varchar(64)) RETURNS int(18)
BEGIN

DECLARE nextnumber INTEGER DEFAULT 0;
SELECT synewnumber.LastNumber INTO nextnumber FROM synewnumber
WHERE UPPER(TRIM(synewnumber.TableName)) = UPPER(TRIM(c_number_type));
SET nextnumber = nextnumber + 1;
RETURN ( nextnumber );

END

_____________________________________________________________________________
Now the same Query via function.

select mand22.getNextSysNumber('sg_sys_objektdaten');


RESULT: no result, but
OUTPUT:

09:04:48 select mand22.getNextSysNumber('sg_sys_objektdaten') LIMIT 0, 1000 OK 0.063 sec


i go crazy
Thank you for your support

Franz


PS: all this operations are done with the MYSL Workbench 8.0