dynamic query from external file

[Anonymous]

Dear Experts,

Please suggestions needed.

I have a requirements say, need to create sql query for each columns which is defined in .txt file.

my .txt file will look like below:

tbl_name1=tbl1

cl1=name

cl2=zipcode

note: columns is not static may be tomorrow new column can be added. 

now, what i want to do is:

read the key, value from the file, and store in list may be list..or etc 

get the first column. in this case name

and, create a sql query ( select name from tbl1)

next, get the next column, in this case zipcode and create query.......like that need make it dynamic...and if future new columns comes we dont want to edit/change the code, it should form a query.

[PhilHibbs]

I don't think the flow link column structure can be dynamic. It has to be compiled into the Java.

What you could do is construct a query that returns an array of values (if your database has an array data type) in a single column. Or, a key field followed by an array of values that can change based on the config file.

[Anonymous]

is it possible?

let say i have something in my text file [c1,c2,c3]

convert it as array list

count number of items in array list

and get the first item from the array list -> create a sql query "select AL[0] from tbl1" > pass to tJavaRow and execute

and get the next item from the array list -> create a sql query "select AL[1] from tbl1" > pass to tJavaRow and execute

this process do untill the array list end.

[PhilHibbs]

I would do it by constructing a SQL statement that retrieves an array or structure.

If you have this in your file:

c1,c2,c3

then for example the query might look like this:

"SELECT ("+row1.columnlist+") FROM db.tablename"

In HiveQL, for example, that will return a JSON structure like this:

{"col1":"0300-819231","col2":"ababc@abc.com","col3":null}

You could then unpack that JSON using a tExtractJSONFields component.

Other databases might vary in their behaviour.

Alternatively, this query would return an array:

"SELECT ARRAY("+row1.columnlist+") FROM db.tablename"

That would return an array of values rather than a JSON object.

[PhilHibbs]

Defending against injection attacks is also a consideration, if you don't absolutely trust the file containing the column list.

https://xkcd.com/327/