import ballerina/sql;
import ballerina/io;
type Employee {
    int id;
    string name;
    float salary;
    boolean status;
    string birthdate;
    string birthtime;
    string updated;
};function main (string[] args) {    endpoint sql:Client testDB {
        database: sql:DB_MYSQL,
        host: "localhost",
        port: 3306,
        name: "testdb",
        username: "root",
        password: "root",
        options: {maximumPoolSize:5}
    };    int count;
    table dt;
    int ret;
    var returnValue = testDB -> update("CREATE TABLE EMPLOYEE (id INT,name
        VARCHAR(25),salary DOUBLE,status BOOLEAN,birthdate DATE,birthtime TIME,
        updated TIMESTAMP)", null);    match returnValue {
        int val => {
            count = val;
        }
        error e => io:println("Error in executing CREATE TABLE EMPLOYEE");
    }    returnValue = testDB -> update("INSERT INTO EMPLOYEE VALUES(1, 'John', 1050.50, false,
        '1990-12-31', '11:30:45', '2007-05-23 09:15:28')", null);    match returnValue {
        int val => {
            count = val;
        }
        error e => io:println("Error in executing INSERT INTO EMPLOYEE");
    }    returnValue = testDB -> update("INSERT INTO EMPLOYEE VALUES(2, 'Anne', 4060.50, true,
        '1999-12-31', '13:40:24', '2017-05-23 09:15:28')", null);    match returnValue {
        int val => {
          count = val;
        }
        error e => io:println("Error in executing INSERT INTO EMPLOYEE");
    }
    var returnVal = testDB -> select("SELECT * from EMPLOYEE", null, typeof Employee);    match returnVal {
        table val => {
            dt = val;
        }
        error e => io:println("Error in executing SELECT * from EMPLOYEE");
    }
    while (dt.hasNext()) {
        var returnedNextRec = <Employee>dt.getNext();
        match returnedNextRec {
            Employee rs => {
                io:println("Employee:"+ rs.id + "|" + rs.name +  "|" + rs.salary +
                "|" + rs.status + "|" + rs.birthdate + "|"
                 + rs.birthtime + "|" + rs.updated);
            }
            error e => io:println("Error in retrieving next record");
        }
    }
    var returnVal2 = testDB -> select("SELECT id,name FROM EMPLOYEE", null, null);
    match returnVal2 {
        table val => {
            dt = val;
        }
        error e => io:println("Error in executing SELECT id,name FROM EMPLOYEE");
    }    var jsonRes = <json>dt;
    io:println(jsonRes);
    var returnVal3 = testDB -> select("SELECT id,name FROM EMPLOYEE", null, null);    match returnVal3 {
        table val => {
            dt = val;
        }
        error e => io:println("Error in executing SELECT id,name FROM EMPLOYEE");
    }    var xmlRes = <xml>dt;
    io:println(xmlRes);
    var returnVal4 = testDB -> update("DROP TABLE EMPLOYEE", null);
    match returnVal4 {
        int val => {
            ret = val;
        }
        error e => io:println("Error in executing DROP TABLE EMPLOYEE");
    }
    io:println("Table drop status:" + ret);
    var onConnectionClose = testDB -> close();
    match onConnectionClose {
        error e => io:println("Error in DB Connection close");
        any | () => {
            io:println("DB Connection closed successfully.");
        }
    }
}

Table with SQL Connector

The table type is used to hold tabular data, such as those returned from an SQL database query and provides cursor based access to the data.This sample use MySQL DB and before running the sample copy the MySQL JDBC driver to the BALLERINA_HOME/bre/lib folder and change the DB connection properties as required.

import ballerina/sql;
import ballerina/io;
type Employee {
    int id;
    string name;
    float salary;
    boolean status;
    string birthdate;
    string birthtime;
    string updated;
};

This is the Employee struct. The field names of this should match column names of the table. The field types should match with the sql types.

