Protocol

This webservice uses the http protocol to submit queries to the server and to receive results. You can submit a query using an ordinary web browser and a plain old form, or you can use an http request feature of most web programming languages, or you can program a browser application (Javascript) via XMLHttpRequest or equivalent, to submit queries in AJAX style.

Requests can be made by either POST or GET methods. If the role used requires an authcode, then the POST method must be used; for security reasons, we do not accept authcodes in urls.

XML

When retrieving data in XML form, the root element is named 'xml', and it always contains an element named 'status'. The status element has the attribute 'value' with the value 'complete', 'incomplete', or 'error'. If the value attribute is 'error', there will be a sibling element 'error' with attribute 'code'. The code attribute will have an error number, and the error body will be an error message. Both the code and the message generally originate within a subsystem such as PostgreSQL or the XML parser.

If there was no error, there will be a sibling element named 'rowcount' with the number of rows affected. If the query was a SELECT, then there will be another sibling container element 'records', with the data retrieved. See Result Formats for details on how the records are represented, because it varies from format to format.

JSON

When retrieving data in JSON form, after deserializing to an object named data, data['status'][0] will always exist and be 'complete', 'incomplete', or 'error'. If 'complete' or 'incomplete', data['rowcount'][0] will be the number of records affected. If 'error', then data['error'][0] will be the error code, and data['error'][1] will be an error message.

If there was no error, data['records'] will contain the data retrieved. See Result Formats for details on how the records are represented, because it varies from format to format.

The Error Codes page can tell you more about the types of errors, and how to research the meaning of a specific error.

Deferred Queries

If your query cannot be optimized to complete within 8 seconds, you should run it as a deferred query. A deferred query returns a status page (in XML or JSON) immediately, and runs the query in a background task allowing 10 minutes per query. If you provide a postback url as the deferred parameter, it will request that page (on your site, presumably) when it completes. The same XML or JSON page that would have been served on a regular query is provided as the body of the postback request.

Parameters

The complete list of request parameters:

q: The SQL query itself. It may optionally include interpolation tokens '%s', cookie values '%{cookiename}', or cgi-variables %[cgi-var]. Use '%%' if you need a '%' in the query, such as for use with 'LIKE'.
Example: UPDATE TABLE users (email) VALUES(%s) WHERE id = %{userid}
arg<###>: arg000,arg001..arg099 are arguments to be safely interpolated into the SQL query. The number of these must match the number of '%s' interpolation tokens in 'q', and they must be consecutively numbered starting at arg000.
arg:<name>: Named arguments are submitted using a compound name. Append the chosen name to the string 'arg:' for the parameter name. These will be safely interpolated into the SQL query to replace substitution tokens that include the chosen name. For example an argument named 'red' would be sent as parameter 'arg:red', and replace the token '%(red)'.
argtype<###>: argtype000,argtype001..argtype999 are type strings indicating what type the corresponding arg<###> argument is. The possible values are: 'NONE','STRING','NUMBER','DATETIME','ROWID','BINARY','DATE','TIME'. These parameters are optional, so you can include for all arguments, no arguments, or any subset. The default is 'STRING'.
argtype:<name>: These type strings indicate what type type the corresponding arg:<name> argument is. The possible values are: 'NONE','STRING','NUMBER','DATETIME','ROWID','BINARY','DATE','TIME'. These parameters are optional, so you can include for all arguments, no arguments, or any subset. The default is 'STRING'.
authcode: Authcode for role provided in url. default=''
format: What format do you want the results in? options are 'xml', 'xml-easy', 'xml', 'json', 'json-easy', 'jsonp', 'jsonp-easy', and 'binary'. default='xml'.
deferred: If request is to be run as deferred, with the more generous time limit, provide a value here: either 'yes', or a postback url. If the value looks like a url, server will load that page when the query completes. optional
callback: If requested format is 'jsonp' or 'jsonp-easy', then use this parameter to indicate the name to pad with. This parameter is an alternative to appending the pad to the format value. 'format=jsonp:dosomething" is equivalent to 'format=jsonp&callback=dosomething". If format is omitted, the presence of callback implies 'jsonp-easy' format. So, 'format=jsonp-easy:doit', 'format=jsonp-easy&callback=doit', and 'callback=doit'(no format string) are all equivalent. optional
contenttype: If requested format is 'binary', then use this parameter to indicate what content-type header to put on the page. This is an alternative to appending the type to the format. 'format=binary:text/plain' and 'format=binary&contenttype=text/plain' are equivalent. default='application/octet-stream'. optional

Cookie Values

Cookie values can be included in the query by incorporating the cookie name like '%{cookiename}'. The value of that cookie (or '') will be safely substituted into the query.

The cookies would presumably be created by the application, in Javascript or Python, so the cookie tokens are a convenient alternative to just using one more '%s' token.

CGI Variables

CGI Variable values can be included in the query by incorporating the cgi-var name like '%[cgi-var]'. The value of that cgi-variable (or '') will be safely substituted into the query.

This can be useful, for example, for recording the user's IP address; the IP address is in the CGI variable 'REMOTE_ADDR', and can be included in your query like 'INSERT INTO tablename (userid, ip) VALUES(%s, %[REMOTE_ADDR])'