search

trinodb / trino

Official repository of Trino, the distributed SQL query engine for big data, formerly known as PrestoSQL (https://trino.io)
https://trino.io
Apache License 2.0
10.39k stars 2.98k forks source link

Add sybase connector #2481

Open tooptoop4 opened 4 years ago

academy-codex commented 4 years ago

I'll take this up. Have already implemented it.

academy-codex commented 4 years ago

I'll take this up. Have already implemented it.

@findepi Can you assign this to me ?

tooptoop4 commented 4 years ago

@academy-codex what driver do you use? I first tried jconn4.jar but hit "JZ0SB: Parameter index out of range: 1" error, reading online I saw getSchema is not implemented in JConn (also the Jconn jar is not on maven) - 

io.prestosql.spi.PrestoException: JZ0SB: Parameter index out of range: 1.
    at io.prestosql.plugin.jdbc.BaseJdbcClient.listSchemas(BaseJdbcClient.java:191)
    at io.prestosql.plugin.jdbc.BaseJdbcClient.getSchemaNames(BaseJdbcClient.java:168)
    at io.prestosql.plugin.jdbc.jmx.StatisticsAwareJdbcClient.lambda$getSchemaNames$1(StatisticsAwareJdbcClient.java:81)
    at io.prestosql.plugin.jdbc.jmx.JdbcApiStats.wrap(JdbcApiStats.java:35)
    at io.prestosql.plugin.jdbc.jmx.StatisticsAwareJdbcClient.getSchemaNames(StatisticsAwareJdbcClient.java:81)
    at io.prestosql.plugin.jdbc.ForwardingJdbcClient.getSchemaNames(ForwardingJdbcClient.java:63)
    at io.prestosql.plugin.jdbc.ForwardingJdbcClient.getSchemaNames(ForwardingJdbcClient.java:63)
    at io.prestosql.plugin.jdbc.JdbcMetadata.listSchemaNames(JdbcMetadata.java:75)
    at io.prestosql.metadata.MetadataManager.listSchemaNames(MetadataManager.java:291)
    at io.prestosql.metadata.MetadataListing.listSchemas(MetadataListing.java:58)
    at io.prestosql.connector.informationschema.InformationSchemaPageSource.addSchemataRecords(InformationSchemaPageSource.java:300)
    at io.prestosql.connector.informationschema.InformationSchemaPageSource.buildPages(InformationSchemaPageSource.java:214)
    at io.prestosql.connector.informationschema.InformationSchemaPageSource.getNextPage(InformationSchemaPageSource.java:171)
    at io.prestosql.operator.TableScanOperator.getOutput(TableScanOperator.java:287)
    at io.prestosql.operator.Driver.processInternal(Driver.java:379)
    at io.prestosql.operator.Driver.lambda$processFor$8(Driver.java:283)
    at io.prestosql.operator.Driver.tryWithLock(Driver.java:675)
    at io.prestosql.operator.Driver.processFor(Driver.java:276)
    at io.prestosql.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:1075)
    at io.prestosql.execution.executor.PrioritizedSplitRunner.process(PrioritizedSplitRunner.java:163)
    at io.prestosql.execution.executor.TaskExecutor$TaskRunner.run(TaskExecutor.java:484)
    at io.prestosql.$gen.Presto_327_21_gae536e0_dirty____20200129_230107_2.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: JZ0SB: Parameter index out of range: 1.
    at com.sybase.jdbc4.jdbc.ErrorMessage.raiseError(Unknown Source)
    at com.sybase.jdbc4.jdbc.ParamManager.int(Unknown Source)
    at com.sybase.jdbc4.jdbc.ParamManager.doSetParam(Unknown Source)
    at com.sybase.jdbc4.jdbc.ParamManager.setParam(Unknown Source)
    at com.sybase.jdbc4.jdbc.SybPreparedStatement.a(Unknown Source)
    at com.sybase.jdbc4.jdbc.SybPreparedStatement.a(Unknown Source)
    at com.sybase.jdbc4.jdbc.SybPreparedStatement.setString(Unknown Source)
    at com.sybase.jdbc4.jdbc.SybDatabaseMetaData.a(Unknown Source)
    at com.sybase.jdbc4.jdbc.SybDatabaseMetaData.getSchemas(Unknown Source)
    at io.prestosql.plugin.jdbc.BaseJdbcClient.listSchemas(BaseJdbcClient.java:179)
    ... 24 more

