Ballerina SQL Parameterized Queries

Question

I have the following function which retrieves a user from a SQL database.

isolated function getUser(string userId) returns UserData|error {
    io:println("[User ID]: ", userId);
    sql:ParameterizedQuery query = `SELECT * FROM user WHERE id = "${userId}"`;
    io:println("[Query]: ", query);
    return dbClient->queryRow(query);
}

When invoked, this function prints the following:

[User ID]: 91c7571c-5524-4f88-9e75-743fb94c3b48
[Query]: SELECT * FROM user WHERE id = "91c7571c-5524-4f88-9e75-743fb94c3b48"

But after the execution, it throws the following error:

Error while executing SQL query: SELECT * FROM user WHERE id = \" ? \". Parameter index out of range (1 > number of parameters, which is 0)

What am I doing wrong here?

Answer 1

Looks like the " around the parameter causes the issue. I was able to resolve it after removing the quotes around the id parameter.

The following line:

sql:ParameterizedQuery query = `SELECT * FROM user WHERE id = "${userId}"`;

Should be changed to:

sql:ParameterizedQuery query = `SELECT * FROM user WHERE id = ${userId}`;