Re: SQL UDF for Regular Expression usage in a WHERE clause

More info:

FINAL CALL Specifies that a final call is made to the function. To
differentiate between final calls and other calls, the function
receives an additional argument that specifies the type of call.

FINAL CALL is only allowed with PARAMETER STYLE DB2SQL or PARAMETER
STYLE DB2GENERAL.

The types of calls are:
First Call
Specifies the first call to the function for this reference to the
function in this SQL statement. A first call is a normal call. SQL
arguments are passed and the function is expected to return a result.

Normal Call
Specifies that SQL arguments are passed and the function is
expected to return a result.

Final Call
Specifies the last call to the function to enable the function to
free resources. A final call is not a normal call. If an error occurs,
the database manager attempts to make the final call. A final call
occurs at these times:
v End of statement: When the cursor is closed for cursor-oriented
statements, or the execution of the statement has completed.
v End of a parallel task: When the function is executed by parallel tasks.
v End of transaction: When normal end of statement processing does not
occur. For example, the logic of an application, for some reason,
bypasses closing the cursor.


Charles

On Wed, Sep 19, 2012 at 10:01 AM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

Dennis,

You stole my idea! :)

One improvement I might suggest, would be to make use of the FINAL
CALL clause on the CREATE FUNCTION and it's corresponding parameters:
- An INTEGER input parameter for the call type, if FINAL CALL was
specified on the CREATE FUNCTION statement.

This would allow you to ensure that the regex is only compiled once
and to make sure you clean up after the last call.

HTH,
Charles

On Wed, Sep 19, 2012 at 7:08 AM, Dennis <iseries@xxxxxxxxxxxx> wrote:

Folks, I just noticed that the SQL templates in my example for creating both functions, were slightly incorrect.

Both had PARAMETER STYLE GENERAL, and they both should have been PARAMETER STYLE SQL.

I apologize for this oversight. That's what happens when I copy comments from one module to a new copy.
++
Dennis
++
"The defect of equality is that we only desire it with our superiors."
-- Henry Becque




Bravely sent from my Galaxy tablet phone.
++


Dennis Lovelady <iseries@xxxxxxxxxxxx> wrote:

Hello, RPG folks.



I followed a discussion on midrange-l where someone wanted to find a
string
of digits within a table column using SQL. The discussion led me to
write a
UDF for i that emulates as much as possible the regexp_like function
available in Oracle. To that end, I have written and am now pleased to
share that via http://code.midrange.com/907466d593.html



Note that Scott Klement gets credit for the Regular Expressions header
(REGEX_H) that I got from one of his functions, eons ago.



I decided it would be good to share it on this list as well.



Simple compile hint and template for function creation are in the
source.



Using that, if you wanted to find a string of at least 6 consecutive
digits
(for example), you might do something like:



SELECT TEXT_COLUMN

FROM THAT_TABLE

WHERE REGEXP_LIKE(TEXT_COLUMN, '[0-9]{6}') = '1'







Enjoy.





Dennis Lovelady

http://www.linkedin.com/in/dennislovelady




I have never hated a man enough to give his diamonds back.
- Zsa Zsa Gabor





--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
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: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.