import ballerina/io;
import ballerinax/java.jdbc;
import ballerina/sql;

jdbc:Client dbClient = check new (url = "jdbc:h2:file:./master/orderdb",
                           user = "test", password = "test");

public function main() returns error? {
    // Uses a raw template to create `Orders` table.
    _ = check dbClient->execute(`CREATE TABLE IF NOT EXISTS Orders
                                 (orderId INTEGER NOT NULL IDENTITY,
                                  customerId INTEGER, noOfItems INTEGER,
                                  PRIMARY KEY (orderId))`);
    // Uses a raw template to insert values to `Orders` table.
    _ = check dbClient->execute(`INSERT INTO Orders (orderId, customerId,
                                noOfItems) VALUES (1, 1, 20)`);
    _ = check dbClient->execute(`INSERT INTO Orders (orderId, customerId,
                                noOfItems) VALUES (2, 1, 15)`);

    stream<record {| anydata...; |}, sql:Error?> strm = getOrders(1);
    record {|record {} value;|}|sql:Error? v = strm.next();
    while (v is record {|record {} value;|}) {
        record {} value = v.value;
        io:println(value);
        v = strm.next();
    }
}

function getOrders(int customerId)
    returns stream<record {| anydata...; |}, sql:Error?> {
    // In this raw template `customerId` variable is interpolated in the literal.
    return dbClient->query(`SELECT * FROM orders
                          WHERE customerId = ${customerId}`);
}

Raw Templates

A raw template is a backtick template without a tag. Exposes result of phase 1 without further processing. Raw template is evaluated by evaluating each expression and creating an object containing.

  • an array of the strings separated by insertions
  • an array of the results of expression evaluation and an array of strings separating


Important use case: SQL parameters.

import ballerina/io;
import ballerinax/java.jdbc;
import ballerina/sql;
jdbc:Client dbClient = check new (url = "jdbc:h2:file:./master/orderdb",
                           user = "test", password = "test");
public function main() returns error? {
    _ = check dbClient->execute(`CREATE TABLE IF NOT EXISTS Orders
                                 (orderId INTEGER NOT NULL IDENTITY,
                                  customerId INTEGER, noOfItems INTEGER,
                                  PRIMARY KEY (orderId))`);

Uses a raw template to create Orders table.

    _ = check dbClient->execute(`INSERT INTO Orders (orderId, customerId,
                                noOfItems) VALUES (1, 1, 20)`);
    _ = check dbClient->execute(`INSERT INTO Orders (orderId, customerId,
                                noOfItems) VALUES (2, 1, 15)`);

Uses a raw template to insert values to Orders table.

    stream<record {| anydata...; |}, sql:Error?> strm = getOrders(1);
    record {|record {} value;|}|sql:Error? v = strm.next();
    while (v is record {|record {} value;|}) {
        record {} value = v.value;
        io:println(value);
        v = strm.next();
    }
}
function getOrders(int customerId)
    returns stream<record {| anydata...; |}, sql:Error?> {
    return dbClient->query(`SELECT * FROM orders
                          WHERE customerId = ${customerId}`);
}

In this raw template customerId variable is interpolated in the literal.

bal run raw_templates.bal
{"ORDERID":1,"CUSTOMERID":1,"NOOFITEMS":20}
{"ORDERID":2,"CUSTOMERID":1,"NOOFITEMS":15}