function main (string[] args) {
    endpoint sql:Client testDB {
        database: sql:DB_MYSQL,
        host: "localhost",
        port: 3306,
        name: "testdb",
        username: "root",
        password: "root",
        options: {maximumPoolSize:5}
    };
    int count;
    table dt;
    int ret;
    var returnValue = testDB -> update("CREATE TABLE EMPLOYEE (id INT,name
        VARCHAR(25),salary DOUBLE,status BOOLEAN,birthdate DATE,birthtime TIME,
        updated TIMESTAMP)", null);

Create table named EMPLOYEE and populate sample data.

    match returnValue {
        int val => {
            count = val;
        }
        error e => io:println("Error in executing CREATE TABLE EMPLOYEE");
    }
    returnValue = testDB -> update("INSERT INTO EMPLOYEE VALUES(1, 'John', 1050.50, false,
        '1990-12-31', '11:30:45', '2007-05-23 09:15:28')", null);
    match returnValue {
        int val => {
            count = val;
        }
        error e => io:println("Error in executing INSERT INTO EMPLOYEE");
    }
    returnValue = testDB -> update("INSERT INTO EMPLOYEE VALUES(2, 'Anne', 4060.50, true,
        '1999-12-31', '13:40:24', '2017-05-23 09:15:28')", null);
    match returnValue {
        int val => {
          count = val;
        }
        error e => io:println("Error in executing INSERT INTO EMPLOYEE");
    }
    var returnVal = testDB -> select("SELECT * from EMPLOYEE", null, typeof Employee);

Query the table using SQL connector select action. Either select or call action can return a table.

    match returnVal {
        table val => {
            dt = val;
        }
        error e => io:println("Error in executing SELECT * from EMPLOYEE");
    }
    while (dt.hasNext()) {
        var returnedNextRec = <Employee>dt.getNext();
        match returnedNextRec {
            Employee rs => {
                io:println("Employee:"+ rs.id + "|" + rs.name +  "|" + rs.salary +
                "|" + rs.status + "|" + rs.birthdate + "|"
                 + rs.birthtime + "|" + rs.updated);
            }
            error e => io:println("Error in retrieving next record");
        }
    }

Iterate through the result until hasNext() become false and retrieve the data struct corresponding to each row.

    var returnVal2 = testDB -> select("SELECT id,name FROM EMPLOYEE", null, null);
    match returnVal2 {
        table val => {
            dt = val;
        }
        error e => io:println("Error in executing SELECT id,name FROM EMPLOYEE");
    }

The table to json/xml conversion is resulted in streamed data. With the data streaming functionality, when a service client makes a request, the result is streamed to the service client rather than building the full result in the server and returning it. This allows virtually unlimited payload sizes in the result, and the response is instantaneous to the client.
Convert a table to JSON.

    var jsonRes = <json>dt;
    io:println(jsonRes);
    var returnVal3 = testDB -> select("SELECT id,name FROM EMPLOYEE", null, null);

Convert a table to XML.

    match returnVal3 {
        table val => {
            dt = val;
        }
        error e => io:println("Error in executing SELECT id,name FROM EMPLOYEE");
    }
    var xmlRes = <xml>dt;
    io:println(xmlRes);
    var returnVal4 = testDB -> update("DROP TABLE EMPLOYEE", null);
    match returnVal4 {
        int val => {
            ret = val;
        }
        error e => io:println("Error in executing DROP TABLE EMPLOYEE");
    }
    io:println("Table drop status:" + ret);

Drop the EMPLOYEE table.

    var onConnectionClose = testDB -> close();
    match onConnectionClose {
        error e => io:println("Error in DB Connection close");
        any | () => {
            io:println("DB Connection closed successfully.");
        }
    }
}

Finally close the DB connection.

$ ballerina run table-with-sql-connector.bal
Employee:1|John|1050.5|false|1990-12-31+05:30|11:30:45.000+05:30|2007-05-23T09:15:28.000+05:30
Employee:2|Anne|4060.5|true|1999-12-31+06:00|13:40:24.000+05:30|2017-05-23T09:15:28.000+05:30
[{"id":1,"name":"John"},{"id":2,"name":"Anne"}]
<results><result><id>1</id><name>John</name></result><result><id>2</id><name>Anne</name></result></results>
Table drop status:0
DB Connection closed successfully.