Need to use message TOPIC to do an INSERT into a database

General
1

I can do the INSERT just fine, eg

INSERT INTO EVENTS (EVENTID,OPID,DATETIME,EVENTTYPE,BRANCHID,DEPTID) VALUES (29,9,'2021-05-22 19:48:01',1,1,1);

but what I'm trying to do now is somehow make the DATETIME (for example) dynamic, so I can change it based on an upstream node.

Does anyone know how I'd use a placeholder in the TOPIC or maybe a function or some other node to insert changing values into the TOPIC?

I'm familiar with how to do all this for payloads, but not topics

thanks!

2

Well you could use a change node if you set the date to something like 'date_goes_here' when you build the topic, in the change node you could use the 'change' option to replace 'date_goes_here' with the date you want.

3

Here is an example of adding date time based on a msg variable.

[{"id":"b2dfac7d.a7a5d","type":"inject","z":"c74669a0.6a34f8","name":"","props":[{"p":"date","v":"2021-05-22 19:48:01","vt":"str"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":100,"y":3780,"wires":[["f7f4877e.01e31"]]},{"id":"f7f4877e.01e31","type":"function","z":"c74669a0.6a34f8","name":"","func":"msg.topic = `INSERT INTO EVENTS (EVENTID,OPID,DATETIME,EVENTTYPE,BRANCHID,DEPTID) VALUES (29,9,'${msg.date}',1,1,1)` ;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":300,"y":3780,"wires":[["76c24b1.5e6a534"]]},{"id":"76c24b1.5e6a534","type":"debug","z":"c74669a0.6a34f8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":520,"y":3780,"wires":[]}]
4

Brilliant, thanks, so I could just use for e.g ${msg.payload) too ? Thank you!

5

As an extension of this question, I'm trying to do two inserts in one transaction but I keep getting SQL errors no matter how I try to format it - any ideas?! Many thanks

[
    {
        "id": "c9812b6c.13eb68",
        "type": "inject",
        "z": "447a4ba3.9fb214",
        "name": "set invoice amount",
        "topic": "update TRANS set AMOUNT = '39.71' where TRANS = '99887000038'",
        "payload": "",
        "payloadType": "str",
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "x": 130,
        "y": 680,
        "wires": [
            [
                "e543c4d.e890b38"
            ]
        ]
    },
    {
        "id": "eb5d8539.ded2b8",
        "type": "debug",
        "z": "447a4ba3.9fb214",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "x": 590,
        "y": 660,
        "wires": []
    },
    {
        "id": "e543c4d.e890b38",
        "type": "firebird-db",
        "z": "447a4ba3.9fb214",
        "firebirddb": "a94792eb.18f8d",
        "requesttype": "query",
        "name": "",
        "x": 400,
        "y": 760,
        "wires": [
            [
                "eb5d8539.ded2b8"
            ]
        ]
    },
    {
        "id": "a94792eb.18f8d",
        "type": "firebird-database",
        "z": "",
        "host": "192.168.1.130",
        "port": "3040",
        "db": "C:\\data\\CHECK.FDB"
    }
]
6

Your code is showing you do an update:
update TRANS set AMOUNT = '39.71' where TRANS = '99887000038'"
not an insert.

7

Sorry I'm not using the right term. :slight_smile: But I guess the question is the same whether it's an insert or an update. At the moment I've fudged it by doing an inject which feeds into a single change node which feeds into two function nodes, but I think the function nodes ought to be combined

8

I know nothing about the firebirddb and you haven't provided the name of the contrib node you are using so it is hard to say what it will flow.

You first have to research and determine if doing multiple inserts or updates is allowed by the firebird api, then look at the node and see what it allows you to do.

If it is something you really need then you should go to the GitHub issues page for the node (lookup the node in the 'FLows' tab, then follow the GitHub link) and open an issue with the author of the node.

9

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.