RE: How to debug an SQL UDF

I think I know what the answer is

In the create function - I am using
NO SQL
When the procedure uses SQL

One dope slap to me


Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

From: Alan Shore
Sent: Thursday, November 01, 2018 2:19 PM
To: 'midrange-l@xxxxxxxxxxxx' <midrange-l@xxxxxxxxxxxx>
Subject: RE: How to debug an SQL UDF

Hmmm
In my procedure - I am using
exec SQL set :Nbr@ = REGEXP_COUNT(:Hld_Email_Address, '@');
to calculate the number of @ in thee-mail

In the SQL udf - this is resulting in 0 - when I can see that it should be 1

In the RPG program that uses the procedure - this same code for the same e-mail results in 1

Is there something I did wrong in the creation of the SQL udf? - please see below

Alan Shore
E-mail : ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

From: Alan Shore
Sent: Thursday, November 01, 2018 2:15 PM
To: 'midrange-l@xxxxxxxxxxxx' <midrange-l@xxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxx>>
Subject: RE: How to debug an SQL UDF

Hi everyone
Never mind - just worked it out by signing on a different session and using STRSRVJOB the STRDBG on the service program
Looks to be working - I am in debug mode

Alan Shore
E-mail : ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

From: Alan Shore
Sent: Thursday, November 01, 2018 2:11 PM
To: 'midrange-l@xxxxxxxxxxxx' <midrange-l@xxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxx>>
Subject: How to debug an SQL UDF

Hi everyone
Before I forget - we are on V7r3

I have created a procedure in a service program and tested it in my RPG programs with no problems
The service program validates e-mail data

I then created an SQL function using the following

CREATE FUNCTION NBJLIB.VALID8EMAIL (
INEMAIL CHAR(256))
RETURNS CHAR(60)
LANGUAGE RPGLE
SPECIFIC NBJLIB.VALID8EMAIL
NOT DETERMINISTIC
NO SQL
CALLED ON NULL INPUT
EXTERNAL NAME 'NBJLIB/CSTMSTSP(VALID8EMAILADDRESS)'
PARAMETER STYLE GENERAL

When I run a test using this SQL function - I am receiving responses that shows there is an error with the e-mail - when I can see that error just does not exist

Is there any way to debug the SQL function as it uses the procedure?

As always - all responses gratefully accepted
Alan Shore
E-mail : ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill