Add support for prepared statements in JDBC driver #1195

felika · 2014-04-15T05:27:57Z

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)

The text was updated successfully, but these errors were encountered:

premal · 2015-01-25T20:57:28Z

Do you guys have any updates on this issue?

kokosing · 2015-10-22T12:22:26Z

I will take this one.

electrum · 2015-10-22T15:11:11Z

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

kokosing · 2015-10-23T05:18:17Z

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 · 2015-12-08T20:14:33Z

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 · 2015-12-16T16:35:24Z

+1

toyama0919 · 2015-12-16T23:41:55Z

+1

spragues-trulia · 2016-01-05T01:34:11Z

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 · 2016-01-05T10:52:16Z

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 · 2016-01-05T15:34:39Z

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 · 2016-01-05T16:03:49Z

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 · 2016-01-07T15:25:50Z

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 · 2016-01-07T15:32:22Z

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 · 2016-05-17T22:04:52Z

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

maciejgrzybek · 2016-08-04T22:12:11Z

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 · 2016-11-07T23:10:00Z

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 · 2016-11-22T15:17:41Z

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

electrum · 2016-11-22T15:41:41Z

This is still being worked on.

ghost · 2016-11-24T10:38:37Z

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

ghost · 2016-11-29T10:28:56Z

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

cawallin · 2016-11-29T14:25:10Z

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

kesak · 2016-11-29T15:35:54Z

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 · 2016-11-29T15:38:03Z

@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 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 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 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 · 2016-12-01T22:05:45Z

@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 · 2017-02-10T00:29:03Z

+1 on it, any roadmap to implement it ?

shivasakthi18 · 2017-02-24T07:06:10Z

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

harrymiya · 2017-03-21T08:08:18Z

+1 on it, any roadmap ?

CiaranMcCann · 2017-03-22T18:34:42Z

+1 Still affecting Presto 0.166

losipiuk · 2017-03-23T21:44:41Z

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 · 2017-05-11T22:04:44Z

Blocked/impacted by #5759

HAOGRE · 2017-08-29T08:29:22Z

+1 Still affecting Presto 0.183

fsck-mount · 2017-10-21T01:17:11Z

+1 Still affecting Presto 0.187
👍 2

onionch · 2017-11-23T05:59:10Z

+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 · 2018-03-08T03:31:51Z

+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 · 2018-03-15T17:06:37Z

+1

ghost · 2018-04-06T15:52:52Z

+1

owahab · 2018-05-11T09:56:57Z

Still affecting Presto 0.200

Mlnard1 · 2018-05-21T16:27:32Z

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

electrum · 2018-05-22T15:46:01Z

This is done in #10673

Mlnard1 · 2018-05-22T23:00:57Z

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

…

On Tue, May 22, 2018 at 11:46 AM, David Phillips ***@***.***> wrote: This is done in #10673 <#10673> — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#1195 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AYCMaLwxI-jZGxJgob-_kcWGPb8haVE0ks5t1DLZgaJpZM4ByiKS> .

electrum · 2018-05-23T02:58:14Z

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 · 2018-06-06T08:50:47Z

@electrum still not available :(

Mlnard1 · 2018-06-06T22:41:42Z

Any idea when? From: Igor <notifications@github.com> Sent: Wednesday, June 6, 2018 4:51 AM To: prestodb/presto <presto@noreply.github.com> Cc: Mlnard1 <mn.gotswell@gmail.com>; Comment <comment@noreply.github.com> 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 <#1195 (comment)> , or mute the thread <https://github.com/notifications/unsubscribe-auth/AYCMaEZtURQxy-xBIgV_VtytCJL3JcIpks5t55gKgaJpZM4ByiKS> . <https://github.com/notifications/beacon/AYCMaFlnYZmyl6aU-7AxUVUTvmIUSMvuks5t55gKgaJpZM4ByiKS.gif>

dain · 2018-06-07T04:00:06Z

@Mlnard1 and @ilio, please track the progress of the pull request here: #10673

magicsilence · 2018-06-11T05:14:23Z

+1 still not available in the 0.203 release

brentgracey · 2018-06-20T12:48:02Z

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

dain changed the title ~~presto-jdbc-0.66-SNAPSHOT.jar preparedstatement unsupported exception.~~ Add support for prepared statements in JDBC driver Jul 7, 2014

maciejgrzybek closed this as completed Aug 4, 2016

janua mentioned this issue Sep 14, 2016

Presto JDBC driver SQLException typelevel/doobie#339

Closed

electrum reopened this Nov 22, 2016

findepi mentioned this issue Nov 7, 2017

Method Connection.prepareStatement is not yet implemented #9299

Closed

timyates mentioned this issue Jan 31, 2018

Method Connection.prepareStatement is not yet implemented on Pentaho timyates/mod-jdbc-persistor#36

Open

electrum closed this as completed May 22, 2018

Add support for prepared statements in JDBC driver #1195

Add support for prepared statements in JDBC driver #1195

Comments

felika commented Apr 15, 2014

premal commented Jan 25, 2015

kokosing commented Oct 22, 2015

electrum commented Oct 22, 2015

kokosing commented Oct 23, 2015

JanDoerrenhaus commented Dec 8, 2015

albertocsm commented Dec 16, 2015

toyama0919 commented Dec 16, 2015

spragues-trulia commented Jan 5, 2016

albertocsm commented Jan 5, 2016

cawallin commented Jan 5, 2016

spragues-trulia commented Jan 5, 2016

spragues-trulia commented Jan 7, 2016

spragues-trulia commented Jan 7, 2016

xerial commented May 17, 2016

maciejgrzybek commented Aug 4, 2016

PatrickSauts commented Nov 7, 2016 • edited Loading

villasv commented Nov 22, 2016

electrum commented Nov 22, 2016

ghost commented Nov 24, 2016

ghost commented Nov 29, 2016

cawallin commented Nov 29, 2016

kesak commented Nov 29, 2016

ghost commented Nov 29, 2016

kesak commented Dec 1, 2016

hodrigohamalho commented Feb 10, 2017 • edited Loading

shivasakthi18 commented Feb 24, 2017

harrymiya commented Mar 21, 2017

CiaranMcCann commented Mar 22, 2017

losipiuk commented Mar 23, 2017

Downchuck commented May 11, 2017

HAOGRE commented Aug 29, 2017

fsck-mount commented Oct 21, 2017

onionch commented Nov 23, 2017 • edited Loading

tuean commented Mar 8, 2018 • edited Loading

rodrigopalhares commented Mar 15, 2018

ghost commented Apr 6, 2018

owahab commented May 11, 2018

Mlnard1 commented May 21, 2018

electrum commented May 22, 2018

Mlnard1 commented May 22, 2018 via email

electrum commented May 23, 2018

ilio commented Jun 6, 2018

Mlnard1 commented Jun 6, 2018 via email

dain commented Jun 7, 2018

magicsilence commented Jun 11, 2018

brentgracey commented Jun 20, 2018

PatrickSauts commented Nov 7, 2016 •

edited

Loading

hodrigohamalho commented Feb 10, 2017 •

edited

Loading

onionch commented Nov 23, 2017 •

edited

Loading

tuean commented Mar 8, 2018 •

edited

Loading