Query works in PGAdmin but fails on the server(node \ localhost)

Question

We are using node server and we use pg module to call to our postgres database. When making a query in pgadmin, I get the desired results, but when trying to run the same query on our server(js) it throws errors, anyone knows what can cause this and how can this be fixed?

Here is a query:

 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)

This runs perfectly in PGAdmin(replacing $1 and $2 with params), and a very similar query runs both in pgAdmin and Server with no errors what so ever, but this fails. Here is a 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);

we tried changing names to aliases, reordering items, changing the db string and still nothing.

score 1 · Answer 1 · answered May 17 '18 at 20:46

Okay, so I have found the reason and posting it here for those who will struggle with the same issue, when concatenating strings in query on the web side, make sure there are spaces, for example:

  "SELECT DISTINCT name, address, city" +
  "FROM public.city JOIN employees"...

is wrong, correct version will look like this:

  "SELECT DISTINCT name, address, city" +
  " FROM public.city JOIN employees"...
   ^_________note this space_______

Query works in PGAdmin but fails on the server(node \ localhost)

1 Answers1