then I tried jtds-1.3.1.jar and hit below error

java.lang.AbstractMethodError: undefined
    at net.sourceforge.jtds.jdbc.JtdsDatabaseMetaData.getSchemas(JtdsDatabaseMetaData.java:3587)
    at io.prestosql.plugin.jdbc.BaseJdbcClient.listSchemas(BaseJdbcClient.java:179)
    at io.prestosql.plugin.jdbc.BaseJdbcClient.getSchemaNames(BaseJdbcClient.java:168)
    at io.prestosql.plugin.jdbc.jmx.StatisticsAwareJdbcClient.lambda$getSchemaNames$1(StatisticsAwareJdbcClient.java:81)
    at io.prestosql.plugin.jdbc.jmx.JdbcApiStats.wrap(JdbcApiStats.java:35)
    at io.prestosql.plugin.jdbc.jmx.StatisticsAwareJdbcClient.getSchemaNames(StatisticsAwareJdbcClient.java:81)
    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 io.prestosql.plugin.base.util.LoggingInvocationHandler.handleInvocation(LoggingInvocationHandler.java:60)
    at com.google.common.reflect.AbstractInvocationHandler.invoke(AbstractInvocationHandler.java:86)
    at com.sun.proxy.$Proxy186.getSchemaNames(Unknown Source)
    at io.prestosql.plugin.jdbc.ForwardingJdbcClient.getSchemaNames(ForwardingJdbcClient.java:63)
    at io.prestosql.plugin.jdbc.ForwardingJdbcClient.getSchemaNames(ForwardingJdbcClient.java:63)
    at io.prestosql.plugin.jdbc.JdbcMetadata.listSchemaNames(JdbcMetadata.java:75)
    at io.prestosql.metadata.MetadataManager.listSchemaNames(MetadataManager.java:291)
    at io.prestosql.metadata.MetadataListing.listSchemas(MetadataListing.java:58)
    at io.prestosql.connector.informationschema.InformationSchemaPageSource.addSchemataRecords(InformationSchemaPageSource.java:300)
    at io.prestosql.connector.informationschema.InformationSchemaPageSource.buildPages(InformationSchemaPageSource.java:214)
    at io.prestosql.connector.informationschema.InformationSchemaPageSource.getNextPage(InformationSchemaPageSource.java:171)
    at io.prestosql.operator.TableScanOperator.getOutput(TableScanOperator.java:287)
    at io.prestosql.operator.Driver.processInternal(Driver.java:379)
    at io.prestosql.operator.Driver.lambda$processFor$8(Driver.java:283)
    at io.prestosql.operator.Driver.tryWithLock(Driver.java:675)
    at io.prestosql.operator.Driver.processFor(Driver.java:276)
    at io.prestosql.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:1075)
    at io.prestosql.execution.executor.PrioritizedSplitRunner.process(PrioritizedSplitRunner.java:163)
    at io.prestosql.execution.executor.TaskExecutor$TaskRunner.run(TaskExecutor.java:484)
    at io.prestosql.$gen.Presto_327_21_gae536e0_dirty____20200131_015521_2.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)

From reading https://stackoverflow.com/questions/41231750/abstractmethoderror-with-jtds-jdbc-driver-on-tomcat-8 I thought connectionProperties.setProperty("validationQuery", "select 1"); would solve but not sure where to inject it.

other downside of jtds is it does not support Sybase IQ

I guess fully qualified name of tables having 4 parts in Sybase (but then again sqlserver too?) complicates things: server.db.owner.table

update: before I was using old version of jconn4.jar (v7 from 2010), when using v16 from 2015 it works! http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc39001.0707100/doc/html/har1355981355759.html FAKE_METADATA property led the way

academy-codex commented 4 years ago

Hey @tooptoop4. I am using sajdbc. See my fork today.

academy-codex commented 4 years ago

@tooptoop4 You can see the PR I have made.

kondamudikarthik commented 4 years ago

any update on this issue?

codekaust commented 4 years ago

Any updates on this issue @academy-codex @tooptoop4 ? This connector will be of great help.

tooptoop4 commented 4 years ago

https://github.com/prestosql/presto/pull/3105 is better than this