Connector reply error: Unexpected lexem 'From' at (62,5). Expected '<end of file>'.

[kdaniels-obrien]

Hi, 

I am trying to load data using a rest connector to an end point.  I get the error message: Connector reply error: Unexpected lexem 'From' at (62,5). Expected '<end of file>'. Check SELECT STATEMENT syntax with documentation.  I have not seen this before and could not find much information regarding this message in the community or help documentation.  A sample version of my code is below.  If more information is required, please let me know! TIA!

I have another rest connection that grabs my access token (then used for vAccessToken) which is working.  I think the issue stems from the layers in the API, maybe more specifically ProcResult?

LIB CONNECT TO 'REST_generic_get ';

RestConnectorMasterTable:
SQL SELECT 

"My field name 1",

"My field name 2"

FROM JSON (wrap on) "root"

FROM JSON (wrap on) "ProcResult"
FROM JSON (wrap on) "Table"
WITH CONNECTION (
URL "https://<servername>/<stuff>/<stuff>/<stuff>",

HTTPHEADER "Authorization" "Bearer $(vAccessToken)",
QUERY "fromDate" "08/01/2010",
QUERY "toDate" "08/02/2010",
);

[Levi_Turner]

Unfortunately I do not have an API endpoint to test this theory but my initial thought is that the REST Connector is interpreting the / values in the QUERY incorrectly. I've found for other use cases (example here) that it's needed to transform special characters into their CHR representations. For / the chr representation is chr(47). For example from that guide:

// Replace the / characters with the chr representations
LET vBody = Replace(vBody,'/',chr(47));

So for your use case it'd be something like this:

LET vFrom = '08/01/2010';
LET vTo = '08/02/2010';

// Replace the / characters with the chr representations
LET vFrom = Replace(vFrom,'/',chr(47));
LET vTo = Replace(vTo,'/',chr(47));

...

URL "https://<servername>/<stuff>/<stuff>/<stuff>",

HTTPHEADER "Authorization" "Bearer $(vAccessToken)",
QUERY "fromDate" "$(vFrom)",
QUERY "toDate" "$(vTo)",
);