Swan Lake Beta2 is here.
import ballerina/io;
import ballerinax/mysql;
import ballerina/sql;

// Defines a record to load the query result schema as shown below in the
// 'getDataWithTypedQuery' function. In this example, all columns of the 
// customer table will be loaded. Therefore, the `Customer` record will be 
// created with all the columns. The column name of the result and the 
// defined field name of the record will be matched case insensitively.
type Customer record {|
    int customerId;
    string lastName;
    string firstName;
    int registrationId;
    float creditLimit;
    string country;
|};

public function main() returns error? {
    // Runs the prerequisite setup for the example.
    check beforeExample();

    // Initializes the MySQL client.
    mysql:Client mysqlClient = check new (user = "root", 
            password = "Test@123", database = "MYSQL_BBE");

    // Select the rows in the database table via the query remote operation.
    // The result is returned as a stream and the elements of the stream can
    // be either a record or an error. The name and type of the attributes 
    // within the record from the `resultStream` will be automatically 
    // identified based on the column name and type of the query result.
    stream<record {}, error?> resultStream =
            mysqlClient->query(`SELECT * FROM Customers`);

    // If there is any error during the execution of the SQL query or
    // iteration of the result stream, the result stream will terminate and
    // return the error.
    error? e = resultStream.forEach(function(record {} result) {
        io:println("Full Customer details: ", result);
    });

    // The result of the count operation is provided as a record stream.
    stream<record {}, error?> resultStream2 =
            mysqlClient->query(`SELECT COUNT(*) AS total FROM Customers`);

    // Since the above count query will return only a single row,
    // the `next()` operation is sufficient to retrieve the data.
    record {|record {} value;|}|error? result = resultStream2.next();
    // Checks the result and retrieves the value for the total.
    if result is record {|record {} value;|} {
        io:println("Total rows in customer table : ", result.value["total"]);
    }

    // In general cases, the stream will be closed automatically
    // when the stream is fully consumed or any error is encountered.
    // However, in case if the stream is not fully consumed, the stream
    // should be closed specifically.
    error? er = resultStream2.close();

    // If a `Customer` stream type is defined when calling the query method,
    // The result is returned as a `Customer` record stream and the elements
    // of the stream can be either a `Customer` record or an error.
    stream<Customer, error?> customerStream =
        mysqlClient->query(`SELECT * FROM Customers`);

    // Iterates the customer stream.
    error? e2 = customerStream.forEach(function(Customer customer) {
        io:println("Full Customer details: ", customer);
    });

    // Performs the cleanup after the example.
    check afterExample(mysqlClient);
}

// Initializes the database as a prerequisite to the example.
function beforeExample() returns sql:Error? {
    mysql:Client mysqlClient = check new (user = "root", password = "Test@123");

    // Creates a database.
    sql:ExecutionResult result = 
        check mysqlClient->execute(`CREATE DATABASE MYSQL_BBE`);

    // Creates a table in the database.
    result = check mysqlClient->execute(`CREATE TABLE MYSQL_BBE.Customers
            (customerId INTEGER NOT NULL AUTO_INCREMENT, firstName
            VARCHAR(300), lastName  VARCHAR(300), registrationID INTEGER,
            creditLimit DOUBLE, country  VARCHAR(300),
            PRIMARY KEY (customerId))`);

    // Adds the records to the newly-created table.
    result = check mysqlClient->execute(`INSERT INTO MYSQL_BBE.Customers
            (firstName, lastName, registrationID,creditLimit,country) VALUES
            ('Peter','Stuart', 1, 5000.75, 'USA')`);
    result = check mysqlClient->execute(`INSERT INTO MYSQL_BBE.Customers
            (firstName, lastName, registrationID,creditLimit,country) VALUES
            ('Dan', 'Brown', 2, 10000, 'UK')`);

    check mysqlClient.close();
}

// Cleans up the database after running the example.
function afterExample(mysql:Client mysqlClient) returns sql:Error? {
    // Cleans the database.
    sql:ExecutionResult result = 
            check mysqlClient->execute(`DROP DATABASE MYSQL_BBE`);
    // Closes the MySQL client.
    check mysqlClient.close();
}

Simple Query

This BBE demonstrates how to use the MySQL client select query operations with the stream return type. Note that the MySQL database driver JAR should be defined in the Ballerina.toml file as a dependency. For a sample configuration and more information on the underlying module, see the MySQL module. The MySQL connector uses database properties from MySQL version 8.0.13 onwards. Therefore, it is recommended to use a MySQL driver version greater than 8.0.13.

import ballerina/io;
import ballerinax/mysql;
import ballerina/sql;
type Customer record {|
    int customerId;
    string lastName;
    string firstName;
    int registrationId;
    float creditLimit;
    string country;
|};

