Trying to use SQL API from Oracle PL/SQL to CartoDB

Question

I am trying to make an http call from within an Oracle server to CartoDB. To do so I have developed a custom PL/SQL package that will generate the SQL and make the calls. A sample API call is below. The return status looks like a permissions problem ({"error":["permission denied for relation address"]}

Any ideas?

http://mycompany.cartodb.com/api/v2/sql?q=INSERT
INTO address(address_id,street_number,city,state,zip_code) SELECT
'BBE20140321095101832AM5656' address_id, '3469 Shelby Ganges Road'
street_number, 'Shelby' city, 'Ohio' state, '44875' zip_code WHERE NOT EXISTS (
select 1 from ADDRESS WHERE ADDRESS_ID =
'BBE20140321095101832AM5656')&api_key=488e214c52f78b55f2fe9c05daef94074c7e3cd2

Answer 1

Well, before answering, some points:

1. Although i see the domain is hidden, you should never publish your api_key.
2. When using the api_key you should always use https, not http (and maybe CartoDB should enforce this).

Now considering your error my most obvious guess would be that the api_key is wrong or it is not being correctly pass to the API. The error basically says that the user executing the query does not have permissions, but considering that you are passing the api_key, which makes the query be ran as owner of the DB, my best guess is that the api_key is not being correctly interpreted. My best would be:

1. The api_key is wrong
2. Something on the query is making the api_key be ignored, try encoding the sql as a url param.