Run Java checking rules on my data in TSQL

[szroman]

Hello,

I have my data in an SQL server, I can connect to them with DB Connector successfully. In a table I have a column with field validation rules in Java script, like this: (ISNULL(INSTR.AKT_KITETT_DEV) or regexp_like(INSTR.AKT_KITETT_DEV, ‘^[[:upper:]]{3}$’)) with reference to my fieldnames.
How can I run these rules and get a value like 1 or 0 back if this is valid to my field or not?
Do I need Java snippet or how can I combine my data in SQL with this code from Java?
Thank you!

Szilvia

[takbb]

Hi @szroman,

An interesting challenge

I think I have some questions to check a couple of things before trying to discuss a solution

1. When you say an SQL server, do you mean an MS SQL Server database, or do you actually mean some other SQL database? I know in the heading you said TSQL so that does rather suggest it’s MS SQL Server, but the regexp_like got me wondering…

2. You say these rules are in JavaScript. The only place I’ve seen REGEXP_LIKE is in Oracle pl/sql and MySQL. (But the ISNULL means it’s not Oracle, but more likely to be MySQL. Apprently DB2 has both those functions too), It may just be my lack of knowledge of JavaScript but I haven’t seen them before. Are you certain they are JavaScript?

A java snippet isn’t where I’d immediately go looking for the answer, since firstly if they are JavaScript, then this isn’t the same as Java, and secondly, these scripts are accessing database tables and columns, which won’t be very useful to you from a java snippet.

3. This table containing field validation rules… is it just a table that consists of something like “Rule Name”, “Condition” and that’s pretty much it? I presume it can reference any table in the schema?

4. Are these rules processed somehow by an existing system? If so, how does it work at the moment? That might give some pointers about how it could work here.

5. What kind of data return are you expecting from “running” these conditions. At the moment they are purely conditions. There is no indication of what data you would want returned if somehow a condition is met. If you applied a condition to a table with 1000 rows, are you expecting to get back the every row where it is True, or false, or do you just want a row count, or just some sort of “Table Passed/Failed” ?

thanks

edit:
That all said, my immediate thoughts are that if the conditions were actually SQL conditions of some form (which is what they look like), then I’d write a stored procedure that accessed each condition in turn in a cursor loop, then used dynamic sql to execute a query containing the condition and write a standardised “something-or-other” result to a table.

I’d then call the stored procedure from Knime, and interrogate the result table.

I would think you could also dynamically construct sql in knime by using a combination of reading your validation rules from the database, constructing some SQL and then running DB SQL Executor node, or maybe another query node (probably in a loop) in which case that processing could be done in Knime, but it would depend on the specific answers to these questions

[szroman]

Hello @takbb
thank you for your help! I receive an excel file with more than 8300 rows with table and fieldnames (these I have in an SQL DB) and with one column of checking rules on fields and columns. This column you mentioned to be in MySql I thought in Java, but probably you are right it seems to be mysql. I need to verify my field values in my MS-SQL DB against these rules (coded in mysql).
My idea is to copy my DB to MySQL server (table by table) with Knime and from there construct the SQL commands in order to validate my values.
By the end I need the results, row by row.
What do you think?
Thank you again!
Szilvia

[takbb]

It’s possibly workable yes but might I suggest start small and prove the concept on maybe just one or two tables.

Build yourself a workflow that validates your idea first, and then when you have found the pitfalls, and established a working model, then look at bringing across the whole database.

Certainly if you have the data in MySQL then at least you have your best chance of constructing queries using those conditions without modification.

Good luck. If you need further assistance then obviously ask away, and either I or somebody else will chip in with ideas.

[szroman]

Hello @takbb
thank you for the idea of MySQL, this will be the solution.

Szilvia

[takbb]

Hi @szroman,

Here is a proof of concept workflow built on MySQL.

A couple of things come out of this.

1. Your rules table needs to have the table name held in a separate column to the rule condition, so that you can dynamically create a sql statement against the required table.

2. It will add complication to return entire rows from any table where the condition fails, if the tables have differing column structures, as you won’t be able to gather a single result set. There are possibly ways round this, such as dynamically inserting a returned row into a local (eg H2) database inside the Knime workflow, and then you’d have to query each local table individually at the end, or else you could append rows to spreadsheets named by table. Something like that.

I’d recommend using the approach I have here (at least initially to get it working) where you have a single “row_key” value in your rule table which is maybe the concatenation of rows that uniquely identify the failing rows, and maybe a “row_data” value for each condition which returns a single column (again possibly concatenation of columns as a single value) as the returned data for each “failure”. That way, you have a standard returned result set.

KNIME_execute_table_rules.knwf (56.9 KB)