Call URL Action Improvements

Today we’ve added an ability to supply additional headers and perform POST, PUT, PATCH and DELETE HTTP calls to Call URL Action. Action’s setup interface is extended with Method, Headers and Body fields; latter is visible when method selected is POST, PUT or PATCH. Support for more HTTP methods opens the road for the database to perform API calls to many services such as Twitter or IFTTT. In addition, we added conditional content generation and improved hidden logic behind <% %> code blocks.

Recap

We introduced code blocks first with XHTML – Formulas. The idea behind code blocks was to allow writing XHTML markup directly, eliminating quoting and escaping the string needed in regular formulas, with placeholders for database-provided content. Also, placeholders escape characters that have special meaning in HTML automatically. Later we’ve added format blocks that perform implicit call to Format() function to produce locale-aware text representation  for dates, times and numbers. As primary function of XHTML – Formulas is to provide HTML styling for column’s content, the formula produces no markup when one of the format/code block’s calculated value is NULL. If there is no content, no markup needed.

We took this logic intact to Call URL’s Request property, but there it introduced a problem – nullifying whole URL when one code block evaluates to NULL results to failures on attempt to call web server with no URL provided. It became clear that further expansion of this logic XML and JSON will lead to more errors as empty content is not valid in either XML or JSON. So, we have decided to change format/code blocks behavior: restrict their usage to well-defined positions and adopt the behavior to the context code/format block is used in to ensure its output won’t break the result. Plus, we are introducing conditional content generation via <%? condition %>content<%?%>. Changes only apply to Call URL formulas, XHTML – Formula behavior is left intact.

Let’s see how new logic will work for each type of content.

Request URL

URL can be divided into logical parts – server address:

protocol://server/path/to/api

…and optional query string part

?param=value&param=value…

If query string part is detected in Request URL formula we break it into name=value pairs. As code blocks are used to generate database-provided values you can use them in place of parameter values only. It is unlikely that parameter name is database controlled. When value contains code block(s) and one of code blocks outputs NULL we’ll remove preceding ampersand, parameter name and equal sign, so instead of no-value-no-URL it’s now no-value-no-parameter logic. If you want to keep parameter in place you can always wrap code block expression with Nz(). Non-empty content produced by code block is automatically encoded according to URL encoding rules.

Conditional blocks can only appear either in the beginning or end of query string and around ampersands – this way you can safely remove any number of name-value pairs without a chance of generating malformed query string.

Back to request URL, in server address part restrictions are relaxed, and condition and code blocks can be placed anywhere. The only restriction is that condition block cannot include question mark though it is not fatal – presence of question mark with no query string has no special meaning nothing, but absence of question mark due to false condition will make query string the part of server address which is obviously an error.

Note on format code blocks

Format blocks are allowed to keep the compatibility with formulas already written but their use may lead to unexpected results. The service to call likely expects dates, times and numbers in certain predefined format. As format block performs implicit Format([Column]) call, the value is formatted according to locale preferences: in case Call URL action is triggered from Time-Dependent or Periodic trigger this is the locale of the database, but in case of Record Change triggers this is the locale of the user performing the change – in case of, say, server expecting the date in US format and user’s UK locale you’ll likely get back completely unexpected result. Instead of using format blocks we would recommend to use code blocks performing explicit Format() call explicitly supplying locale identifier.

The only real use for format blocks is an injection of raw URL content via <%=[URL Column]%> or <%=[URL Formula]%>. Since format block refers to URL column/formula type we do not perform encoding.

Headers

HTTP headers consist of name:value pairs each pair is placed on separate line. As each header type require value in its own format we do not expect much use for database content. Nevertheless, headers behave similar to quest strings. Code blocks are allowed in place of values only; NULL value generated by code block removes whole name:value pair; condition blocks are allowed only at the beginning or end of line.

As there is no standard for encoding header values, code block results are placed verbatim to the formula output with one special case for basic access authentication (BA). To authorize via BA you must supply

Authorization: Basic <base64-encoded-username-colon-password>

You can supply username and password separated by colon either pre-encoded with base64 (won’t contain colon) or in plain text (should contain colon even if password is empty); in latter case we’ll encode it via base64 algorithm automatically.

Request Body: Form

This type mimics the functionality implemented in the browser to send user input to the server. As with query string part of Request URL input consist of param=value pairs separated by ampersand except of there is no question mark at the beginning. Logic is similar as well: code blocks can only be placed in a value part, NULL value produced by code block remove preceding ampersand, parameter name and equal sign, condition blocks are allowed in the beginning or end of the formula or around ampersand. Use of format blocks is disabled due there is no well-defined formatting rules; use code blocks, and format date, time and numbers manually according third-party service specification.

Request Body: XML

Here we have two distinct behaviors for code blocks placed inside of attribute value quotes and code blocks between tag names. Any format/code block inside attribute value quotes resulting to NULL will remove preceding attribute name, equal sign and surrounding quotes. Format/Code blocks placed between tags and resulting to NULL render no text but keep surrounding content intact.

To exclude elements from rendering condition blocks can be placed in a text between tags, but not between attributes or at the document level, and should wrap both opening and closing tag. As XML has well defined culture-invariant type system, format blocks are no longer restricted to only column references (from where we derive formatting in case of Request URL). You can write any expression that will be formatted according to XML rules. Boolean expressions are formatted as true/false. Numbers contain no group separator and always use dot as a decimal point. Dates are formatted as yyyy-mm-dd. Times are rendered in hh:mm:ss using 24 hour format. Timestamps are formatted as composition of date and time using capital letter T as a separator between parts and Z at the end to designate UTC timezone. Durations are dumped as a number of seconds in PTnnnnS format.

Request Body: JSON

JSON core concept is value, either primitive (null, true/false for booleans, numbers, or text enclosed in quotes) or composite – array or object. Array is a comma separated list of values (primitive, arrays or objects) enclosed in square brackets. Object is comma separated list of “name”: value pairs surrounded by curly braces. Top level content is a single value, primitive and composite. No empty content is allowed. There is no special format for dates, times or timestamps, they are typically produced as quoted strings in ISO 8601 format like timestamps in XML.

Format blocks can appear wherever value is expected. Since JSON data is typed they support expressions and produce culture-invariant formatted values. As string can be rendered either as “some text” or null, format blocks cannot appear inside of quotes; use code blocks instead.

Condition blocks can only appear inside of objects or arrays after opening bracket, before closing bracket or around commas.

Request Body: Text

This type can be used to generate any kind of structured or unstructured text content. Condition/code/format blocks are allowed everywhere. No encoding is performed. Format blocks produce locale-aware formatted values.