That worked. I tried several other combinations in case it was a case sensitivity issue but did not try what you suggested. Now that I see it, it seems obvious. Thanks for the help. Would you like to update the StackOverflow thread or do I (new to SO too).
On Wed, Jun 8, 2016 at 5:41 PM, Julian Hyde <[email protected]> wrote: > Can you try > > ResultSet rs = stmt3.executeQuery("select * from db1.\”table1\"”); > > I don’t recall PostgreSQL’s case-sensitivity policy, but if table1 is > lower-case in PostgreSQL’s catalog then it will be lower-case in Calcite’s > too. Calcite’s default case-sensitity policy, like Oracle’s, is to convert > unquoted identifiers to upper-case, so in your original query > > ResultSet rs = stmt3.executeQuery("select * from db1.table1”); > > it will be looking for TABLE1 in schema DB1. > > Julian > > PS I noticed that you asked the same question on SO. > http://stackoverflow.com/questions/37683607/calcite-table-not-found-when-using-postgres > < > http://stackoverflow.com/questions/37683607/calcite-table-not-found-when-using-postgres> > I’m not going to carry on the same conversation in two threads, but let’s > be sure to update SO when we find an answer. > > > On Jun 8, 2016, at 8:45 AM, Dan Payne <[email protected]> wrote: > > > > I'm new to Calcite and trying to query across multiple databases in > > Postgres. But first I'm just trying to get a simple use case working > where > > I query a single Postgres DB via Calcite and JDBC. However, any 'select' > > query executed against Postgres always throws an exception with 'table x > > not found'. I modeled my code after this Calcite test class that queries > > across two datasources in Hsqldb: > > > > > https://github.com/apache/calcite/blob/master/core/src/test/java/org/apache/calcite/test/MultiJdbcSchemaJoinTest.java > > > > > > When I run that test everything works fine. However, when I change it to > > use Postgres instead, I get the 'table not found' error. > > > > I'm using Mac OS X 10.11.5, Calcite 1.7.0, Postgres 9.5.2.0 and Java > > 1.8.0_77. > > > > I boiled the test case and my code down to just the minimum which > > demonstrates the issue. The class is below and pom file follows and then > > finally the error itself. Any help is much appreciated. > > > > package org.apache.calcite; > > > > import java.sql.Connection; > > import java.sql.DriverManager; > > import java.sql.ResultSet; > > import java.sql.SQLException; > > import java.sql.Statement; > > > > import javax.sql.DataSource; > > > > import org.apache.calcite.adapter.jdbc.JdbcSchema; > > import org.apache.calcite.jdbc.CalciteConnection; > > import org.apache.calcite.schema.SchemaPlus; > > > > /** > > * This class demonstrates Calcite unable to recognize tables in Postgres > > on > > * Mac OS X 10.11.5 with Calcite 1.7.0, Postgres 9.5.2.0 and Java > 1.8.0_77. > > * > > * Before you run this class, you must create the user and database in > > * Postgres by executing the following SQL: > > * > > * create user johnsnow with password 'password'; > > * create database db1 with owner johnsnow; > > * > > */ > > public class TableNotFoundMain { > > public static void main(String... args) throws SQLException, > > ClassNotFoundException { > > final String dbUrl = "jdbc:postgresql://localhost/db1"; > > > > Connection con = DriverManager.getConnection(dbUrl, "johnsnow", > > "password"); > > Statement stmt1 = con.createStatement(); > > stmt1.execute("drop table if exists table1"); > > stmt1.execute("create table table1(id varchar not null primary > key, > > field1 varchar)"); > > stmt1.execute("insert into table1 values('a', 'aaaa')"); > > con.close(); > > > > Connection connection = > > DriverManager.getConnection("jdbc:calcite:"); > > CalciteConnection calciteConnection = > > connection.unwrap(CalciteConnection.class); > > SchemaPlus rootSchema = calciteConnection.getRootSchema(); > > final DataSource ds = JdbcSchema.dataSource(dbUrl, > > "org.postgresql.Driver", "johnsnow", "password"); > > rootSchema.add("DB1", JdbcSchema.create(rootSchema, "DB1", ds, > > null, null)); > > > > Statement stmt3 = connection.createStatement(); > > ResultSet rs = stmt3.executeQuery("select * from db1.table1"); > > > > while (rs.next()) { > > System.out.println(rs.getString(1) + '=' + rs.getString(2)); > > } > > } > > } > > > > > > <project xmlns="http://maven.apache.org/POM/4.0.0"; xmlns:xsi=" > > http://www.w3.org/2001/XMLSchema-instance"; > > xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 > > http://maven.apache.org/xsd/maven-4.0.0.xsd";> > > <modelVersion>4.0.0</modelVersion> > > <groupId>org.apache</groupId> > > <artifactId>calcite-table-not-found</artifactId> > > <version>0.0.1-SNAPSHOT</version> > > > > <dependencies> > > <dependency> > > <groupId>org.apache.calcite</groupId> > > <artifactId>calcite-core</artifactId> > > <version>1.7.0</version> > > </dependency> > > <dependency> > > <groupId>org.postgresql</groupId> > > <artifactId>postgresql</artifactId> > > <version>9.4.1208.jre7</version> > > </dependency> > > </dependencies> > > </project> > > > > Exception in thread "main" java.sql.SQLException: Error while executing > SQL > > "select * from db1.table1": From line 1, column 15 to line 1, column 24: > > Table 'DB1.TABLE1' not found > > at org.apache.calcite.avatica.Helper.createException(Helper.java:56) > > at org.apache.calcite.avatica.Helper.createException(Helper.java:41) > > at > > > org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:143) > > at > > > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:186) > > at > org.apache.calcite.TableNotFoundMain.main(TableNotFoundMain.java:45) > > Caused by: org.apache.calcite.runtime.CalciteContextException: From line > 1, > > column 15 to line 1, column 24: Table 'DB1.TABLE1' not found > > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native > Method) > > at > > > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) > > at > > > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) > > at java.lang.reflect.Constructor.newInstance(Constructor.java:423) > > at > > > org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405) > > at > org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:768) > > at > org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:753) > > at > > > org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:3929) > > at > > > org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:106) > > at > > > org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:86) > > at > > > org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:845) > > at > > > org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:831) > > at > > > org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2754) > > at > > > org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:2739) > > at > > > org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2957) > > at > > > org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60) > > at > > > org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:86) > > at > > > org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:845) > > at > > > org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:831) > > at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:208) > > at > > > org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:807) > > at > > > org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:523) > > at > > > org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:577) > > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:224) > > at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:193) > > at > > > org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:720) > > at > > > org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:587) > > at > > > org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:557) > > at > > > org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:214) > > at > > > org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:573) > > at > > > org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:581) > > at > > > org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:135) > > ... 2 more > > Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Table > > 'DB1.TABLE1' not found > > at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native > Method) > > at > > > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) > > at > > > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) > > at java.lang.reflect.Constructor.newInstance(Constructor.java:423) > > at > > > org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405) > > at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:514) > > ... 29 more > >
