Presto: Add support for prepared statements in JDBC driver

[felika]

I'm using presto-jdbc-0.66-SNAPSHOT.jar, and trying to execute presto query to presto-server on my java application.

Below sample code, using jdbc statement, is working well.

    Class.forName("com.facebook.presto.jdbc.PrestoDriver");
    Connection connection = DriverManager.getConnection("jdbc:presto://192.168.33.33:8080/hive/default", "hive", "hive");

    Statement statement = connection.createStatement();
    ResultSet rs = statement.executeQuery("SHOW TABLES");
    while(rs.next()) {
        System.out.println(rs.getString(1));
    }

However, using jdbc preparedstatement, throw exception.
Is presto-jdbc not support yet "preparedstatement" ?
Here's my test code and exception info.

Test Code :

    Class.forName("com.facebook.presto.jdbc.PrestoDriver");
    Connection connection = DriverManager.getConnection("jdbc:presto://192.168.33.33:8080/hive/default", "hive", "hive");

    PreparedStatement ps = connection.prepareStatement("SHOW TABLES");
    ResultSet rs = ps.executeQuery();
    while(rs.next()) {
        System.out.println(rs.getString(1));
    }

Exception Info :

    java.lang.UnsupportedOperationException: PreparedStatement
at com.facebook.presto.jdbc.PrestoPreparedStatement.<init>(PrestoPreparedStatement.java:44)
at com.facebook.presto.jdbc.PrestoConnection.prepareStatement(PrestoConnection.java:93)
at com.nsouls.frescott.hive.mapper.PrestoConnectionTest.testShowTable(PrestoConnectionTest.java:37)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:88)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
at org.junit.runners.ParentRunner.run(ParentRunner.java:300)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
at org.junit.runner.JUnitCore.run(JUnitCore.java:157)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:74)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:202)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:65)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)

[premal]

Do you guys have any updates on this issue?

[kokosing]

I will take this one.

[electrum]

This requires a new API on the server or adding placeholders to the parser and doing it in the driver.

[kokosing]

Yes, I was doing the latter. Anyway, I was told that in Teradata there is ongoing effort to provide ODBC/JDBC, so I think it will better if I switch to other task. I don't know what is going to be supported and when exactly, but I would prefer to wait for that before doing this ticket.

[JanDoerrenhaus]

This is a showstopper for us, as the 3rd party software we want to connect to Presto via JDBC relies on prepared statements. Are there any plans on implementing this in the near future?

[albertocsm]

+1

[toyama0919]

+1

[spragues-trulia]

lemme add my desire to see this implemented as well. I'm finding that Pentaho/PDI makes a call to prepareStatment() as well and boom that stops the show right there.

Any updates from the Teradata guys by chance?

(i see from the OP that the executeQuery() works just fine and that 3rd party apps that use that probably will work fine)

[albertocsm]

in my experience Pentaho does call prepareStatment only if you use queries with parameters @spragues-trulia.
I had that same scenario and what i ended up doing was implementing a very poor client side handling of parametric queries on the jbdc module.
i didnt even considered making a PR with it because AFAIK, the road map is to have a proper implementation of parametric queries being pushed all the way down

regarding Teradata, the info i got is that this is in their roadmap but there is currently no team allocated to parametric queries impl.

[cawallin]

From the Teradata side, our recently released ODBC driver fakes prepared statement support. Namely, the driver wraps SELECT queries with LIMIT 0 to get the metadata, meaning that when prepared statements are used, the driver executes two queries, one with LIMIT 0 and then the actual query. That means there's a performance penalty, and there also may be cases where the metadata isn't exactly the same with and without LIMIT 0. That ODBC driver (as well as the Teradata-certified Presto release and presto-admin) can be found at www.teradata.com/presto. So you can give it a try!

We are also indeed looking into adding support to Presto (which is ideally what should happen), but haven't started that work yet.

[spragues-trulia]

Thanks @albertocsm & @cawallin . Wouldn't you have known it _after_ i updated this ticket i checked my email and that's where i saw Teradata's announcement of the new ODBC driver on the presto-user mail list. Figures. We'll give it the 'ol one-two today or tomorrow and report our findings back here.

This workaround might be enough to get things to play nice together! That'd be a huge first step.

And thanks again for your responses!

[spragues-trulia]

hi guys we're still working on this. we managed to get the iODBC command line test utility to communicate with presto in fine order but still struggling a little with Pentaho/PDI using it.

