BLOB, CLOB, Large Strings, Large Binary

API Creator supports Binary Large OBjects (BLOBs) and Character Large OBjects (CLOBs).

CLOB

API Creator merges the database concepts of CHAR, VARCHAR, TEXT, LONG TEXT, and CLOB into a single JSON string. This means that client programs see just a string regardless of the underlying datatype.

The JSON for all character results is returned in one of the following formats:

  • An INLINED value. A standard JSON string, for example "ABC."
  • A DEFERRED value. A JSON object containing the length of the string and a URL.

For example:

"name": {
    "length": 12,
    "url": "https://demodev.espressologic.com/data/el-dev/demo/v1/demo:employee/1/name"
}

You can use the JSON object to retrieve objects.

BLOB

API Creator merges all database binary types to a single base 64-encoded string.

The JSON for all binary results is returned in one of the following formats:

  • An INLINED value. A JSON object, with type, length, and value properties. For example:
"icon": {
"type": "base64",
"length": 1185,
"value": "iVBORw0KGgoAAAANSUhEUgAAADIAAABKCAIAAAB2LJBKAAAEaElEQVRoBdWZi5bbIAxE657+/y+7AsEwCIlHnE3aPV2vDNLoMsbOo9d937/o57ro5Hvhb279jzAJ0h9gHTJNXO3sh/5RkLDeB6StlfgRnEgc1U9Miuw40i8iR1gvMIH1DG4f6wnTMVx3J6L6x4LdtX0Ya3e9n8faMuzzWGLYmuwrWEuy9CTduXXX69vdNU6eA/Attxw6Hvq/sRyfeXEPYl/5627JrnU27iaWU/nAobHU6m9i+VaP8u8YSf5tYtnVvKP9TGMTaybxE3ObWB+4iN0Fae/lny9a2Tv5A1GsPAWbbh30gvw2klTYok2srRaG3em2JZOS3oalECCzyw+B3MStBwR6hdplon5Cd1utinn+ft0tYagYWVG/NLhvLEICxNyT4oh/yy3SqSGAENSZ9Fdp+i83eF7jiElm125NFzx8Hr/qSC7r7WxgAjRhSnlSPs/wsbSG59KIXMHryiZ1mmDNYN1UHnEO88cp93WKZQh8KdVj8stWo3OssFp5hQngcxMoUyrmuanpgzuRdkDrCsy2ojJELE5SS8/RhH1RTG2Kphak8fJ7iZncgBTH6g6Mq3iCFHiYYlfYLePM3K8920ivC10sV7wr4046YYRUwk3L+eNMp//63upk6jZWmogJJQsobHnNy48c1YSCH0RJRadS+sUyere7sb5opRGJ01FuJn71yI/oqKNtoQR2tD4yVrPENUrArTr1lKnqtOcZRrogoq5Jb9tbKsjL4ri2K9eufznCZAs6LL6gLeUbUYclABOy9H4g/0Scoz3jCGrnhlksJpMNMOdAj0kwIZtUOVjIhiLDuauUTCSjfBm4UlrlYwmHTOvRqK/uIZPun+br4E/pqI8lcy5TqtHHn1bTMRhOGcbIlJl/qdqGPtbEXhFQYjSrwZaPOblkhisfHqeFuhTUN+Z2LRmNByOrRtJxhHUQR26lhGpDSRYLjYsmAaLPAx9LVx+arFciJxWyANAMy6kZiRbgY0XZdVdlcflAUfNSv8kVr2neX59zjYV2MK/C7S5daNBcanUB9eihRluec6PtLHckpx3FS2vXbkX9BBf+RTk8DsN0UGsjhQMsXE1uthOb3vl04fQBFnYILgGCGE4YEgHItCTeGEXpCOv4dovaL9eziwUhCbD02CSdMVeKTzl2ZLZ6gKl0600ws2ji0muyO4VCCZJb8708duWReS130liAlkwJK/cwN29TA0HWas4TTao1s3Wk6UgEKartEvgk/CIpF6Mll9hYmNDSztXzHkVk2wprSvc3xJKsidvYXYmpE9QT21XyhUz+obArqhLKK8fwTjT1ME/lYE/q1HVIJ6ZW52mRfUX+sklQlI2PWapPVi0cDVYeL43GKSKAwBDk/goxzDW3ogSUmITWevQGXqJ4DETOKHLObG9xnokNSmM0efGpKmSnxyT674K2RKyiBkHxKHc8InD9CtXE7jaymwsFQvyCH0eM2gLWVEOSRsMC0JH082Rg8d4SxJ82ZZe8Yf0rRJn8LyOOamREwQ06AAAAAElFTkSuQmCC"
}
  • A DEFERRED value. A JSON object with type, length, and url properties. For example:
