Parameters in queries

[AndreyAlifanov]

Does this client support parameterized queries? I see that command line client can do this, but it uses it`s own implementation.

[Enmk]

Hi Andey! Thank you for asking, right now clickhouse-cpp doesn't support parametrized queries. You are wellcome to submit any of (or all) a PR/API/implementation draft.

[huotianyu]

Hi Andey! Thank you for asking, right now clickhouse-cpp doesn't support parametrized queries. You are wellcome to submit any of (or all) a PR/API/implementation draft.

when support parameterized queries? when I excuted client.Select, I can not deal data.

[Enmk]

Hi @huotianyu and sorry for a late reply. There are no solid date on this, seems like there is not enough interest from community.

To get things started, could you propose an API for such a feature?

[tsarchghs]

Hi @Enmk, @AndreyAlifanov.

I was thinking maybe we could introduce parameters in queries in format:

• {<name>:<data type>} eg: SELECT * FROM default.numbers WHERE id = {paramId:Int} AND name = {paramName:String} which would require either setting parameters on the sessions by executing: set "param_paramId" = 1; set "param_paramName" = 'one'; and/or following the prepared-statement concept:

QueryStatement prep_stmt = client.PrepareStatement("SELECT * FROM default.numbers WHERE id = {paramId:Int} AND name = {paramName:String}");
prep_stmt->setInt("paramId", 1);
prep_stmt->setString("paramName", "one");
prep_stmt.execute([] (const Block& block)
    {
        for (size_t i = 0; i < block.GetRowCount(); ++i) {
            std::cout << block[0]->As<ColumnUInt64>()->At(i) << " "
                      << block[1]->As<ColumnString>()->At(i) << "\n";
        }
    }
);

setInt, setString - QueryStatement methods can either translate into raw queries or be replaced as-is on the string?

[AndreyAlifanov]

Hi guys!

I think about it in terms of usual syntax for all DBMS, I know. Something like this: select * from my_table where id = ? and key > ?. Here ? are parameters, that one can substitute with real values while preparing query.

Proposal of @gjergjk71 is good too, but I would prefer standard SQL-syntax.

[tsarchghs]

@AndreyAlifanov maybe we can introduce parameterized queries with an implementation that makes use of std::variant?

std::variant<int, std::string> params[10] = { 2, "one" };
client.Execute("SELECT id, name FROM default.numbers WHERE id = ? AND name = ?", params);

Maybe @Enmk has another idea? Otherwise if this is a good addition, I could look more into it.

[AndreyAlifanov]

@gjergjk71 it's good idea, I think.

[Enmk]

I strongly believe that we need to utilize CH built-in mechanism for query parameters, but there are few prerequisite steps for that: https://github.com/ClickHouse/clickhouse-cpp/pull/260#issuecomment-1517792737

I see utility in bringing ? - based query parameters, but it is just a syntax sugar and might not benefit from any fancy features implemented on server side (like query caching). And it brings some problems with it: client would have to parse SQL query on client side to understand where to replace '?' with a value and where - not (strings, setting values, anything else)