Glad that solved your problem. Thanks for getting back to us, and for updating the StackExchange article. Case-sensitivity issues are one of the main problems people run into, and that article will help.
Julian > On Jun 9, 2016, at 9:43 AM, Dan Payne <[email protected]> wrote: > > 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 >>> >>> >>
