Taking a Dataset and Inserting it to a SQL temp table

Ignition
4

If you were using a competent database (the following is PostgreSQL), you can pass java arrays as arguments, and unnest() them in a FROM clause. For an example, I created a table in my psql forum playground called phrases, to be used in a join. When queried by itself, you get this dataset:

"#NAMES"
"prefix","suffix"
"#TYPES"
"str","I"
"#ROWS","4"
"Hello","1"
"Good Bye","2"
"Welcome to","3"
"Whoa!","4"

To go with that, in a custom property named lookups, I created the following unbound dataset:

"#NAMES"
"id","name"
"#TYPES"
"I","str"
"#ROWS","4"
"1","World!"
"2","Cruel World."
"3","Happy Valley!"
"4","Uncanny Valley?"

In a simple button's actionPerformed event, I placed this code:

import jarray
from java.lang import String, Integer
root = event.source.parent
sql = """
SELECT concat(phrases.prefix, ' ', suffixes.suffix) As phrase
FROM phrases INNER JOIN
  unnest(?::int[], ?::varchar[]) as suffixes(key, suffix)
  ON phrases.suffix = suffixes.key
"""
lists = [root.lookups.getColumnAsList(i) for i in range(root.lookups.columnCount)]
params = [jarray.array(lists[0], Integer), jarray.array(lists[1], String)]
ds = system.db.runPrepQuery(sql, params)
root.joined = ds.underlyingDataset

When executed, the joined custom property received this result dataset:

"#NAMES"
"phrase"
"#TYPES"
"str"
"#ROWS","4"
"Hello World!"
"Good Bye Cruel World."
"Welcome to Happy Valley!"
"Whoa! Uncanny Valley?"

I recommend you migrate to a database technology that has unnest() functionality in some form. (I can't find any way to do this with MS SQL Server.)

1 Like