Open AndreyAlifanov opened 2 years ago
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.
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.
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?
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?
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.
@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.
@gjergjk71 it's good idea, I think.
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)
Pull request for parameters in query. https://github.com/ClickHouse/clickhouse-cpp/pull/394
Does this client support parameterized queries? I see that command line client can do this, but it uses it`s own implementation.