I answered my own question on SO so no need for you to do so as well. Thanks again for the help!
On Thu, Jun 9, 2016 at 11:38 AM, Dan Payne <[email protected]> wrote: > 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 >> >> >