Defines a record to load the query result schema as shown below in the ‘getDataWithTypedQuery’ function. In this example, all columns of the customer table will be loaded. Therefore, the Customer record will be created with all the columns. The column name of the result and the defined field name of the record will be matched case insensitively.

public function main() returns error? {
    check beforeExample();

Runs the prerequisite setup for the example.

    mysql:Client mysqlClient = check new (user = "root", 
            password = "Test@123", database = "MYSQL_BBE");

Initializes the MySQL client.

    stream<record {}, error?> resultStream =
            mysqlClient->query(`SELECT * FROM Customers`);

Select the rows in the database table via the query remote operation. The result is returned as a stream and the elements of the stream can be either a record or an error. The name and type of the attributes within the record from the resultStream will be automatically identified based on the column name and type of the query result.

    error? e = resultStream.forEach(function(record {} result) {
        io:println("Full Customer details: ", result);
    });

If there is any error during the execution of the SQL query or iteration of the result stream, the result stream will terminate and return the error.

    stream<record {}, error?> resultStream2 =
            mysqlClient->query(`SELECT COUNT(*) AS total FROM Customers`);

The result of the count operation is provided as a record stream.

    record {|record {} value;|}|error? result = resultStream2.next();

Since the above count query will return only a single row, the next() operation is sufficient to retrieve the data.

    if result is record {|record {} value;|} {
        io:println("Total rows in customer table : ", result.value["total"]);
    }

Checks the result and retrieves the value for the total.

    error? er = resultStream2.close();

In general cases, the stream will be closed automatically when the stream is fully consumed or any error is encountered. However, in case if the stream is not fully consumed, the stream should be closed specifically.

    stream<Customer, error?> customerStream =
        mysqlClient->query(`SELECT * FROM Customers`);

If a Customer stream type is defined when calling the query method, The result is returned as a Customer record stream and the elements of the stream can be either a Customer record or an error.

    error? e2 = customerStream.forEach(function(Customer customer) {
        io:println("Full Customer details: ", customer);
    });

Iterates the customer stream.

    check afterExample(mysqlClient);
}

Performs the cleanup after the example.

function beforeExample() returns sql:Error? {
    mysql:Client mysqlClient = check new (user = "root", password = "Test@123");

Initializes the database as a prerequisite to the example.

    sql:ExecutionResult result = 
        check mysqlClient->execute(`CREATE DATABASE MYSQL_BBE`);

Creates a database.

    result = check mysqlClient->execute(`CREATE TABLE MYSQL_BBE.Customers
            (customerId INTEGER NOT NULL AUTO_INCREMENT, firstName
            VARCHAR(300), lastName  VARCHAR(300), registrationID INTEGER,
            creditLimit DOUBLE, country  VARCHAR(300),
            PRIMARY KEY (customerId))`);

Creates a table in the database.

    result = check mysqlClient->execute(`INSERT INTO MYSQL_BBE.Customers
            (firstName, lastName, registrationID,creditLimit,country) VALUES
            ('Peter','Stuart', 1, 5000.75, 'USA')`);
    result = check mysqlClient->execute(`INSERT INTO MYSQL_BBE.Customers
            (firstName, lastName, registrationID,creditLimit,country) VALUES
            ('Dan', 'Brown', 2, 10000, 'UK')`);

Adds the records to the newly-created table.

    check mysqlClient.close();
}
function afterExample(mysql:Client mysqlClient) returns sql:Error? {

Cleans up the database after running the example.

    sql:ExecutionResult result = 
            check mysqlClient->execute(`DROP DATABASE MYSQL_BBE`);

Cleans the database.

    check mysqlClient.close();
}

Closes the MySQL client.

# Create a Ballerina project.
# Copy the example to the project and add relevant database driver jar details to the `Ballerina.toml` file.
# Execute the command below to build and run the project.
bal run
Full Customer details: {"customerId":1,"firstName":"Peter","lastName":"Stuart","registrationID":1,"creditLimit":5000.75,"country":"USA"}
Full Customer details: {"customerId":2,"firstName":"Dan","lastName":"Brown","registrationID":2,"creditLimit":10000.0,"country":"UK"}
Total rows in customer table : 2
Full Customer details: {"customerId":1,"firstName":"Peter","lastName":"Stuart","registrationId":1,"creditLimit":5000.75,"country":"USA"}
Full Customer details: {"customerId":2,"firstName":"Dan","lastName":"Brown","registrationId":2,"creditLimit":10000.0,"country":"UK"}
import ballerina/io;
import ballerinax/mysql;
import ballerina/sql;

