Improve sql:ParameterizedQuery

[kalaiyarasiganeshalingam]

Description: $Subject

Steps to reproduce:

public function main() returns sql:Error? {
    mysql:Client dbClient = check new (host = "localhost", user = "user", password = "password", database = "db", port = 3306);
    string tablename = "test";
    _ = check dbClient->execute("DROP TABLE IF EXISTS " + tablename);
    _ = check dbClient->execute("CREATE TABLE " + tablename +
                                "(Id INTEGER NOT NULL AUTO_INCREMENT, Name  VARCHAR(300), PRIMARY KEY(Id))");
    _ = check dbClient->execute("INSERT INTO " + tablename + "(Id, Name) VALUES (1,'John')");
    _ = check dbClient->execute("INSERT INTO " + tablename + "(Id, Name) VALUES (2,'Sam')");
    int[] ids = [1, 2];
    sql:ParameterizedQuery query = `SELECT * FROM tablename WHERE Id IN (${ids})`;
    stream<record {}, sql:Error?> resultStream = dbClient->query(query);
    sql:Error? e = resultStream.forEach(function(record {} result) {
        io:println("Full Customer details: ", result);
    });
    io:println(e); 
}

Error: error DatabaseError ("Error while executing SQL query: SELECT * FROM tablename WHERE Id IN ( ? ). Database Error Occurred.",errorCode=0,sqlState=null)

Affected Versions: 0.6.0-beta.2

[niveathika]

This should come from the underlying RawTemplate construct. @pubudu91 WDYT?

[pubudu91]

@niveathika What's the exact issue here? If it's about passing arrays to a raw template, that's already supported. e.g.,

type Foo object {
    *object:RawTemplate;
};

// usage
int[] arr = [10, 20];
Foo f = `Test ${arr}`;

or even the following works:

type Foo object {
    *object:RawTemplate;
    public (readonly & string[]) strings;
    public anydata[][] insertions;
};

[kalaiyarasiganeshalingam]

Ballerina array types are used to get SQL array type input. If we use array type to get input for the IN operator value, It will become the parameterized query more complicate. Therefore, we have improved the error message now.

[niveathika]

@pubudu91 It was my mistake, To describe the issue a bit more.

As of now, the raw template supports the 1:1 mapping of parameters to the variable. i.e

// usage
int[] arr = [10, 20];
RawTemplate template = `Test ${arr} type`;

//Desugurs
RawTemplate template = {
    strings: ["Test", " type" ],
    insertions: [[10, 20]]
};

The use case is to support 1:n mapping of the variable in case the variable consists of multiple elements like an array with a default divisor, for instance

// usage
int[] arr = [10, 20];
RawTemplate template = `Test ${arr...} type`;

//Desugurs
RawTemplate template = {
    strings: ["Test", ",", " type" ],
    insertions: [10, 20]
};

This is still not supported through the spec and needs to validate this use-case and the syntax we can use.

Like @kalaiyarasiganeshalingam mentioned for now we can only improve the error message saying that this is not supported.