need to study this a bit more to make Pentaho aware of this new odbc.ini file as that's not quite clear at the moment where Pentaho presently picks up its odbc config information. we'll get there just need some more time.

I will say though - my opinion only - the jdbc driver setup wins on simplicity hands down! :)

[spragues-trulia]

and i just found this: http://wiki.pentaho.com/pages/viewpage.action?pageId=14850644 . I think engaging Pentaho support might be the prudent next move (for us) before we spend more time going down the rabbit hole.

[xerial]

Now that #4412 is merged, I think it's a good timing to revisit this issue for supporting Prepared Statements in JDBC.

[maciejgrzybek]

Since #4412 is merged and #5414 is pending, I think this issue can be closed because discussion starting 1.5 year back may be outdated and partially irrelevant. Feel free to start a new issue if something's wrong or is missing in current implementation.

[PatrickSauts]

Method Connection.prepareStatement is not yet implemented with presto-jdbc 0.155 inside DbVisualizer while trying to DELETE or INSERT.

Inside java code too using HikariDataSource and Jooq

try (Connection con = this.pool.getConnection()) {
   String query = ...
   DSL.using(con).fetch(query).stream()...
}

[villasv]

other places mention that this should be fixed by 0.153 but I'm using 0.157 and still get NotImplementedException.

[electrum]

This is still being worked on.

[ghost]

@electrum How to integrate spring JPA using presto-jdbc ?

[ghost]

@cawallin : Caused by: java.sql.SQLFeatureNotSupportedException: [Teradata][JDBC](10220) Driver not capable.
Could you please help me solve this problem?

[cawallin]

@handsetvn -- can you file a new issue with the query that failed and the full stacktrace? Your problem seems unrelated to this issue.

[kesak]

java.sql.SQLException: Method Connection.prepareStatement is not yet implemented with presto-jdbc-0.157.jar . Iam using dbutils to run the query with result handler. I can able to connect and get the results as given in samples but while (rs.next()) is slow . Could any one help , to get the result set using result handler.

[ghost]

@cawallin
. ____ _ __ _ _
/\ / ___'_ __ _ _(_)_ __ __ _
( ( )___ | '_ | '_| | '_ / _` |
\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |___, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v1.2.5.RELEASE)

2016-11-29 22:36:42.551 INFO 15341 --- [ main] n.cero.sqltemplate.example.Application : Starting Application on longtran with PID 15341 (/home/longtran/Downloads/sqltemplate-master/bootiful-sqltemplate-example/target/classes started by longtran in /home/longtran/Downloads/sqltemplate-master/bootiful-sqltemplate-example)
2016-11-29 22:36:42.592 INFO 15341 --- [ main] s.c.a.AnnotationConfigApplicationContext : Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@7a187f14: startup date [Tue Nov 29 22:36:42 ICT 2016]; root of context hierarchy
2016-11-29 22:36:43.831 INFO 15341 --- [ main] o.s.j.e.a.AnnotationMBeanExporter : Registering beans for JMX exposure on startup
2016-11-29 22:36:43.844 INFO 15341 --- [ main] n.cero.sqltemplate.example.Application : Started Application in 1.697 seconds (JVM running for 2.069)
2016-11-29 22:36:43.865 DEBUG 15341 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL query
2016-11-29 22:36:43.865 DEBUG 15341 --- [ main] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL statement [SELECT * FROM mysql.mp_commerce.ship_carrier]
Exception in thread "main" org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory ([Teradata][JDBC](10220) Driver not capable.)
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:630)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:695)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:727)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:777)
at ninja.cero.sqltemplate.core.SqlTemplate.forList(SqlTemplate.java:66)
at ninja.cero.sqltemplate.example.SampleProcess.process(SampleProcess.java:18)
at ninja.cero.sqltemplate.example.Application.main(Application.java:44)
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory ([Teradata][JDBC](10220) Driver not capable.)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
... 7 more
Caused by: java.sql.SQLFeatureNotSupportedException: [Teradata][JDBC](10220) Driver not capable.
at com.teradata.exceptions.ExceptionConverter.toSQLException(Unknown Source)
at com.teradata.jdbc.common.SConnection.setAutoCommit(Unknown Source)
at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:331)
at org.apache.commons.dbcp.PoolableConnectionFactory.activateObject(PoolableConnectionFactory.java:378)
at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1248)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221)
... 10 more
2016-11-29 22:36:45.654 INFO 15341 --- [ Thread-1] s.c.a.AnnotationConfigApplicationContext : Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@7a187f14: startup date [Tue Nov 29 22:36:42 ICT 2016]; root of context hierarchy
2016-11-29 22:36:45.657 INFO 15341 --- [ Thread-1] o.s.j.e.a.AnnotationMBeanExporter : Unregistering JMX-exposed beans on shutdown

