Docs‎ > ‎CA Live API Creator‎ > ‎Create‎ > ‎Resources‎ > ‎REST APIs‎ > ‎

BLOB, CLOB, large strings, large binary

API Creator supports binary and large character objects.  This page will use the term BLOB (for binary large object) and CLOB (for character large object).



CLOB Support


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


For all character results, the JSON returned will be in one of two formats:


a) An 'INLINED' value - a standard JSON string - e.g. "ABC"

 


b) A 'DEFERRED' value - a JSON Object containing the length of the string and a URL that can be used to retrieve the value. 

    "name": {

      "length": 12,

      "url": "https://demodev.espressologic.com/data/el-dev/demo/v1/demo:employee/1/name"

    }



BLOB Support


API Creator merges all database binary types to a single base 64 encoded string.  Like strings, they can be returned INLINE or DEFERRED.


a) An 'INLINED' VALUE - a JSON object, with 'type', 'length', and 'value' properties.


"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"

    }



b) A 'DEFERRED' value - a JSON object wih 'type', 'length', and 'url' properties.


"icon": {

      "type": "base64",

      "length": 1185,

      "url": "https://demodev.espressologic.com/data/el-dev/demo/v1/demo:employee_picture/1/icon"

    }



There are several project properties and per request parameters that can be used to control the values.


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'.  This is a project wide setting 'Inline Limit Default'  Any values larger that this limit are returned in the DEFERRED format.  This size or less are returned in INLINE.  This can also be adjusted on a per request basis using the inlineamount query parameter:


...?inlinelimit=1000


Request Parameters


This behavior can be controlled on a per-column basis using the inline and deferred query parameters.  They can be used in GET, PUT and POST requests. (DELETE requests do not require any additional feature)



Deferred

Comma-separated list of Resource.attribute names that will be returned as deferred links. This applies only to BINARY and STRING data.  'deferred' values take precedence over 'inline' and 'inlineamount' values.  The value is also used for transaction summaries, but database names must be used.


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


This will cause the Cust.Orders.LineItem.Product.Photo object to be returned as a deferred link regardless of the size.

Inline
Comma-separated list of Resource.attribute names that will be returned as inline values. This applies only to BINARY and STRING data. 
'inline' values take precedence over 'inlineamount' values.  
The value is also used for transaction summaries, but database names must be used.

...?inline=Cust.Address.Map



Inlinelimit

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. 

Both 'deferred' and 'inline' take precendence over 'inlinelimit' values.  A value of 0 will result in ALL non-null values returned as a link. A value of -1 will result in all values returned as a link.


Project Properties


Inline Limit Default

The default values for inlinelimit if not provided as a request parameter.


Checksum Size Limit

The number of characters or bytes used to calculate the checksum.  For obvious reasons, it would be impractical to compute a checksum on a very large (e.g. gigabytes) blob. This setting defines how many characters/bytes will be used to compute the check sum. If the column is larger than that, the checksum will be computed using the total size and the checksum of the first n bytes/characters where n is the value of Checksum Size Limit.

This is unrelated to the type of value returned (inline or deferred) and can be larger or smaller that the inline limit.


Stored Procedure Row Limit

Maximum number of rows returned for each result set returned by a stored procedure.  These are NOT pageable.  The will be a value in the JSON telling you the limit was exceeded.


Stored Procedure Inline Limit

Values returned in a stored procedure use this to determine when to inline.  Unfortunately, it is NOT possible to generate a URL for a deferred link.  When a value is exceeds the inline limit, just the length is returned.


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, will be read entirely.  In large files such a video and audio, this can have significant performance improvements



API Creator has support for multi-part form uploads


In addition to base64 encoded values for BLOBs as part of the normal JSON processing, HTML Multi-part Forms can be used to stream the data directly into the database when supported by the underlying database/driver combination.  Oracle and SQL Server are well supported for this. MySQL is supported, but has some limitations and requirements on the MySQL configuration.


Currently, while a POST HTTP Request is being performed, this MUST be an update 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.

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>