Overview

About ql.io

Language Overview

Runtime Overview

Usage Overview

Getting Started

Prerequisites

Quick Start

Build an App

Examples

Build and Develop ql.io!

Writing Scripts

create table

connectors

select

if-else

insert

update

delete

URI Templates

Data Formats

Variable References

Configuration

Script Routes

Monkey Patching

UDF

try-catch-finally

Timeouts and Back-off

OAuth

Executing Scripts

HTTP Interface

WebSocket Interface

Engine API

Caching (Memcache)

Fallback

Conditions

Monitoring

Runtime Monitoring

References

Language Reference

URI Template Syntax Reference

Best Practices Guide

Troubleshooting

insert into Statement

This statement is used to add new data. Idempotency of this statement depends on the HTTP method used by the API.
Below we have three methods to use the insert statement

Method 1

This approach is useful when there is a limited number of column-value pairs.

insert into table ( columnsClause ) values ( csv ) with parts
insert into {table} ({list of columns}) values ({comma-separated name-value pairs}) with parts {comma-separated list of parts}

Examples

Try me
insert into bitly.shorten (longUrl) values ('http://ql.io/docs')
    insert into flickr.photos.upload with parts "{req.parts[0]}", "{req.parts[1]}"

Clauses

Columns

Comma separated list of columns.

Values

Comma-separated name-value pairs. The number of pairs must match the number of columns.

Parts

Comma-separated list of parts. Parts are multipart attachments (rfc1341) and can be sent to ql.io using 'multipart/form-data', 'multipart/related' or 'multipart/mixed' content types. The first part is considered the body of the request, while the rest are considered attachments.

Method 2

You can use this method when there are many column-value pairs which are better represented in the form of a json object.

insert " { jsonobj } " into table
insert {"{json obj}"} into {table}

Json Object

Creating a json object is another way to specify name-value pairs. You can directly insert the object into the table.

Examples

Assume there is a table
--Please make sure you have a valid eBayAuthToken
create table SetSellingManagerFeedbackOptions
    on insert post to "https://api.ebay.com/ws/api.dll?"
    using headers 'X-EBAY-API-COMPATIBILITY-LEVEL' = '737',
                  'X-EBAY-API-SITEID' = '0',
                  'X-EBAY-API-CALL-NAME'= 'SetSellingManagerFeedbackOptions'
    using defaults RequesterCredentials.eBayAuthToken = '{eBayAuthToken}'
    using bodyTemplate 'SetSellingManagerFeedbackOptions.ejs' type 'application/xml'
    resultset 'SetSellingManagerFeedbackOptionsResponse'
The insert statement would look like :

Try me

json = {
       "RequesterCredentials.eBayAuthToken" = 'eBayAuthToken',
       "AutomatedLeaveFeedbackEvent" : "PaymentReceived",
       "StoredComments.StoredCommentText" : "Prompt payment!",
       "StoredComments.StoredCommentText" : "Paid quickly!",
       "StoredComments.StoredCommentText" : "Fast payment!"
       }

insert {"{json}"} into SetSellingManagerFeedbackOptions

Method 3

Use this approach when you need to pass opaque parameters. You can pass the entire xml after the values clause.

insert into table values ( opaqueinput )
insert into {table} values ({opaque input})

Opaque Input

Instead of passing name-value pairs, insert the entire input body as a string. ql.io will not read the content of this string. Instead, ql.io engine will treat it as an opaque input source and directly pass it to the api gateway.

Examples

Assume there is a table
create table insert.into
on insert post to 'http://localhost:3000/'
using bodyTemplate 'insert-into.json.mu' type 'application/json'
and the bodyTemplate
{
    {{#params}}
        "name" : "{{name}}"
    {{/params}}
}
on a server
    var server = http.createServer(function (req, res) {
        res.writeHead(200, {
            'Content-Type': 'application/json'
        });
    util.pump(req, res, function (e) {
    if(e) {
        console.log(e.stack || e);
    }
    res.end();
    });
});
The conventional way of inserting into this table, is to use name-value pairs.
insert into insert.into (name) values ("Tom")
Or, to ignore column-value pairs,
insert into insert.into values ("Tom")