'select * from file', using a message for each row, returns correct number of messages - all with last row data

colin-grierson commented 1 year ago

When testing this function I set up a simple 'select * from lib.file' query, using the default option of one message for each row. The correct number of records were returned, but the contents of all the messages were the same - containing the data from the last row in the table. Enabling 'Single Array Result Mode' works as expected, returning one message containing an array. The data in this is correct. I tried a different table and had the same results Changing the sort order confirms it is the last row data that is returned

node-red-contrib-db2-for-i should up to date as I installed it yesterday. Similarly idb-connector. The database is on the local system The table contains 49 rows and is totally unremarkable as far as I know The IBMi is running V7R3M0 Node-RED version: v3.1.0 Node.js version: v18.17.1

Please let me know if you require more information Thanks, Colin Grierson

AndyYouens commented 1 year ago

If you run the same sql statement from run sql scripts in ACS do you get the same results as above?

colin-grierson commented 1 year ago

HI Andy

Thanks for looking at this. If I run the query in ACS I get 49 rows, all different. If I run the query in node-red, with the option to return all rows on one message selected, I get 49 rows, all different, same data as with ACS. If I run the query in node red, using the option to return one row in each message, I get 49 messages - all the same, all with the data from the last row the query retrieved.

Regards Colin

Get Outlook for Androidhttps://aka.ms/AAb9ysg

We develop, integrate and manage mission critical systems

From: Andy Youens @.> Sent: Thursday, September 28, 2023 8:46:12 PM To: IBM/node-red-contrib-db2-for-i @.> Cc: Colin Grierson @.>; Author @.> Subject: Re: [IBM/node-red-contrib-db2-for-i] 'select * from file', using a message for each row, returns correct number of messages - all with last row data (Issue #15)

If you run the same sql statement from run sql scripts in ACS do you get the same results as above?

— Reply to this email directly, view it on GitHubhttps://github.com/IBM/node-red-contrib-db2-for-i/issues/15#issuecomment-1738645660, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AJ3QPHJC6E5I5NTRYD7EDU3X4UTMJANCNFSM6AAAAAA5KGDPS4. You are receiving this because you authored the thread.Message ID: @.***>

AndyYouens commented 1 year ago

Hi Colin

I can confirm I get the same error as you did, running a SQL over the qiws.qcustcdt table.

I always use the array option so I hadnt noticed this :-)

the following inject fires off 2 db2 queries one returning all results in an array - works fine the second inject returns just the last row - not what should happen.

I'm also on 7.3 of the OS

I'll dig deeper, I'll write a nodejs program see if we get the same results.

[ { "id": "3af1e8090840e4b1", "type": "tab", "label": "Flow 1", "disabled": false, "info": "", "env": [] }, { "id": "0c55103467d384ff", "type": "inject", "z": "3af1e8090840e4b1", "name": "", "props": [ { "p": "payload" }, { "p": "topic", "vt": "str" } ], "repeat": "", "crontab": "", "once": false, "onceDelay": 0.1, "topic": "", "payload": "select from qiws.qcustcdt", "payloadType": "str", "x": 350, "y": 380, "wires": [ [ "9b714bcfe73e9c38", "3215177419507a14" ] ] }, { "id": "9b714bcfe73e9c38", "type": "DB2 for i", "z": "3af1e8090840e4b1", "mydb": "e39dd96753e2d8ee", "name": "", "arraymode": true, "x": 590, "y": 300, "wires": [ [ "7ee1e16f4871818b" ] ] }, { "id": "7ee1e16f4871818b", "type": "debug", "z": "3af1e8090840e4b1", "name": "debug 66", "active": false, "tosidebar": true, "console": false, "tostatus": false, "complete": "false", "statusVal": "", "statusType": "auto", "x": 810, "y": 300, "wires": [] }, { "id": "0252486550ca3c7f", "type": "debug", "z": "3af1e8090840e4b1", "name": "debug 67", "active": true, "tosidebar": true, "console": false, "tostatus": false, "complete": "false", "statusVal": "", "statusType": "auto", "x": 820, "y": 440, "wires": [] }, { "id": "3215177419507a14", "type": "DB2 for i", "z": "3af1e8090840e4b1", "mydb": "e39dd96753e2d8ee", "name": "", "arraymode": false, "x": 600, "y": 440, "wires": [ [ "0252486550ca3c7f" ] ] }, { "id": "e39dd96753e2d8ee", "type": "DB2 for i Config", "cnnname": "Galatea", "db": "LOCAL", "keepalive": true } ]

