Client API and passing query parameters

[AlexMAS]

Hello!

I'm trying to migrate from JDBC API to Client API by using 0.3.2-patch10.

With JDBC API I can define parameters using the ? marker like this:

select number from numbers(?) where number % ? = 0

And then set them like this:

java.sql.PreparedStatement statement;
statement.setInt(1, 10);
statement.setInt(1, 2);

Eventually I'll get the query:

select number from numbers(10) where number % 2 = 0

The Client API expects that parameters are defined in the format :<name>[(<type>)]. Thus I should rewrite my request like this:

select number from numbers(:limit) where number % :div = 0

Along with it I expected a method which associates names and values:

com.clickhouse.client.ClickHouseRequest request;
request.param("limit", 10);
request.param("div", 2);

Yes, currently we have something close in meaning:

params(Map<String, String> namedParams);

But this overload is unsafe due to values are raw SQL which I must build manually. Other overloads accepts only values, and as I understand in my case safer to use something like this:

com.clickhouse.client.ClickHouseRequest request;
request.params(10, 2);

More over I have to define these parameters in the same order as they are defined in the query.

But such using makes defining parameter names (:name) useless and similar to JDBC's ? marker... And now I must replace all ? markers with appropriate names but don't see any profit because don't have an ability to define 'name-value' associations.

As I see we can add one more overload(s):

ClickHouseRequest.params(Map<String, Object> namedParams);
// and/or
ClickHouseRequest.param(String name, Object value);
// etc

And one more question. Does Client API have an ability to use ? markers instead of named parameters? For example, sometimes I have to build SQL queries dynamically and it would be convenient to use simple placeholders like ? instead of generating name for each parameter.

[zhicwu]

Hi @AlexMAS, named parameter is used by default because it has a few advantages: 1) reuse parameter in same query; 2) extended to support data type. Yes, it's going to be a problem when you want to reuse the exact same query for JDBC, or there are many many parameters.

To answer your questions:

But such using makes defining parameter names (:name) useless and similar to JDBC's ? marker...

They looks similar but they're different, for example:

String sql = "select :a,:b,:a";
request.query(sql).params("1", "2");
Assert.assertEquals(request.getStatements(false).get(0), "select 1,2,1");

Does Client API have an ability to use ? markers instead of named parameters?

Question mark placeholder is only supported by JDBC driver at this point, but it makes sense to port it back to clickhouse-client and maybe make it as default for ease of use.