[kesak]

@electrum we are getting the following exception, while trying to use result handler . you have mentioned in your previous comments that its being worked on. When can we expect it or if there is any workaround. Please let us know.

java.sql.SQLException: Method Connection.prepareStatement is not yet implemented with presto-jdbc-0.157.jar

[hodrigohamalho]

+1 on it, any roadmap to implement it ?

[shivasakthi18]

+1 on it, post us some expected time to accomplish.

[harrymiya]

+1 on it, any roadmap ?

[CiaranMcCann]

+1 Still affecting Presto 0.166

[losipiuk]

I do not believe anyone is working right now on adding support for prepared statement to open-source JDBC drive.

I would keep this issue as an remainder. Yet I suggest you try to use closed source driver which is part of Teradata Presto distribution available here: www.teradata.com/presto. It supports prepared statements.

[Downchuck]

Blocked/impacted by #5759

[HAOGRE]

+1 Still affecting Presto 0.183

[fsck-mount]

+1 Still affecting Presto 0.187
👍 2

[onionch]

+1 Still affecting Presto 0.189

Cause: com.facebook.presto.jdbc.NotImplementedException: Method Connection.prepareStatement is not yet implemented
; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; Method Connection.prepareStatement is not yet implemented; nested exception is com.
facebook.presto.jdbc.NotImplementedException: Method Connection.prepareStatement is not yet implemented] with root cause
com.facebook.presto.jdbc.NotImplementedException: Method Connection.prepareStatement is not yet implemented
at com.facebook.presto.jdbc.PrestoConnection.prepareStatement(PrestoConnection.java:111)
at com.facebook.presto.jdbc.PrestoConnection.prepareStatement(PrestoConnection.java:281)
at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:472)
at com.alibaba.druid.filter.FilterAdapter.connection_prepareStatement(FilterAdapter.java:941)
at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:143)
at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:469)
at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:375)

[tuean]

+1 presto-jdbc 0.189

update
I found something on https://prestodb.io/docs/0.189/sql/execute.html
I tested it and found it worded for jdbc mode!
note: prepare query is worked on current session so it doesn't support cli

[rodrigopalhares]

+1

[ghost]

+1

[owahab]

Still affecting Presto 0.200

[Mlnard1]

Yes - trying to use DBVisualizer to query Linked tables. Can create the linked table, but not query S3 using Presto 0.2

[electrum]

This is done in #10673

[Mlnard1]

awesome! do I need a new .jar file?
I'm

On Tue, May 22, 2018 at 11:46 AM, David Phillips [e-mail]
wrote:

This is done in #10673 https://github.com/prestodb/presto/pull/10673

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/prestodb/presto/issues/1195#issuecomment-391040817,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AYCMaLwxI-jZGxJgob-_kcWGPb8haVE0ks5t1DLZgaJpZM4ByiKS
.

[electrum]

It is not merged yet, but should be in the 0.203 release. If you want to use it sooner, you will need to build a patched version locally with the change from that PR.

[ilio]

@electrum still not available :(

[Mlnard1]

Any idea when?

From: Igor [e-mail]
Sent: Wednesday, June 6, 2018 4:51 AM
To: prestodb/presto [e-mail]
Cc: Mlnard1 [e-mail]; Comment [e-mail]
Subject: Re: [prestodb/presto] Add support for prepared statements in JDBC driver (#1195)

@electrum https://github.com/electrum still not available :(

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub https://github.com/prestodb/presto/issues/1195#issuecomment-394993430 , or mute the thread https://github.com/notifications/unsubscribe-auth/AYCMaEZtURQxy-xBIgV_VtytCJL3JcIpks5t55gKgaJpZM4ByiKS . https://github.com/notifications/beacon/AYCMaFlnYZmyl6aU-7AxUVUTvmIUSMvuks5t55gKgaJpZM4ByiKS.gif

[dain]

@Mlnard1 and @ilio, please track the progress of the pull request here: https://github.com/prestodb/presto/pull/10673

[magicsilence]

+1 still not available in the 0.203 release

[myFree]

Building master locally; 205-SNAPSHOT runs queries generated from Anorm; which up to 204 error-ed on prepared statements.