ability to turn off isolation level setting on connect?

[danoyoung]

I'm trying out the new Simbda JDBC driver for Big Query, and it seems to work fine with SQLLine except for during the initial connection I get an erro about setting the isolation level. I believe there's no such thing with Big Query. I was wondering if there's a way to have SQLLine not issue the set isolation level automatically? Other than this, it seems to be working, and I can ignore this...

I also have an email into the Simba support re: isolation levels, but thought I would ask this anyways.

Dan-Young:sql-line dan$ java -Djava.ext.dirs=$PWD sqlline.SqlLine --verbose=true
sqlline version 1.2.0
sqlline> !properties big-query-service.properties
Connecting to jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=[my-project];OAuthType=0;OAuthServiceAcctEmail=[service-account];OAuthPvtKeyPath=[key-path];Timeout=60;LogLevel=6;LogPath=/tmp
Connected to: Google BigQuery (version 2.0)
Driver: SimbaDriverforGoogleBigQuery (version 01.00.02.1003)
Autocommit status: true
Error: [Simba]JDBC Unsupported transaction isolation level: 4. (state=HY000,code=11975)
java.sql.SQLException: [Simba]JDBC Unsupported transaction isolation level: 4.
at com.simba.exceptions.ExceptionConverter.toSQLException(Unknown Source)
at com.simba.jdbc.common.SConnection.setTransactionIsolation(Unknown Source)
at sqlline.Commands.isolation(Commands.java:679)
at sqlline.DatabaseConnection.connect(DatabaseConnection.java:186)
at sqlline.DatabaseConnection.getConnection(DatabaseConnection.java:203)
at sqlline.Commands.connect(Commands.java:1064)
at sqlline.Commands.properties(Commands.java:948)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at sqlline.ReflectiveCommandHandler.execute(ReflectiveCommandHandler.java:38)
at sqlline.SqlLine.dispatch(SqlLine.java:809)
at sqlline.SqlLine.begin(SqlLine.java:686)
at sqlline.SqlLine.start(SqlLine.java:398)
at sqlline.SqlLine.main(SqlLine.java:291)

[julianhyde]

Did you try setting isolation from the command line?

./sqlline --isolation=TRANSACTION_NONE

For what it's worth, DRILL-3114 seems to be related to this issue.

[danoyoung]

Hello Julian, yes I tried that as well

Setting property: [isolation, TRANSACTION_NONE]
sqlline version 1.2.0
sqlline> !properties big-query-service.properties
Connecting to jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=[my-project];OAuthType=0;OAuthServiceAcctEmail=[service-account];OAuthPvtKeyPath=[key-path];Timeout=60;LogLevel=6;LogPath=/tmp

Connected to: Google BigQuery (version 2.0)
Driver: SimbaDriverforGoogleBigQuery (version 01.00.02.1003)
Autocommit status: true
Error: [Simba][JDBC](11900) Unknown transaction isolation level: 0. (state=HY000,code=11900)
java.sql.SQLNonTransientException: [Simba][JDBC](11900) Unknown transaction isolation level: 0.
    at com.simba.exceptions.ExceptionConverter.toSQLException(Unknown Source)
    at com.simba.jdbc.common.SConnection.setTransactionIsolation(Unknown Source)
    at sqlline.Commands.isolation(Commands.java:679)
    at sqlline.DatabaseConnection.connect(DatabaseConnection.java:186)
    at sqlline.DatabaseConnection.getConnection(DatabaseConnection.java:203)
    at sqlline.Commands.connect(Commands.java:1064)
    at sqlline.Commands.properties(Commands.java:948)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at sqlline.ReflectiveCommandHandler.execute(ReflectiveCommandHandler.java:38)
    at sqlline.SqlLine.dispatch(SqlLine.java:809)
    at sqlline.SqlLine.begin(SqlLine.java:686)
    at sqlline.SqlLine.start(SqlLine.java:398)
    at sqlline.SqlLine.main(SqlLine.java:291)
0: jdbc:bigquery://https://www.googleapis.com> 

[danoyoung]

I'll see if I hear anything back from the folks @ Simba....

[julianhyde]

It does sound like a bug in Simba's JDBC driver. But since JDBC drivers often do have bugs, it would be useful to have a way to prevent sqlline from calling Connection.setTransactionIsolation. Say --isolation=default on the command line. Feel free to contribute a patch.

[snuyanzin]

There is a suggestion besides --isolation=default to make call Connection.setTransactionIsolation only if DatabaseMetaData#supportsTransactionIsolationLevel(isolation) returns true.

[julianhyde]

Good idea.

[julianhyde]

Fixed in 2a84c36, PR #126. Thanks @snuyanzin!