SQLI to perform regex matching in AE scripting

[Michael A. Lowry]

Because the STR_MATCH AE scripting function doesn't support regular expressions, I decided to come up with a solution. Building on my earlier idea of using Oracle SQL to compare timestamps, I developed a new SEC_SQLI VARA to perform regex matching.

SELECT CASE
WHEN REGEXP_LIKE (?, ?, 'i')
THEN 1 ELSE 0 END AS MATCHES
FROM DUAL

In the SEC_SQLI VARA object, the first bind parameter is the string to compare (&STRING#), and the second is the regular expression (&REGEX#).

For example, one could use the SQLI to validate AE object names, by matching with the regular expression ^[A-Z0-9\._\-]{1,200}$.

:SET &STRING# = "EBM.MYJOB.JOBS_UNIX"
:SET &REGEX#   = "^[A-Z0-9\._\-]{1,200}$"
:SET &STRING_MATCHES# = GET_VAR(EBM.REGEX_MATCH.VARA_SEC_SQLI)
:IF &STRING_MATCHES# = 1
:  PRINT "Object name is valid."
:ELSE
:  PRINT "Object name is NOT valid."
:ENDIF

This returns 1 in one of AE system, but 0 in another. 1 is the expected/correct value. Is there a straightforward way to find out why the SEC_SQLI is returning incorrect results in one AE system?

[Joel Wiesmann]

Hi Michael

Are both AE systems running with the same DB backend? The regexp implementation often differs.

The same could be done using an EXEC VARA and having the regexp-check done on an OS agent using python/perl. Could get tricky to pass the special characters down to the script.

P.S. what about a regexp webservice ;-)? This could also feedback capturing groups.

------------------------------
☎️ Swisscom Automation Engineer & 🧙 PE Membership Creator

Automic Kurse, Tutorials, Tools und mehr auf:
https://membership.philippelmer.com/
Zwei Wochen kostenlos testen!
------------------------------

[Michael A. Lowry]

Hey @Joel Wiesmann. Good suggestions.

The same could be done using an EXEC VARA and having the regexp-check done on an OS agent using python/perl

Yeah, even using an EXEC VARA wrapper around an SQL job would help with troubleshooting. (The SQL query works the same in both systems when run in a JOBS object.) One reason I'm going with SEC_SQLI is that it doesn't spawn additional AE tasks.

Could get tricky to pass the special characters down to the script.

Indeed. I suspect one of the characters in the regex is getting garbled by the Oracle client when replacing the bind parameters. The versions of the Oracle DB, Oracle client, SQL agent, and AE are identical between the two systems though, so I'm at a loss to explain the difference in behavior.

[W]hat about a regexp webservice ;-)? This could also feedback capturing groups.

I have in mind a more general purpose regex JOBI that can be used not just for comparisons, but for returning capture groups in a data sequence. For now though, a simple comparison that works reliably would be sufficient.

​