Re: How to debug an SQL UDF

Alan,

Using REGEXP_LIKE DB2 predicate https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/db2/rbafzregexp_like.htm and the General Email Regex (RFC 5322 Official Standard) shown here https://emailregex.com/ as the pattern expression string, it was easy to create a pretty robust UDF scalar function to test email address validity.
REGEXP_LIKE - ibm.com<https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/db2/rbafzregexp_like.htm>
source-string An expression that specifies the string in which the search is to take place. The expression must return a value that is a built-in character string, graphic string, numeric, or datetime data type.
www.ibm.com



Nick Litten wrote about the same thing using RPG procedure.

I can post the pure SQL version if anyone wants.

Jim
________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> on behalf of Alan Shore <ashore@xxxxxxxx>
Sent: Thursday, November 1, 2018 2:20 PM
To: midrange-l@xxxxxxxxxxxx
Subject: 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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com