"icon": {
"type": "base64",
"length": 1185,
"url": "https://demodev.espressologic.com/data/el-dev/demo/v1/demo:employee_picture/1/icon"
}

You can control the values using project properties and per request parameters. By default, strings are INLINED when the value in the column for a particular row is 2000 characters or less. BLOBs use the same value but the length is in 'bytes'. You can set the limit project-wide using the Inline Limit Default project setting. Values larger than this limit are returned in the DEFERRED format. Values this size or less than this limit are returned in the INLINE format.

You can adjust this on a per request basis using the inlinelimit query parameter:

...?inlinelimit=1000

Control Behavior on a Per-column Basis

You can control behavior on a per-column basis using the inline and deferred query parameters. You can use them in GET, PUT, and POST requests.

The deferred Query Parameter

The deferred query parameter is a comma-separated list of Resource.attribute names that is returned as deferred links. This applies only to BINARY and STRING data. deferred query parameter values take precedence over inline and inlinelimit query parameter values. The value is also used for transaction summaries, but you must use database names. For example:

...?deferred=Cust.Orders.LineItem.Product.Photo,Cust.Orders.SalesRep.EmployeePicture

This returns the Cust.Orders.LineItem.Product.Photo object as a deferred link regardless of the size.

The inline Query Parameter

The inline query parameter is a comma-separated list of Resource.attribute names that is returned as inline values. This applies only to BINARY and STRING data. inline query parameter values take precedence over inlinelimit query parameter values. The value is also used for transaction summaries, but you must use database names. For example:

...?inline=Cust.Address.Map

The inlinelimit Query Parameter

The inlinelimit query parameter overrides the project setting. Number of characters or bytes used to decide whether a value is returned inline in the JSON or deferred to a link for BINARY or STRING data.

The deferred and inline query parameters take precedence over the inlinelimit query parameter values. A value of zero (0) results in ALL non-null values returned as a link. A value of -1 results in all values returned as a link.

Performance Impacts

When a value is INLINED, it must be read from the database in its entirety. When a value is DEFERRED, only the number of bytes or characters specified by the checksum limit need to be read from the database. Values whose size is smaller than the inline limit is read entirely. In large files, such a video and audio, this can have significant performance impact.

Stream Data Directly into the Database

You can stream data directly into the database when supported by the underlying database/driver combination using HTML multi-part forms. Oracle and SQL Server are well supported for this. MySQL is supported, but has some limitations and requirements on the MySQL configuration.

While a POST HTTP request is being performed, the following requirement must be met:

  • The update must be to an existing record, based on the primary key.
  • A checksum value must be provided.
  • The authorization value must be provided with a valid API key.

The values sent by the browser are streamed directly in the database and are not available to rules during the transaction.

The following simple HTML page demonstrates it usage:

Note: The "checksum" == "override", while convenient for testing, is not recommended for production.

<html>
<body>
<h2>File Upload Example</h2>
<form action="https://demodev.espressologic.com/rest/abl/demo/v1/employee_picture/1" method="post" enctype="multipart/form-data">
<input type="hidden" name="checksum" value="override" />
<input type="hidden" name="authorization" value="demo_full:1" />
<p>Select file for ICON <input type="file" name="icon" size="70" /></p>
<p>Select file for PICTURE <input type="file" name="picture" size="70" /></p>
<p>Select file for VOICE <input type="file" name="voice" size="70" /></p>
<input type="submit" value="Upload Them" />
</form>
</body>

</html>

More Information

For more information:
  • About the API project settings, such as Inline Limit Default, Checksum Size Limit, and Stored Procedure Row Limit, see API Properties.
  • About how to use the JSON object, see JavaScript.