ballerina.io SQL LIKE statement

Question

i am currently trying to execute the following SQL statement in ballerina.io against a MariaDB.

Plan SQL:

select * FROM testDB where test LIKE '%BA%';

I get a result set with all data.

ballerina.io:

var selectRet = testDB->select("select * FROM testDB where test LIKE '%?%'", testREC, "BA");

I get an empty result set.

versions: ballerina --version
jBallerina 1.1.2 Language specification 2019R3 Ballerina tool 0.8.0

Is it possible to make a SQL statement with LIKE in ballerina.io?

Many greetings, Martin

score 1 · Accepted Answer · answered Sep 03 '20 at 20:00

The parameter is passed to the query as a separate literal string, not as some kind of template variable. To surround it with wildcards, you need to use concat() in the query:

var selectRet = testDB->select(
    "select * FROM testDB where test like concat('%', ?, '%')", 
    testREC, 
    "BA"
);

Or just concatenate the wildcards in your code (this looks a bit cleaner to me):

var selectRet = testDB->select(
    "select * FROM testDB where test like ?", 
    testREC, 
    "%BA%"
);

ballerina.io SQL LIKE statement

1 Answers1