// Defines a record to load the query result schema as shown below in the
// 'getDataWithTypedQuery' function. In this example, all columns of the 
// customer table will be loaded. Therefore, the `Customer` record will be 
// created with all the columns. The column name of the result and the 
// defined field name of the record will be matched case insensitively.
type Customer record {|
    int customerId;
    string lastName;
    string firstName;
    int registrationId;
    float creditLimit;
    string country;
|};

public function main() returns error? {
    // Runs the prerequisite setup for the example.
    check beforeExample();

    // Initializes the MySQL client.
    mysql:Client mysqlClient = check new (user = "root", 
            password = "Test@123", database = "MYSQL_BBE");

    // Select the rows in the database table via the query remote operation.
    // The result is returned as a stream and the elements of the stream can
    // be either a record or an error. The name and type of the attributes 
    // within the record from the `resultStream` will be automatically 
    // identified based on the column name and type of the query result.
    stream<record {}, error?> resultStream =
            mysqlClient->query(`SELECT * FROM Customers`);

    // If there is any error during the execution of the SQL query or
    // iteration of the result stream, the result stream will terminate and
    // return the error.
    error? e = resultStream.forEach(function(record {} result) {
        io:println("Full Customer details: ", result);
    });

    // The result of the count operation is provided as a record stream.
    stream<record {}, error?> resultStream2 =
            mysqlClient->query(`SELECT COUNT(*) AS total FROM Customers`);

    // Since the above count query will return only a single row,
    // the `next()` operation is sufficient to retrieve the data.
    record {|record {} value;|}|error? result = resultStream2.next();
    // Checks the result and retrieves the value for the total.
    if result is record {|record {} value;|} {
        io:println("Total rows in customer table : ", result.value["total"]);
    }

    // In general cases, the stream will be closed automatically
    // when the stream is fully consumed or any error is encountered.
    // However, in case if the stream is not fully consumed, the stream
    // should be closed specifically.
    error? er = resultStream2.close();

    // If a `Customer` stream type is defined when calling the query method,
    // The result is returned as a `Customer` record stream and the elements
    // of the stream can be either a `Customer` record or an error.
    stream<Customer, error?> customerStream =
        mysqlClient->query(`SELECT * FROM Customers`);

    // Iterates the customer stream.
    error? e2 = customerStream.forEach(function(Customer customer) {
        io:println("Full Customer details: ", customer);
    });

    // Performs the cleanup after the example.
    check afterExample(mysqlClient);
}

// Initializes the database as a prerequisite to the example.
function beforeExample() returns sql:Error? {
    mysql:Client mysqlClient = check new (user = "root", password = "Test@123");

    // Creates a database.
    sql:ExecutionResult result = 
        check mysqlClient->execute(`CREATE DATABASE MYSQL_BBE`);

    // Creates a table in the database.
    result = check mysqlClient->execute(`CREATE TABLE MYSQL_BBE.Customers
            (customerId INTEGER NOT NULL AUTO_INCREMENT, firstName
            VARCHAR(300), lastName  VARCHAR(300), registrationID INTEGER,
            creditLimit DOUBLE, country  VARCHAR(300),
            PRIMARY KEY (customerId))`);

    // Adds the records to the newly-created table.
    result = check mysqlClient->execute(`INSERT INTO MYSQL_BBE.Customers
            (firstName, lastName, registrationID,creditLimit,country) VALUES
            ('Peter','Stuart', 1, 5000.75, 'USA')`);
    result = check mysqlClient->execute(`INSERT INTO MYSQL_BBE.Customers
            (firstName, lastName, registrationID,creditLimit,country) VALUES
            ('Dan', 'Brown', 2, 10000, 'UK')`);

    check mysqlClient.close();
}

// Cleans up the database after running the example.
function afterExample(mysql:Client mysqlClient) returns sql:Error? {
    // Cleans the database.
    sql:ExecutionResult result = 
            check mysqlClient->execute(`DROP DATABASE MYSQL_BBE`);
    // Closes the MySQL client.
    check mysqlClient.close();
}

Simple Query

This BBE demonstrates how to use the MySQL client select query operations with the stream return type. Note that the MySQL database driver JAR should be defined in the Ballerina.toml file as a dependency. For a sample configuration and more information on the underlying module, see the MySQL module. The MySQL connector uses database properties from MySQL version 8.0.13 onwards. Therefore, it is recommended to use a MySQL driver version greater than 8.0.13.

import ballerina/io;
import ballerinax/mysql;
import ballerina/sql;
type Customer record {|
    int customerId;
    string lastName;
    string firstName;
    int registrationId;
    float creditLimit;
    string country;
|};

Defines a record to load the query result schema as shown below in the ‘getDataWithTypedQuery’ function. In this example, all columns of the customer table will be loaded. Therefore, the Customer record will be created with all the columns. The column name of the result and the defined field name of the record will be matched case insensitively.

