BLOB, CLOB, Large Strings, Large Binary

BLOB, CLOB, Large Strings, Large Binary
lac40
You can store images, icons, pictures, documents, voice, video, and other binary data in 
CA Live API Creator
 using various SQL database datatypes (for example, Binary Large OBjects (BLOBs) and Character Large OBjects (CLOBs)).
In this article:
 
 
2
 
 
CLOB
API Creator merges the database concepts of CHAR, VARCHAR, TEXT, LONG TEXT, and CLOB into a single JSON string. Client programs see only 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 Base64-encoded 
icon
 string.
The JSON for all binary results is returned in one of the following formats:
  • An INLINED value.
     A JSON object ( 
    icon
     attribute), 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"
    }
    API Creator reads INLINED values from the database in its entirety.
    Reading large files, such as video and audio files, can impact API Creator's performance.
  • A DEFERRED value.
     A JSON object with 
    type
    length
    , and 
    url
     properties.
    For DEFERRED values, API Creator reads only the number of characters or bytes you have defined as the limit to calculate checksum. If the value is smaller than this limit, API Creator reads the value is its entirety.
    For example:
    For more information about where to define the number of characters or bytes API Creator uses to calculate checksum, see API Properties.
You can choose how API Creator emits binary data for your API and per-request parameters. By default, strings are returned in the INLINED format 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 also set the limit project-wide. Values larger than this limit are returned in the DEFERRED format. Values this size or less than this limit are returned in the INLINED format.
You can adjust the value on a per request basis using the 
inlinelimit
 query parameter:
...?inlinelimit=1000
For more information:
  • About how to choose the encoding scheme for your API, how to define the default values for the 
    inlinelimit
     query parameter, and about how to force binary data as an object, see API Properties.
  • About how API Creator emits binary data, see Use Binary Data.
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. The deferred query parameter 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 request 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 query parameter 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 API attributes. API Creator decides whether to return a value inline in the JSON or deferred to a link for binary or string data using the number of characters or bytes.
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.  
 Note: 
The following is returned in place of the URL for views and tables that do not have a primary key defined:
"inlineLimitExceeded": 6000
Stream Data Directly into the Database
You can stream data directly into the database using HTML multi-part forms if the underlying database/driver combination supports it. For example, Oracle and Microsoft SQL Server support streaming data directly into the database using HTML multi-part forms. MySQL also supports streaming data into the database using HTML multi-part forms, but has some limitations and requirements on the MySQL configuration.
While a POSTing an HTTP request, 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 that are 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. You can create an HTML file and run it using the Demo API:
The "checksum" == "override", while convenient for testing, is not recommended for production.