How to create a transition table in javascript?

[vdurante]

First of all, you are doing god's work. Me and my team use your blog on a daily basis, since adobe's documentation is lacking.

I was wondering if you could write a blog post about a subject...

I am trying to create and populate a transition table from a javascript code block. Nowadays I do this in a very hacky way: I query a random table with customs schema then I use a javascript block truncate the table and insert new data to it.

Unfortunately, I am working with a workflow which I can't use the method above. Do you know if it is possible to create a table from a javascript block?

I tried the following, but of course it didnt work, specially since vars.tablename` is undefined since there is no inbound population

sqlExec("ALTER TABLE " + vars.tableName + " ADD COLUMN sTest nvarchar(500)");
sqlExec("INSERT INTO "+ vars.tableName + "(sTest) VALUES ('foobar');");

Thanks in advance.

[floriancgit]

Dear Vitore,

Thanks for the blog comment ;)

Now for your request, please consider both versions:

1. Using the native vars.tableName from an empty Query

Query details:

Enrich details:

JS details

logInfo('vars.tableName: '+vars.tableName);

var url = 'https://jsonplaceholder.typicode.com/users';
var req = new HttpClientRequest(url);
req.execute();
var resp = req.response;
var users = JSON.parse(resp.body);
for each(var user in users){
  logInfo(user.name); // "Leanne Graham"
  var sql = 'INSERT INTO '+vars.tableName+' (sName) values ($(sz))';
  sqlExec(sql, user.name);
}

Display target:

SQL table:

SQL Logs:

11/09/2021 8:10:53 PM (query)
-- Log: Creating table 'wkf28782_51_1'  SELECT DropTableIfExists('wkf28782_51_1'); CREATE UNLOGGED TABLE wkf28782_51_1(   iId INTEGER Default 0 ) WITH (autovacuum_enabled=FALSE, toast.autovacuum_enabled=FALSE);

11/09/2021 8:10:53 PM (query)
INSERT INTO wkf28782_51_1 (iId) SELECT  DISTINCT  F0.iFolderId FROM XtkFolder F0 WHERE (F0.iFolderId = 0) AND ((F0.iFolderId > 0 OR F0.iFolderId < 0))

11/09/2021 8:10:53 PM (enrich)
-- Log: Creating table 'wkf28782_52_1'  SELECT DropTableIfExists('wkf28782_52_1'); CREATE UNLOGGED TABLE wkf28782_52_1(   iId INTEGER Default 0,    sName VARCHAR(255) ) WITH (autovacuum_enabled=FALSE, toast.autovacuum_enabled=FALSE);

11/09/2021 8:10:53 PM (enrich)
INSERT INTO wkf28782_52_1 (iId,sName) SELECT   W0.iId, NULL FROM wkf28782_51_1 W0 JOIN XtkFolder F1 ON (F1.iFolderId = W0.iId) WHERE ((F1.iFolderId > 0 OR F1.iFolderId < 0))

[floriancgit]

2. Now using custom version without Query

Workflow:

Create table:

vars.tableName = 'wkf28782_xxx_1';
var sql = 'SELECT DropTableIfExists(\'wkf28782_xxx_1\'); CREATE UNLOGGED TABLE wkf28782_xxx_1(   iId INTEGER Default 0 ) WITH (autovacuum_enabled=FALSE, toast.autovacuum_enabled=FALSE);';
sqlExec(sql);

Add columns:

var sql = 'ALTER TABLE '+vars.tableName+' ADD COLUMN sName VARCHAR(255)';
sqlExec(sql);

Fetch users JS is the same as version 1.

Display target will fail:

SQL Logs are empty.

But SQL table was created and is working:

Please let me know if that helps!

Also, you may consider sending an overview of your workflow for in depth analysis

Best regards
Florian