Re-implement SQL output format

[alasql]

Re-implement SQL output format

[PotOfCoffee2Go]

Thanks for re-open, wasn't sure if to close or not. This would be a great enhancement! Given time I still would like to implement this enhancement, but maybe someone else can help.

The advantage of my solution is flexibility in building the INSERTS. The disadvantage is the field datatypes are unknown thus require creating strings of all data and INSERTing into a tmp table - ultimately SQL needs to update the production tables while converting data from the tmp table to the proper datatypes.

alasql would handle the datatypes properly, so the tmp table would not be required, and could directly INSERT INTO the production tables - which would be awesome!

[mathiasrw]

That sounds very reasonable - thank you again for contributing!

[PotOfCoffee2Go]

I started implementing alasql node to output INSERT INTO commands, but then it dawned on me; after using an alasql node to select the data - wire it to a standard function node with the following code. (Plus the code can be easily modified for special INSERT INTO edge cases).

// Build sql 'insert into' commands from data in payload
msg.db = msg.db || {table: 'node_red_tmp'};
msg.db.fieldnames = []; msg.db.sqlcmds=[];

function isPlainObject(input){
   return input && !Array.isArray(input) && typeof input === 'object';
}

// Create the insert commands for each object in payload
if (Array.isArray(msg.payload) && msg.payload.length && isPlainObject(msg.payload[0])) {
    msg.db.fieldnames = Object.keys(msg.payload[0]);
    msg.payload.forEach((rec) => {
        var values = msg.db.fieldnames.map(name => '"' + rec[name] + '"');
        msg.db.sqlcmds.push('INSERT INTO ' + msg.db.table + ' ' + msg.db.fieldnames.join(',') + 
            ' VALUES (' + values.join(',') + ');');
    });
}
return msg;

The results are in msg.db.sqlcmds, which then are pumped into a SQL database.

Export of node:

[{"id":"d29dc847.4a6038","type":"function","z":"1919d1c3.b05b5e","name":"Create SQL Insert Into","func":"// Build sql insert into commands from data in payload\nmsg.db = msg.db || {table: 'node_red_tmp'};\nmsg.db.fieldnames = []; msg.db.sqlcmds=[];\n\nfunction isPlainObject(input){\n   return input && !Array.isArray(input) && typeof input === 'object';\n}\n// Create the insert commands for each obj in payload\nif (Array.isArray(msg.payload) && msg.payload.length && isPlainObject(msg.payload[0])) {\n    msg.db.fieldnames = Object.keys(msg.payload[0]);\n    msg.payload.forEach((rec) => {\n        var values = msg.db.fieldnames.map(name => '\"' + rec[name] + '\"');\n        msg.db.sqlcmds.push('INSERT INTO ' + msg.db.table + ' ' + msg.db.fieldnames.join(',') + \n            ' VALUES (' + values.join(',') + ');');\n    });\n}\nreturn msg;","outputs":1,"noerr":0,"x":370,"y":420,"wires":[["2040a1d.e19675e"]]}]

[mathiasrw]

Sure, this will solve the problem.

I will reopen the issue and indicate it as a feature request so others might feel inspired to implement it.