Several statements in one query

[rasmus93]

Hi, @serebrserg !
Can we execute several statements in one query?
For example: "CREATE TABLE .....; CREATE TABLE .....;"
For now it is not working. Is it bug of jdbc-connector or normal behavior of database?
Thanks!

[spitty]

Hello, @rasmus93!
As far as I know JDBC doesn't support multi statement execution. This functionality is implemented in MySQL Connector/J and is not recommended to be used:

If you want to execute multiple statements, you need to use separate executes. Technically, MySQL does have an option to support multiple executions which can be enabled by a connection property. This behavior is not compliant with the JDBC specification/API and makes your code less portable.
(source: http://stackoverflow.com/a/18516309/954275)

It is always possible to separate statements in source string. And this approach gives you more options for error handling.

Is there an issue behind this question?

[rasmus93]

Hi again,
It will be very nice to do "create table" statement, but I understand why such requirement should be.

Issue behind this is that I need to create local and distributed tables(based on local) or alter some columns(also do in local and distributed tables - equal changes).
And I want to do this in one query. But I understand why it is not recommended to be used.

Thanks for answer!

[spitty]

And I want to do this in one query

You probably mean "in a single transaction", aren't you?

As far as I know, currently there are no transactions support in CH because of their impact on performance. Thus you can not assume that multiple statements written in a single query (if it would be possible) will be executed as an atomic change.

BTW in Oracle RDBMS DDL statements make two implicit commits: before and after change. It means that it is impossible to rollback completed DDL. And "two-statement-in-one-query" trick is impossible too in that RDBMS.

[serebrserg]

Thanks for discussion.
In fact it is the normal behavior of Clickhouse.

Making the query through clickhouse-client:

:) CREATE TABLE test.test2 ( d Float64) ENGINE = Log; CREATE TABLE test.test3 ( d Float64) ENGINE = Log;

CREATE TABLE test.test2
(
d Float64
) ENGINE = Log

Received exception from server:
Code: 62. DB::Exception: Received from localhost:9000, ::1. DB::Exception: Syntax error (Multi-statements are not allowed): failed at position 52: CREATE TABLE test.test3 ( d Float64) ENGINE = Log;.

0 rows in set. Elapsed: 0.020 sec.

So if a correct exception with correct code is thrown, this is ok.

[blanklin030]

@rasmus93 you can use String dbUrl = "jdbc:clickhouse:///test?allowMultiQueries=true"; to exec multiple query in clickhouse jdbc

[LinhaiXu]

@blanklin030 What's your ClickHouseDriver version? I tried com.clickhouse.jdbc.ClickHouseDriver (0.3.2) and ru.yandex.clickhouse.ClickHouseDriver (0.2.4) they both not working