Trouble with URLs

[wlaughto]

Can anyone explain why this (1):-

Code: Select all

put url "https://data.nsw.gov.au/data/api/3/action/datastore_search_sql?sql=SELECT%20count(notification_date)%20as%20numberofrecords%20from%20%222776dbb8-f807-4fb2-b1ed-184a6fc2c8aa%22%20WHERE%20notification_date%20between%20%272020-06-01%27%20and%20%272020-06-30%27" 

works ok, and this (2) doesn't:-

Code: Select all

   put "2020-06-01" into tFrom
   put "2020-06-30" into tTo
   put url "https://data.nsw.gov.au/data/api/3/action/datastore_search_sql?sql=SELECT%20count(notification_date)%20as%20numberofrecords%20from%20%222776dbb8-f807-4fb2-b1ed-184a6fc2c8aa%22%20WHERE%20notification_date%20between%20%27" & \
         tFrom & "%27%20and%20%27" & tTo & "%27" into tCases

Both pieces of code produce the same URL (theoretically, and confirmed).

Output from (2):-

{"help": "https://data.nsw.gov.au/data/api/3/acti ... search_sql", "success": false, "error": {"info": {"params": [{}], "statement": ["SELECT count(notification_date) as numberofrecords from \"2776dbb8-f807-4fb2-b1ed-184a6fc2c8aa\" WHERE notification_date between '"], "orig": ["unterminated quoted string at or near \"'\"\nLINE 1: ...07-4fb2-b1ed-184a6fc2c8aa\" WHERE notification_date between '\n ^\n"]}, "query": ["(psycopg2.ProgrammingError) unterminated quoted string at or near \"'\"\nLINE 1: ...07-4fb2-b1ed-184a6fc2c8aa\" WHERE notification_date between '\n ^\n [SQL: 'SELECT count(notification_date) as numberofrecords from \"2776dbb8-f807-4fb2-b1ed-184a6fc2c8aa\" WHERE notification_date between \\'']"], "__type": "Validation Error"}}2020-06-01%27%20and%20%272020-06-30%27

(1) correctly produces:-

{"help": "https://data.nsw.gov.au/data/api/3/acti ... search_sql", "success": true, "result": {"records": [{"numberofrecords": "71"}], "fields": [{"type": "int8", "id": "numberofrecords"}], "sql": "SELECT count(notification_date) as numberofrecords from \"2776dbb8-f807-4fb2-b1ed-184a6fc2c8aa\" WHERE notification_date between '2020-06-01' and '2020-06-30'"}}

The composed URL from 2 is:-

https://data.nsw.gov.au/data/api/3/acti ... 0-06-30%27

which is the same as 1:-

https://data.nsw.gov.au/data/api/3/acti ... 0-06-30%27

and both these URLs work when you click on them here!

Seems when Livecode sends the composed URL, it is not honouring the formatting somehow! Potentially a bug?

Any advice appreciated.

[Klaus]

Parentheses, parentheses, parentheses!
Alway use parentheses when constructing file-, object-names and URLs!

Try this:

Code: Select all

...
put "2020-06-01" into tFrom
put "2020-06-30" into tTo
   put url ("https://data.nsw.gov.au/data/api/3/action/datastore_search_sql? sql=SELECT%20count(notification_date)%20as%20numberofrecords%20from%20%222776dbb8-f807-4fb2-b1ed-184a6fc2c8aa%22%20WHERE%20notification_date%20between%20%27" & \
         tFrom & "%27%20and%20%27" & tTo & "%27") into tCases
...