DataStore API SQL queries respond with Authorization Error

[philipashlock]

Once you properly construct a DataStore API SQL query using the appropriate quotes (see original description of the issue below) the API responds with an Authorization Error. For example, this query should work fine:

https://inventory.data.gov/api/action/datastore_search_sql?sql=SELECT * from "02c2b1c6-a42d-4a10-9e8b-9cf06e54a82b" WHERE "AWARD_YEAR" = '2014'

Updating name of this issue from "DataStore API SQL queries expect column names to be lowercase" to "DataStore API SQL queries respond with Authorization Error"

Originally the problem was thought to be simply an issue of capitalizing the column names, but that issue should be addressed by quoting the column names - this is another thing that could also be better articulated in the documentation and examples for the DataStore API.

---

Original description:
It looks like the way SQL queries are handled with the DataStore API expects all column names to be lowercase. If you specify an uppercase column name it won't find it (even if it exists) because it runs the query using a the column name as lowercase.

Here's an example:
https://inventory.data.gov/api/action/datastore_search_sql?sql=SELECT%20*%20from%20%2202c2b1c6-a42d-4a10-9e8b-9cf06e54a82b%22%20WHERE%20AWARD_YEAR%20LIKE%20%272014%27

This looks like it's specifically a problem for using the API with GET and the full SQL syntax. It's not a problem using POST and specifying the parameters as JSON in the body.

[philipashlock]

As a workaround you can also specify the query with a JSON object on the datastore_search endpoint.

Instead of a SQL statement like WHERE Status = 'Active' you'd have a JSON object like {"Status":"Active"}

For example:

https://inventory.data.gov/api/action/datastore_search?resource_id=1705fe89-ffc3-420d-a385-f365f75fa7eb&filters={%22Status%22:%22Active%22}

There's also a workaround using the datastore_search endpoint with POST

POST /api/3/action/datastore_search HTTP/1.1
Host: inventory.data.gov
Content-Type: application/x-www-form-urlencoded; charset=UTF-8

{"resource_id":"1705fe89-ffc3-420d-a385-f365f75fa7eb","q":"","filters":{"Status":"Active"},"limit":100,"offset":0}

Here it is in javascript:

var xhr = new XMLHttpRequest();
xhr.open("POST", "https://inventory.data.gov/api/3/action/datastore_search");
xhr.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
xhr.send('{"resource_id":"1705fe89-ffc3-420d-a385-f365f75fa7eb","q":"","filters":{"Status":"Active"},"limit":100,"offset":0}');

Here's a screenshot using the RESTClient extension for firefox:

[kvuppala]

@philipashlock

I was able to access and filter the data with the below query as well.

https://inventory.data.gov/api/action/datastore_search?resource_id=1705fe89-ffc3-420d-a385-f365f75fa7eb&filters={%22Status%22:%22Active%22}

Are we looking at this API call or any other API call fix?

[kvuppala]

@philipashlock
I see that SQL query is the issue, ignore the above comment.

[philipashlock]

I think the example you gave is an easier alternative than using POST so thanks for mentioning that, but yeah the issue remains with using the datastore_search_sql endpoint

[ykhadilkar]

@philipashlock I think for upper case column name should be in quotes.
But when I tried it im getting access denied error ... not sure why
https://inventory.data.gov/api/action/datastore_search_sql?sql=SELECT%20*%20from%20%2202c2b1c6-a42d-4a10-9e8b-9cf06e54a82b%22%20WHERE%20%22AWARD_YEAR%22%20=%272014%27

http://stackoverflow.com/questions/20878932/are-postgresql-column-names-case-sensitive

[davekiss]

Thanks for the capitalization tip, I was stuck on this for a minute