Can't add parameters to a query when httpParametersSupported

[darkwanderer]

Can't add parameters to a query when httpParametersSupported

[DarkWanderer]

readthedocs.io is not the official CH documentation, the official one is here: https://clickhouse.tech/docs. RowBinaryWithNamesAndTypes is described here

Your query has string enclosed in single quotes, this is incorrect according to official docs - it should be without quotes, e.g.:
select {val:String}
http://<clickhouse.server>/?query=select%20{val:String}%20format%20JSONCompact&param_val=str

Basically it seems there's a bug with "legacy" HTTP parameter passing, which allows a query with single quotes to work. I'll fix it in version 2.0.0, thanks for reporting

[ozeno]

Also, when I try to add a parameter to the where condition the SubstituteParameters method creates the correct query but
when httpParametersSupported is true it returns empty results as well, like so:

command.CommandText = "select col from tbl where {p1:String} like {p2:String}";
command.AddParameter("p1", "String", "col2");
command.AddParameter("p2", "String", "some_string");

About the single quotes, when I left them out SubstituteParameters didn't enclose the parameters in single quotes that's why I kept them within the query.

[DarkWanderer]

By "when httpParametersSupported is true" do you mean that you force it to true? It won't work that way as the server will not support the parameters in that format. Lack of enclosing string with quotes is the bug I'm talking about, yes.

[DarkWanderer]

Actually, something does not make sense here. If your server version is 20.3.11.97, you should not be hitting the "parameters compatibility" mode.

I'm trying to reproduce the issue right now and cannot:

            command.CommandText = "select count(*) from system.tables where name = {p1:String}";
            command.AddParameter("p1", "tables");
            Console.WriteLine(await command.ExecuteScalarAsync());

This returns 1, as expected

            command.CommandText = "select count(*) from system.tables where name = '{p1:String}'";
            command.AddParameter("p1", "tables");
            Console.WriteLine(await command.ExecuteScalarAsync());

This returns 0, because of single quotes, again, as expected

Can you share, please:

• Which version of ClickHouse.Client you use?
• What is the code you're using to reproduce it?

[ozeno]

I'm using the 20.3.11.97 version of the ClickHouse and here's what happens as a quick recap:

When I run these lines of code I get empty results (with single quotes):

var command = connection.CreateCommand();
command.CommandText = "select col from tbl where col2 = '{p1:String}'";
command.AddParameter("p1", "some_string");

But it works when I remove the quotes as so:

command.CommandText = "select col from tbl where col2 = {p1:String}";

And stops working again when I have two sides of the where clause as a parameter:

command.CommandText = "select col from tbl where {p1:String} like {p2:String}";
command.AddParameter("p1", "col2");
command.AddParameter("p2", "some_string");

Because it interprets the column name as a string as well, but If I were able to specify the quotes myself (as I can when httpParametersSupported = false) for the p2 only, it would work just fine or if there were another way to specify that the p1 is a column name and not a regular string.

For the question about httpParametersSupported, yes I forced it to be false by changing the value in runtime.

[DarkWanderer]

Well, first of all, I asked about what version of CliclHouse.Client you use, not about the server. Second, contrary to what you said, the code in your original question works, now you're providing some other scenario - it's not a recap. Third, what you're trying to do is not supported by ADO at all. If you need dynamic SQL, you can generate it yourself and then feed to ClickHouse.Client:
sql = "select col from tbl where " + SomeColumn + " = {param:String}"