MQTT Bridge Subscriber Stops automatically when receiving subscribed topic

[MJozw]

Hello All,
Today i noticed a strange behaviour:
I was able to create a MQTT Bridge and to get a Subscriber to a topic running (Destination is a TQL Script which should write the data into an internal database).
The thing is, that as soon as the machbase neo sever receives the topic the subscriber is switched to STOP automatically.

The Serverlog shows the stopping but no reason for this:

This is the subscriber:

And this is the TQL File:

and the target database:

Do you have any ideas what the reason could be?

[OutOfBedlam]

i guess ...
if the TQL meets an error while handling the incomming message, the subscriber will stop. it is by the original design but we are having an internal debate not to stop it then let TQL keep to try. What do you think about this idea on your usecase?

[MJozw]

honestly this is something i already expected.
I'm completly new to the TQL scripts.

Is there a way to make debug output to the console from a TQL script?

I would expect if there is an error that stops the Subscriber i get a failure message in the server output...

[OutOfBedlam]

The easiest way to test your tql is set default value of payload() to default and execute in the browser:

CSV(strReplaceAll( payload() ?? `some;test;data;here` , ";", ","))

And,
If you leave your test csv as a comment, I can try to show you some example with it.

CSV(strReplaceAll(payload() ?? "+5.44;+3.9\n+6.44;+4.9\n", ";", ","))

MAPVALUE(2, parseFloat(value(0)))
MAPVALUE(3, parseFloat(value(1)))
MAPVALUE(0, "Temp")
MAPVALUE(1, time("now"))

CSV()

It seems your code should work...

[MJozw]

I already tested the TQL file with default date (at the moment commented out) => with this debug data everything works as expected.
At the moment i publish data via the tool "MQTT Explorer" like this:

i would expect that the payload is looking like the raw data "+5.44;+3.56" but this is propably the critical part.
So my idea would be to just write/log the payload to the console to be able to parse the data correctly

[OutOfBedlam]

your tql has no problem,
its my bad. actually there are still issue in MQTT Subscriber, I tried your data and tql with same schema and have same issue.

Let me try to fix it (again). It might be fully tested on your case by the day after tomorrow.

[OutOfBedlam]

The problem is strReplaceAll()...

Use STRING(payload() ?? "+5.44;+3.9;", separator(";"), trimspace(true)) instead.

I'll fix the strReplaceAll() to work with payload(), the current version can not handle payload() properly.

I have tested and confirmed the data is inserted when using STRING() through a bridged MQTT.

STRING(payload() ?? "+5.44;+3.9;", separator(";"), trimspace(true))

MAPVALUE(2, parseFloat(value(0)))
MAPVALUE(3, parseFloat(value(1)))
MAPVALUE(0, "Temp")
MAPVALUE(1, time("now"))

INSERT("name", "time", "value", "value_2", table("ams_0010_test"))

as a note: the issue using payload() and strReplaceAll() is MQTT message body (payload) is actually bytes array.
but the strReplaceAll() takes string for the first argument.
even though, i think It should work for users perspective.

[OutOfBedlam]

This is a more flexible and safe way, if you are not hesitant to use javascript.

// convert payload() to string and split it by new-line-character
STRING( payload() ?? "+5.44;+3.9;\n+1.2;3.4;\n", separator("\n") )

// convert each line into demanded data from
SCRIPT("js", {
    $.result = {
        columns: ["name", "time", "value", "value_2"],
        types: ["string", "datetime", "double", "double"]
    }
    // millis to nanos, now is the time when mqtt message arrived
    now = (new Date()).getTime() *1000000 
},{
    line = $.values[0];
    // console.log() will print to machbase-neo log 
    // even if this tql was invoked by MQTT subscriber
    // as your request from the next release.
    console.log(line); 
    fields = line.split(";")
    if( fields.length >= 2) {
        // add 1 usec. per line because a record that has same name and time 
        // is not inserted for preventing duplicated data.
        now += 1000;
        // yield: name, time, value, value_2
        $.yield("Temp", now, parseFloat(fields[0]), parseFloat(fields[1]))
    }
})

// insert
INSERT("name", "time", "value", "value_2", table("ams_0010_test"))

[MJozw]

Thank you very much!
with this example i was able to get it working! (in the meanwhile i changed the table structer back to default (only one value))

Although the logging when the TQL script is called from subscriber seems NOT to work.

This is how it looks like:

// convert payload() to string and split it by new-line-character
STRING( payload() ?? "+5.44;+3.9", separator(";") )

// convert each line into demanded data from
SCRIPT("js", {
    $.result = {
        columns: ["name", "time", "value"],
        types: ["string", "datetime", "double"]
    }
    // millis to nanos, now is the time when mqtt message arrived
    now = (new Date()).getTime() *1000000 
    counter = 0;
},{
    line = $.values[0];
    
    // console.log() will print to machbase-neo log 
    // even if this tql was invoked by MQTT subscriber
    // as your request from the next release.
    console.log(line); 
    console.log(counter); 
    fields = line.split(";")
    if( fields.length >= 1) {
        // add 1 usec. per line because a record that has same name and time 
        // is not inserted for preventing duplicated data.
        now += 1000;
        // yield: name, time, value, value_2
        if(counter == 0){
            $.yield("Temp Internal", now, parseFloat(fields[0]))
        }else{
            $.yield("Temp External", now, parseFloat(fields[0]))
        }
        counter++;
    }
})

// insert
INSERT("name", "time", "value", table("ams_0001"))

=> Thank you very much

Next question would be how to access the mqtt topic from tql script - this would make the the work much more easy

[OutOfBedlam]

it is true, it is not documented how to get topic inside a tql.

use param("TOPIC").
param("DUP") returns "true" if dup flag is set. otherwise "false"
param("MSGID") returns message id.

from insde of javascript. $.params.TOPIC $.params.DUP

p.s. console.log in tql will work from next release