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'));

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)

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


Now the same Query via function.

select mand22.getNextSysNumber('sg_sys_objektdaten');

RESULT: no result, but

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

i go crazy
Thank you for your support


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.