bmarolleau commented 1 year ago

Hi @colin-grierson ! Thanks for pointing out this issue, and @AndyYouens for your help.
I could also reproduce the bug, that seems to be a regression to be fixed. I'll let you know when solved. In the meantime, the obvious workaround is to use the Array mode ^^ Thanks.

AndyYouens commented 1 year ago

@colin-grierson If you need an example of processing the array after it returns from the adapter, please let me know.

@bmarolleau If I can help with testing, please let me know.

bmarolleau commented 1 year ago

Hi @colin-grierson , @AndyYouens

I solved the issue and pushed a latest version of the node (0.2.4) but not on npm yet, so it can only be installed manually with a git clone and npm install as specified here: Testing a node module locally I think this bug using multi-result mode that returns always the last record is due to the fact that the node.send() function is asynchronous and we work on the same message object (hence the side effet) . I managed to get the desired result by cloning the msg JSON object and work on a copy when invoking node.send() .Any better idea is welcome, but that works. Please refer to this original discussion https://github.com/node-red/node-red/issues/1214 So instead of a simple node.send(msg);,

var newMessage = RED.util.cloneMessage(msg);
newMessage.payload=row;
node.send(newMessage);

Please test with a local install (instructions above) , let me know, and I'll update the npm package when I can. Note that we should consider to only use the "Array Mode" (single JSON Array output) and only use this multi-row mode when necessary, as the Array mode is more performant, and you can always split the output array if necessary. Thanks.

colin-grierson commented 1 year ago

Hi Andy

I’m very new at this so I tried the most simple case with all the defaults. All the examples I’ve seen since use the array option. Great that you can replicate the problem – makes it much easier.

Thanks again, Colin

We develop, integrate and manage mission critical systems

From: Andy Youens @.> Sent: Thursday, September 28, 2023 10:47 PM To: IBM/node-red-contrib-db2-for-i @.> Cc: Colin Grierson @.>; Author @.> Subject: Re: [IBM/node-red-contrib-db2-for-i] 'select * from file', using a message for each row, returns correct number of messages - all with last row data (Issue #15)

Hi Colin

I can confirm I get the same error as you did, running a SQL over the qiws.qcustcdt table.

I always use the array option so I hadnt noticed this :-)

the following inject fires off 2 db2 queries one returning all results in an array - works fine the second inject returns just the last row - not what should happen.

I'm also on 7.3 of the OS

I'll dig deeper, I'll write a nodejs program see if we get the same results.

[ { "id": "3af1e8090840e4b1", "type": "tab", "label": "Flow 1", "disabled": false, "info": "", "env": [] }, { "id": "0c55103467d384ff", "type": "inject", "z": "3af1e8090840e4b1", "name": "", "props": [ { "p": "payload" }, { "p": "topic", "vt": "str" } ], "repeat": "", "crontab": "", "once": false, "onceDelay": 0.1, "topic": "", "payload": "select from qiws.qcustcdt", "payloadType": "str", "x": 350, "y": 380, "wires": [ [ "9b714bcfe73e9c38", "3215177419507a14" ] ] }, { "id": "9b714bcfe73e9c38", "type": "DB2 for i", "z": "3af1e8090840e4b1", "mydb": "e39dd96753e2d8ee", "name": "", "arraymode": true, "x": 590, "y": 300, "wires": [ [ "7ee1e16f4871818b" ] ] }, { "id": "7ee1e16f4871818b", "type": "debug", "z": "3af1e8090840e4b1", "name": "debug 66", "active": false, "tosidebar": true, "console": false, "tostatus": false, "complete": "false", "statusVal": "", "statusType": "auto", "x": 810, "y": 300, "wires": [] }, { "id": "0252486550ca3c7f", "type": "debug", "z": "3af1e8090840e4b1", "name": "debug 67", "active": true, "tosidebar": true, "console": false, "tostatus": false, "complete": "false", "statusVal": "", "statusType": "auto", "x": 820, "y": 440, "wires": [] }, { "id": "3215177419507a14", "type": "DB2 for i", "z": "3af1e8090840e4b1", "mydb": "e39dd96753e2d8ee", "name": "", "arraymode": false, "x": 600, "y": 440, "wires": [ [ "0252486550ca3c7f" ] ] }, { "id": "e39dd96753e2d8ee", "type": "DB2 for i Config", "cnnname": "Galatea", "db": "LOCAL", "keepalive": true } ]

