We have encountered an issue with our node server and pg module when querying our postgres database. While the query executes successfully in PGAdmin, it throws errors on our server (js). Can anyone provide insights into what might be causing this discrepancy and how we can resolve it?
Below is the query in question:
SELECT DISTINCT waswindows.computername as servername, waswindows.appcode, ipaddress, result, WASInventorywindows.uploaddate
FROM public.waswindows JOIN WASInventorywindows
ON (waswindows.appcode = $1 OR waswindows.computername LIKE $2 OR WASInventorywindows.appcustodian LIKE $2)
AND position(WASInventorywindows.servername in waswindows.computername) > 0
AND result = 'FAILED' AND waswindows.uploaddate = (SELECT MAX(uploaddate) FROM waswindows)
The query runs smoothly in PGAdmin (after replacing $1 and $2 with parameters), however a similar query runs without any errors both in PGAdmin and the Server. Below is an excerpt from the error log:
PARAMS ARE: Alex AND %Alex%
{ error: syntax error at or near "JOIN"
at Connection.parseE (C:\Users\326009107.MAPLE\Desktop\Compliance\GHSRiskTSSCompliance\node_modules\pg\lib\connection.js:567:11)
at Connection.parseMessage (C:\Users\326009107.MAPLE\Desktop\Compliance\GHSRiskTSSCompliance\node_modules\pg\lib\connection.js:391:17)
at Socket.<anonymous> (C:\Users\326009107.MAPLE\Desktop\Compliance\GHSRiskTSSCompliance\node_modules\pg\lib\connection.js:129:22)
at emitOne (events.js:96:13)
at Socket.emit (events.js:188:7)
at readableAddChunk (_stream_readable.js:176:18)
at Socket.Readable.push (_stream_readable.js:134:10)
at TCP.onread (net.js:548:20)
name: 'error',
length: 94,
severity: 'ERROR',
code: '42601',
detail: undefined,
hint: undefined,
position: '143',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'scan.l',
line: '1086',
routine: 'scanner_yyerror' }
C:\Users\326009107.MAPLE\Desktop\Compliance\GHSRiskTSSCompliance\API\WAS\getdata.js:44
set.push(results.rows);
Despite attempting various adjustments such as using aliases, changing the order of items, and modifying the database string, the issue persists.