Requests

A request is a JSON structure that is passed via a POST HTTP call to ws4sqlite, using the port specified when the server application.

First and foremost, the database we connect to is specified in the URL of the POST call. It is something like this:

http://localhost:12321/db2

That db2 part is the database ID, and must match the id of a database, defined in the .

Second, and very important, the request must contain the header:

Content-type: application/json

This is a JSON that exemplifies all possible elements of a request.

{
    "resultFormat": "map",
    "credentials": {
        "user": "myUser1",
        "password": "myCoolPassword"
    },
    "transaction": [
        {
            "query": "SELECT * FROM TEMP"
        },
        {
            "query": "SELECT * FROM TEMP WHERE ID = ?",
            "values": [ 1 ] // Positional parameters
        },
        {
            "statement": "INSERT INTO TEMP (ID, VAL) VALUES (0, 'ZERO')"
        },
        {
            "noFail": true,
            "statement": "INSERT INTO TEMP (ID, VAL) VALUES (:id, :val)",
            "values": { "id": 1, "val": "a" } // Named parameters
        },
        {
            "statement": "#Q2",
            "valuesBatch": [
                { "id": 2, "val": "b" },
                { "id": 3, "val": "c" }
            ]
        }
    ]
}

Let's go through it.

Result Format

Line 2; string; map or list (case insensitive); default map

Since 0.16

This is the format for result sets, in the response. By default, it returns an (ordered) map with the results, but it can return a list of them, too.

Authentication Credentials

Lines 3-6; object

List of Queries/Statements

Line 7; list of objects; mandatory

Must be not empty. The list of the queries or statements that will actually be performed on the database, with their own parameters.

SQL Statements to Execute

Lines 9, 12, 16, 20; string; mandatory one of query or statement

The actual SQL to execute.

Specifying it as query means that a result set is expected (typically, SELECT queries or queries with a RETURNING clause).

Specifying a statement will not return a result set, but a count of affected records.

Stored Query Reference

Line 24; string; mandatory as the above

A query or a statement (see above) can consist of a reference to a Stored Query. They are prepended by a #. An error will occour if the S.Q. with an ID equal to the part after the # was not defined for this database.

Parameter Values for the Query/Statement

Lines 13, 21; object

Since 0.16, there are two forms of parameters:

Positional, as in Line 13; in the SQL you'll want to use the ? form for placeholders, and then specify the parameters, in order, in a list;
Named, as in line 21; in the SQL use :name or @name, and specify an object with their value. May help reduce clutter.

Before v0.16, the parameters were always named.

What happens if some parameter values aren't defined in the values object, in its named form? If there are less parameter values than expected, it will give an error. If they are correct in number, but some parameter names don't match, the missing parameters will be assigned a value of null.

Batch Parameter Values for a Statement

Lines 25..28; list of objects

Only for statements, more than one set of parameter values can be specified; the statement will be applied to them in a batch (by preparing the statement).

NoFail: Don't Fail when Errors Occour

Line 29; Boolean

PreviousStored Statements NextResponses

Last updated 1 year ago

{ "resultFormat": "map", "credentials": { "user": "myUser1", "password": "myCoolPassword" }, "transaction": [ { "query": "SELECT * FROM TEMP" }, { "query": "SELECT * FROM TEMP WHERE ID = ?", "values": [ 1 ] // Positional parameters }, { "statement": "INSERT INTO TEMP (ID, VAL) VALUES (0, 'ZERO')" }, { "noFail": true, "statement": "INSERT INTO TEMP (ID, VAL) VALUES (:id, :val)", "values": { "id": 1, "val": "a" } // Named parameters }, { "statement": "#Q2", "valuesBatch": [ { "id": 2, "val": "b" }, { "id": 3, "val": "c" } ] } ] }