MySQL Forums
Forum List  »  Stored Procedures

Re: Wrong returnvalue on userdef function
Posted by: Franz Balleis
Date: June 29, 2019 01:11AM

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

Options: ReplyQuote




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.