How to execute a SQL statement with `Like` operator in Ballerina

Question

Need to execute a SQL statement with like operator in Ballerina.

Plain SQL sample statement:

SELECT * FROM albums where title like '%mon%';

How can we pass a variable value to such a query in Ballerina SQL client?

Answer 1

In Ballerina, you need to set the wildcard(%) in the value itself, not in the parameterized query.

import ballerina/io;
import ballerinax/mysql;
import ballerinax/mysql.driver as _;


// The `Album` record to load records from `albums` table.
type Album record {|
    string id;
    string title;
    string artist;
    float price;
|};

mysql:Client db = check new ("localhost", "root", "password", "MUSIC_STORE", 3306);

public function main() {
    string title = "%emonad%";
    stream<Album, error?> albumStream = db->query(`SELECT * FROM albums where title like ${title}`);

    // Process the stream and convert results to Album[] or return error.
    Album[]|error albums = from Album album in albumStream
        select album;
    io:println(albums);

}