public function main() returns error? {
    check beforeExample();

Runs the prerequisite setup for the example.

    mysql:Client mysqlClient = check new (user = "root", 
            password = "Test@123", database = "MYSQL_BBE");

Initializes the MySQL client.

    stream<record {}, error?> resultStream =
            mysqlClient->query(`SELECT * FROM Customers`);

Select the rows in the database table via the query remote operation. The result is returned as a stream and the elements of the stream can be either a record or an error. The name and type of the attributes within the record from the resultStream will be automatically identified based on the column name and type of the query result.

    error? e = resultStream.forEach(function(record {} result) {
        io:println("Full Customer details: ", result);
    });

If there is any error during the execution of the SQL query or iteration of the result stream, the result stream will terminate and return the error.

    stream<record {}, error?> resultStream2 =
            mysqlClient->query(`SELECT COUNT(*) AS total FROM Customers`);

The result of the count operation is provided as a record stream.

    record {|record {} value;|}|error? result = resultStream2.next();

Since the above count query will return only a single row, the next() operation is sufficient to retrieve the data.

    if result is record {|record {} value;|} {
        io:println("Total rows in customer table : ", result.value["total"]);
    }

Checks the result and retrieves the value for the total.

    error? er = resultStream2.close();

In general cases, the stream will be closed automatically when the stream is fully consumed or any error is encountered. However, in case if the stream is not fully consumed, the stream should be closed specifically.

    stream<Customer, error?> customerStream =
        mysqlClient->query(`SELECT * FROM Customers`);

If a Customer stream type is defined when calling the query method, The result is returned as a Customer record stream and the elements of the stream can be either a Customer record or an error.

    error? e2 = customerStream.forEach(function(Customer customer) {
        io:println("Full Customer details: ", customer);
    });

Iterates the customer stream.

    check afterExample(mysqlClient);
}

Performs the cleanup after the example.

function beforeExample() returns sql:Error? {
    mysql:Client mysqlClient = check new (user = "root", password = "Test@123");

Initializes the database as a prerequisite to the example.

    sql:ExecutionResult result = 
        check mysqlClient->execute(`CREATE DATABASE MYSQL_BBE`);

Creates a database.

    result = check mysqlClient->execute(`CREATE TABLE MYSQL_BBE.Customers
            (customerId INTEGER NOT NULL AUTO_INCREMENT, firstName
            VARCHAR(300), lastName  VARCHAR(300), registrationID INTEGER,
            creditLimit DOUBLE, country  VARCHAR(300),
            PRIMARY KEY (customerId))`);

Creates a table in the database.

    result = check mysqlClient->execute(`INSERT INTO MYSQL_BBE.Customers
            (firstName, lastName, registrationID,creditLimit,country) VALUES
            ('Peter','Stuart', 1, 5000.75, 'USA')`);
    result = check mysqlClient->execute(`INSERT INTO MYSQL_BBE.Customers
            (firstName, lastName, registrationID,creditLimit,country) VALUES
            ('Dan', 'Brown', 2, 10000, 'UK')`);

Adds the records to the newly-created table.

    check mysqlClient.close();
}
function afterExample(mysql:Client mysqlClient) returns sql:Error? {

Cleans up the database after running the example.

    sql:ExecutionResult result = 
            check mysqlClient->execute(`DROP DATABASE MYSQL_BBE`);

Cleans the database.

    check mysqlClient.close();
}

Closes the MySQL client.

# Create a Ballerina project.
# Copy the example to the project and add relevant database driver jar details to the `Ballerina.toml` file.
# Execute the command below to build and run the project.
bal run
Full Customer details: {"customerId":1,"firstName":"Peter","lastName":"Stuart","registrationID":1,"creditLimit":5000.75,"country":"USA"}
Full Customer details: {"customerId":2,"firstName":"Dan","lastName":"Brown","registrationID":2,"creditLimit":10000.0,"country":"UK"}
Total rows in customer table : 2
Full Customer details: {"customerId":1,"firstName":"Peter","lastName":"Stuart","registrationId":1,"creditLimit":5000.75,"country":"USA"}
Full Customer details: {"customerId":2,"firstName":"Dan","lastName":"Brown","registrationId":2,"creditLimit":10000.0,"country":"UK"}

In the creation of Ballerina, we were inspired by so many technologies. Thank you to all that have come before us (and forgive us if we missed one): Java, Go, C, C++, D, Rust, Haskell, Kotlin, Dart, TypeScript, JavaScript, Python, Perl, Flow, Swift, Elm, RelaxNG, NPM, Crates, Maven, Gradle, Kubernetes, Docker, Envoy, Markdown, GitHub and WSO2.

Cookie Policy

This website uses cookies so that we can provide you with the best user experience. Read our Cookie Policy to find out more.

If you wish to disable cookies you can do so from your browser.

I Understand