ballerina.io SQL LIKE statement

user_14217317 0
Viewed 85

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

ballerina mariadb where-clause sql-like sql
1 Answers

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%"
);
user_106767160
Related