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} ({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 {"{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 :
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 ({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 tablecreate 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 ")