AlaSQL working with msg.payload

Hello there,

did anyone here already try to inject a msg.payload into an AlaSQL node ?

Excerpt from the documentation:

Refer to input data in msg.payload with $0 in your SQL.
If msg.payload is an array the first value will be $0, the second $1, and so forth.

I tried building a SELECT query like:

SELECT event FROM MyTable
WHERE ts = $0

And injected a string 2020.06.25 10:05:30.632 as msg.payload and get an empty array as return from my database. But running the query directly gives me the correct result:

SELECT event FROM MyTable
WHERE ts = "2020.06.25 10:05:30.632"

I also tried node-red-contrib-alasqlfunc. But already the README examples result in syntax errors:

msg.query='select * from abc where Id="'+msg.payload+'"';

I haven't used this node before, but reading that documentation snippet very strict, have you tried injecting an array instead, where the first (and only) item in the array is 2020.06.25 10:05:30.632 as string? Hmmm reading again very closely, that shouldn't make a difference...

Firstly, I assume you realise this doesnt actually talk to a database?

alasql node lets you access javascript objects as if they were a SQL database

Secondly, as your payload is a string, I suspect you need to use single quotes in the querys' where clause

Thirdly, my guess is that msg.payload is the DATABASE (not the where clause)

e.g. As a test - try sending this (in a function node) --> to the alaSQL node then to a debug node...

msg.payload = [ {id:0,t:"hello"} , {id:1,t:"bye"} ];
msg.query = "SELECT id,t FROM ? WHERE t = 'bye'; ";
return msg;

DISCLAIMER
I have never used this contrib node - but a quick look at the source code suggests...

  • the payload is the object to query
  • you can send dynamic alaSQL queries into the node via msg.query or msg.topic (if the field is left blank)
  • there also looks to be a file mode - not sure how that works!

I too just read some code and the issues and I'm spotting a few things. There's 2 open discussions on whether errors in the supplied information/errors in the underlying Ala system should crash Node-RED, or if they should handle those exceptions. The fact that there's even a second discussion needed and not an immediate "hey we handle errors in the node and inform the user rather than taking down Node-RED" does not give me much hope for the rest tbh.


There's also an issue on a merged PR that deals with msg.payload, msg.topic and msg.query, and what you describe above:

A further dive down the code and rest shows that msg.payload is indeed, as @Steve-Mcl says, meant to use as data source. It serves as the database that is being queried. The alasql file-in node that is included can be used to read from, for example, and XLSX file, that will be used as datasource instead. The alasql file-out node allows the result to be written back to a file.

This is done through the underlying alasql library, where this node is a wrapper for.


The examples shown here are for the raw alasql code, where the first argument passed to the alasql function is the SQL query, and the second(/third/fourth/...) argument(s) are the contents of msg.payload, which is the data to operate on.

Hello Steve and afelix,

and thank you for your reply!

I am used to working with this node - but so far only with static queries. I have a rough idea what AlaSQL is in the background. Setting this up as a static query does work:

SELECT event FROM MyDB
WHERE ts = "2020.06.25 10:05:30.632"

I also created this "database" MyDB:

CREATE TABLE MyDB (ts TIMESTAMP VARCHAR(80), event VARCHAR(255));

And I have Node-RED set up to feed data into it - so the select/where query above does retrieve an event for me.

Injecting the message as an array and using msg.topic or msg.query (instead of message.payload) was a good tip. I come to the same conclusion after re-reading the README. But I cannot get it to work:

object
_msgid: "255e763c.6ce1ba"
topic: array[1]
0: "2020.06.25 10:05:30.632"
payload: array[1]
0: "2020.06.25 10:05:30.632"
query: array[1]
0: "2020.06.25 10:05:30.632"

But the node does not use the message - this still gives me an empty array in return:

SELECT event FROM MyDB
WHERE ts = $0

This part I don't understand:

msg.payload = [ {id:0,t:"hello"} , {id:1,t:"bye"} ];
msg.query = "SELECT id,t FROM ? WHERE t = 'bye'; ";
return msg;

You do not have to provide your data inside the query. AlaSQL behaves like an SQL database. Node-RED fills it with data, and I can query against this "database" - just like SQLite. Of course, everything is only in memory - there is nothing persisted. In the end there is no database.

But currently, I am stuck - I think I will try solving this with SQLite instead. Maybe, dynamic queries are just broken at the moment.

I do have a working example here:

https://mpolinowski.github.io/node-red-sql-logging-datastreams

I have been using variations of this in Node-RED for a while to solve all kinds of issues. But only with static queries.

topic MUST only be a string. (e.g. "SELECT * FROM...")