— Reply to this email directly, view it on GitHubhttps://github.com/IBM/node-red-contrib-db2-for-i/issues/15#issuecomment-1738828041, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AJ3QPHK5RBWB7FFUCZDY7RDX4VBRBANCNFSM6AAAAAA5KGDPS4. You are receiving this because you authored the thread.Message ID: @.***>

colin-grierson commented 1 year ago

Wow, So fast!

I am totally new to node-red and just exploring so this is not actually a problem for me, I’ll ask our open-source guy if he can help install your new version – but I’m not sure if he will be able to get to this.

You say the array output performs better, and it seems to be preferred as all the examples I have seen use it – perhaps array output should be the default?

Thanks again, Colin

We develop, integrate and manage mission critical systems

From: Benoit Marolleau @.> Sent: Friday, September 29, 2023 6:19 AM To: IBM/node-red-contrib-db2-for-i @.> Cc: Colin Grierson @.>; Mention @.> Subject: Re: [IBM/node-red-contrib-db2-for-i] 'select * from file', using a message for each row, returns correct number of messages - all with last row data (Issue #15)

Hi @colin-griersonhttps://github.com/colin-grierson , @AndyYouenshttps://github.com/AndyYouens

I solved the issue and pushed a latest version of the node (0.2.4) but not on npm yet, so it can only be installed manually with a git clone and npm install as specified here: Testing a node module locallyhttps://nodered.org/docs/creating-nodes/packaging I think this bug using multi-result mode that returns always the last record is due to the fact that the node.send() function is asynchronous and we work on the same message object (hence the side effet) . I managed to get the desired result by cloning the msg JSON object and work on a copy when invoking node.send() .Any better idea is welcome, but that works. Please refer to this original discussion node-red/node-red#1214https://github.com/node-red/node-red/issues/1214 So instead of a simple node.send(msg); ,

var newMessage = RED.util.cloneMessage(msg);

newMessage.payload=row;

node.send(newMessage);

Please test with a local install (instructions above) , let me know, and I'll update the npm package when I can. Note that we should consider to only use the "Array Mode" (single JSON Array output) and only use this multi-row mode when necessary, as the Array mode is more performant, and you can always split the output array if necessary. Thanks.

— Reply to this email directly, view it on GitHubhttps://github.com/IBM/node-red-contrib-db2-for-i/issues/15#issuecomment-1739726129, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AJ3QPHIPULXK732HLGGOHNDX4WWQBANCNFSM6AAAAAA5KGDPS4. You are receiving this because you were mentioned.Message ID: @.***>

bmarolleau commented 1 year ago

Hi Colin @colin-grierson ,

Again, thank YOU for your feedback. @AndyYouens , team, I've tested and pushed the latest version of the package 0.2.4 on the npmjs.org repo, so feel free to upgrade to the latest version and give it a try.

AndyYouens commented 1 year ago

morning @bmarolleau @colin-grierson I've loaded the new node locally and I can confirm it is working as intended. It is using 0.2.24

Colin take a look at my video on getting started with node-RED https://youtu.be/CQsW8WujbKU (a subscribe to my youtube channel would be appreciated :-) https://www.youtube.com/FormaServeSystemsLtdLoughton?sub_confirmation=1 )

Agree that the default should be array mode processing (think i mentioned that in my video :-) ) Shall I put in a PR for that change @bmarolleau ?

IBM / node-red-contrib-db2-for-i

'select * from file', using a message for each row, returns correct number of messages - all with last row data #15