I have a simple SELECT query in the MySQL node that is returning a null value, but when I run the same query (copy/paste from the expression editor) in Heidi SQL, I get the expected result. There is one variable, item_code, that may or may not have embedded commas in the value. Could that be the difference? I originally used query parameters but I hard-coded values while troubleshooting. PS:I got the same result when replacing ‘=’ with ‘LIKE’
SELECT SUM(actual_qty) AS totalAvail FROM tabBin bin
WHERE bin.item_code = ‘5150 STD, EBNY FB, ICE BL MET’
AND (bin.warehouse = ‘FP-Receiving - SHM’ OR bin.warehouse = ‘Retail Store - SHM’);
It could be that the expression was causing an issue, If you took the query you ran in Heidi and ran that in n8n did it work? Have you also tried a quick select count(*) from tabBin just to see if it returns anything?
Hi Jon,
First thing realized is that values in query parameters cannot have embedded commas. Values get truncated, and following parameters are not used and are numbered out of order, depending on the number of embedded commas. Seems obvious when I thought about it, with parameters using a comma delimeter.
The query worked fine as plain text, only failed when I inserted item_code as an expression. What did work was to add a Set node prior to the MySQL node to set the value to a fresh variable. Maybe the simplified path made a difference, as the initial variable was from 3 or 4 nodes further back? Don’t know, but I got it working and it’s good for now. On to the next project…
