Accessing sqlite inside a function

General
1

Hi the below code is a part of my function code and I am trying to connect sqlite inside this function code. But I could not execute it. Could you help me is this right way to implement sqlite inside a function node:

const sqlite3 = require('sqlite3');

            const dbPath = context.get("C:\Users\Windows10\.node-red\DataBase\smart_database.db");
            const db = new sqlite3.Database(dbPath);

            console.log(db)

            // Check if the expected event for the current sequence position exists in the norm_table
            const expectedEventType = eventTypeOrder[slot.currentSequencePosition];
            const expectedEventQuery = `
			SELECT COUNT(*) AS eventCount
			FROM ${norm_tableName}
			WHERE Sch_PotNumber = ${PotNum}
			AND Sch__AnodeNumber = ${AnodeNum}
			AND Event_Name = '${expectedEventType}'
			`;

            // Run the query to check if the expected event exists
            db.get(expectedEventQuery, (error, result) => {
                if (error) {
                    // Handle the database query error here
                    console.error("Error checking expected event in norm_table:", error);
                } else {
                    const eventCount = result[0].eventCount;
                    if (eventCount > 0) {
                        // The expected event exists, so increment the current sequence position
                        slot.currentSequencePosition++;
                        console.log("hi")
2

You cannot use require in this fashion within node-red.

There is a sqlite node available as a plugin for node-red.

3

I am aware about plugin. But I need to use it inside a function node to run a process. sqlite need to apply in between

4

I propose you share a flow showing what you try to do - even it's not creating the intended result.
The function (fragment) you posted isn't complete and as such not a great starting point to support you.

5

I doubt that you need to run it in a function. If you explain in more detail there may be an easier solution.

6

Thank you @colin0031
I have a Node-RED function node that utilizes SQLite. This function node works based on events it receives in sequence from an upstream node. The expected event order is: event1, event2, event3, and event4.

When event1 is received, the function node stores it in an array and sends it downstream to an SQLite node table. It then waits for event2. When event2 arrives, it also gets stored in the array while waiting for event3.

The issue arises if event3 is received before event2. In this case, the function node detects the out-of-order sequence and flags a warning message.

This works fine while Node-RED is running. However, when Node-RED restarts, the past event data in memory gets cleared out but remains available in the SQLite database.

So if event3 arrives after a restart, before event2, the function node could check the SQLite database to see if event2 had already been processed before raising a warning message. This would prevent false alerts on restart since the events are persisted in SQLite.

7

I see no reason to be hand coding this in a function node over using node-red-node-sqlite

8

You could use persistent context so that the data will get automatically restored on startup.

9

Or you could create an Event 0 that you trigger on startup that looks in the db and passes on whatever data are necessary to initialise the data in the function.

10

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