Taking a Dataset and Inserting it to a SQL temp table

Ignition
1

I have run into a bit of a strange issue regarding a SQL server project. We have a process that is in the in the middle of a database migration. One database is now on one server, the other is still on the old server. Originally, we had the two databases linked while on the same server as to make the union of the data we needed possible for the jdbc driver to commit in one query.

With this new setup, there are security concerns preventing us from connecting the databases between servers. With my options somewhat limited, my next thought was to get a dataset from one database on one server, turnaround, and insert that dataset as a temp table into the other server so we can union the data as we were originally doing.

This is not inherently easy to do. I wanted to throw a line out and see if anyone has attempted something like this they would be willing to share. I dug through Exchange but couldn't find anything like it. Thanks for any help.

2

If all you're wanting to do is UNION the data, then why do you need to put it into the database at all? Does the data need to be kept in the database on the new server permanently?

Seems like query DB on old server, query data on new server, use system.dataset.appendDataset() to perform a union.

Otherwise, if you can use a third party application, then @pturmel's free Integration Tool Kit Module has many tools to do this type of thing in an expression rather than script, including UNION and JOIN

1 Like
3

Union was a poor choice of words. We are doing a lot more than just appending two sets of data. It would be a big challenge to grab the two sets of data and do the transforms post processing.

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.)

2 Likes
5

This is really cool and if it were a personal project I would follow your advice. I can safely say though that MS SQL Server is not going anywhere in my case. I am getting a solution figured out. Its not my favorite thing in the world as its just SQL string building, but that's the best option available to me.

6

My condolences.