The data to query goes into msg.payload (e.g. the database)

msg.topic = ["2020.06.25 10:05:30.632"];
msg.payload = "SELECT event FROM MyDB WHERE ts = $0";
return msg;

If I inject this into the AlaSQL node, then I would have to leave the node empty ? (this does not work - I just tried)

Currently, I am using a function node to set the payload:

And I have the SQL query inside the AlaSQL node:

That is literally nothing like we said.

1st alaSQL queries against arrays of objects e.g.

msg.payload = [ {id:0,t:"hello"} , {id:1,t:"bye"} ];

2nd the topic must be a string NOT an array e.g...

msg.topic = "SELECT id,t FROM ? WHERE t = 'bye'; ";
//alternatively - msg.query = "SELECT id,t FROM ? WHERE t = 'bye'; ";

So for your example...

msg.topic = "SELECT event FROM ? WHERE ts = '2020.06.25 10:05:30.632' ";  // A STRING!!!!
msg.payload = MyDB; // << you need to set this to the thing to query

EDIT...
Also, for msg.topic or msg.query to work, you must leave the "SQL Query" field blank (as I said in first post).

EDIT2...
To make it dynamic:

  • feed the WHERE value into a function with the payload set as your comparitor
  • setup the topic and payload as required
  • send it to alaSQL node (wich must not have a query set in its UI field)

function node before alasql

msg.topic = "SELECT event FROM ? WHERE ts = '" + msg.payload + "'"; 
msg.payload = MyDB; // << you need to set this to the thing to query - wherever that comes from

I think you cannot do that. If you leave the AlaSQL node empty you get a TypeError. Like I do know that adding the following into the node works:

SELECT event FROM MyDB
WHERE ts = "2020.06.25 10:05:30.632"

But if I inject this msg.topic = "SELECT event FROM MyDB WHERE ts = '2020.06.25 10:05:30.632' "; into an empty SQL node, it throws an error:

"TypeError: Cannot read property 'length' of undefined"

What you are describing is the SQLite node - this one receives everything via msg.topic.

Hmm let me try solving this with SQLite. I believe that this is going to work there.

@mpxd Mike, back to basics for a moment. What is your data source, what kind of data are you attempting to run this query over, and where is that data located? These nodes expect that your data, in the shape of an array of objects, is located in msg.payload that goes into the alasql nodes. Is that the case for your data?

1 Like

The data source are base64 encoded images from an surveillance camera. they are stored inside the database with an added timestamp.

I do have an dashboard widget that I am using as an event timeline. It returns the timestamp of an event when you click on it. I now want to find this timestamp and display the corresponding image.

All of this already works, as long as I use static queries. The problem is using the timestamp I receive from the timeline widget instead. So all I have is an AlaSQL node with the SQL Query and a node that outputs a timestamp as an object:

Untitled

And where is this located inside Node-RED? I see in your screenshot an array of objects. Is this fed to the alasql node in msg.payload?

As an alternative approach, have you looked at JSONata in a change node to solve this? It might be worth migrating the string timestamp in your database back to seconds since epoch, and go through your array of objects with jsonata.

So, it seems the version on github is not the same as the version on NPM/flows

See my comment on issues...

I am using an HTTP GET to get the snapshot, do a base64 encode and then insert this data + timestamp into AlaSQL (all in Node-RED). The screenshot is the output when I query this data.

I already looked into the Moment.js node for that - I also have an issue that the timestamp inside the database and the timestamp from the widget are not 100% identical. But I postponed trying to think about that... one step at the time :grinning:

Ok, there is the problem... Thank you!

Hey,

I just wanted to give feedback.

After the AlaSQL update from today everything is working as expected!

I am using a function node to set the msg.payload - important this has to be an array:

msg.payload = ["2020.06.26 12:46:25.219"];
return msg;

And use the AlaSQL node for the SQL query:

SELECT event FROM MyDB
WHERE ts = $0

And I am getting the object returned from my database - the event that belonged to that timestamp.

Thank you again, both of you for your help :slightly_smiling_face:

@mpxd @afelix

The Dev has fixed the issue I raised and V2.0.1 is now in the flows lib

You can now pass the a dynamic query via msg.query or msg.topic (but remember to leave the query field on the UI blank for it to work)

EDIT...
Sorry @mpxd just noticed you mentioned that it had been updated - oops :slight_smile:

1 Like

It's good to see that it's working, but I don't understand how based on what you show. That's quite interesting :slight_smile: Great to see that you succeeded with your flow though!

I wanted to upload the result - once I manage to finish it :slightly_smiling_face:

I will give you a ping

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