Thanks Luis, That worked. I created the table with column names inuppercase.
On Sun, Oct 1, 2017 at 6:38 PM, Luis Fernando Kauer <[email protected]> wrote: > Hi. > > Have you tried a simple query that selects the empno column from table3? > Calcite converts column names to uppercase and it is case sensitive. > I think that is your problem. > Try it in uppercase or use double quotes for exact match. > > > Enviado do Yahoo Mail no Android > <https://overview.mail.yahoo.com/mobile/?.src=Android> > > Em dom, 1 1e out 1e 2017 às 16:23, AshwinKumar AshwinKumar > &It;[email protected]> escreveu: > Hi Team, > > Let me elaborate the issue I am facing. > > I have two databases , one on postgres engine and one on sqlserver engine. > The sqlserver database has a table - > > table4 with the records - > select * from table4 - > > > [image: Inline image 2] > > The postgres engine has the table table 3 - > with records - > > select * from table 3 - > > > [image: Inline image 3] > > I am trying to create a connection to both the databases using apache > calcite and query both the tables in one single query. below is the code - > package test.calcite_rdbms; > > 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 postgresTests { > public static void main(String[] args) throws SQLException, > ClassNotFoundException { > final String dbUrl = "jdbc:sqlserver://localhost; > databaseName=testcal"; > final String dbUrl2 = "jdbc:postgresql://localhost/ > bigdawg_catalog"; > > /*Connection con = DriverManager.getConnection(dbUrl, > "testAdmin", "test@1234"); > Statement stmt1 = con.createStatement(); > stmt1.execute("drop table if exists table2"); > stmt1.execute("create table table2(id varchar(100) not null > primary key, field1 varchar(500))"); > stmt1.execute("insert into table2 values('a', 'aaaa')"); > stmt1.execute("insert into table2 values('b', 'bbbb')"); > con.close();*/ > > Connection connection = DriverManager.getConnection(" > jdbc:calcite:"); > CalciteConnection calciteConnection = connection.unwrap( > CalciteConnection.class); > SchemaPlus rootSchema = calciteConnection.getRootSchema(); > > final DataSource ds = JdbcSchema.dataSource(dbUrl, > "com.microsoft.sqlserver.jdbc.SQLServerDriver", "testAdmin", "test@1234"); > final DataSource ds2 = JdbcSchema.dataSource(dbUrl2, > "org.postgresql.Driver", "pguser", "test"); > rootSchema.add("DB1", JdbcSchema.create(rootSchema, "DB1", ds, > null, null)); > rootSchema.add("DB2", JdbcSchema.create(rootSchema, "DB2", ds2, > null, null)); > Statement stmt3 = connection.createStatement(); > ResultSet rs = stmt3.executeQuery("select a.name from > DB2.\"table3\" as a join DB1.\"table4\" as b on a.empno = b.id "); > > while (rs.next()) { > System.out.println(rs.getString(1) + '=' + rs.getString(2)); > } > > } > } > > Now when I try to run this , It errors out saying - > > Exception in thread "main" java.sql.SQLException: Error while executing > SQL "select a.name from DB2."table3" as a join DB1."table4" as b on > a.empno = b.id ": From line 1, column 66 to line 1, column 70: Column > 'EMPNO' not found in table 'A' > 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) > > As seen above it shows there is no column called empno in table a(which is > an alias). Could you please le me know If anything wrong with the query > syntax in the stmt3.executeQuery(). I tried almost everything. Could you > please look into this. > > Thanks, > Ashwin > > > > > > > > On Mon, Sep 25, 2017 at 6:18 PM, Josh Elser <[email protected]> wrote: > > Apache mailing lists strip attachments for spam/security reasons. > > Please use any number of online services to post a log. Even better, > please distill the error down to something you can include inline in an > email. > > Thanks. > > On 9/25/17 10:26 AM, AshwinKumar AshwinKumar wrote: > > Hi Julian, > > PFA the error log . > > Could you please let me know if I am following the syntax correctly. > Thanks, > Ashwin > > On Sun, Sep 24, 2017 at 9:35 PM, Julian Hyde <[email protected] > <mailto:[email protected] >> wrote: > > That looks good. You’ve created a schema for each data source, then > written a query that uses a table from each schema. > > Does the query work? If not, what is the error? > > Julian > > > On Sep 23, 2017, at 20:28, AshwinKumar AshwinKumar < > [email protected] <mailto:[email protected]> > wrote: > > > > Hi Julian, > > > > I am following the links below - > > > > https://calcite.apache.org/doc s/index.html > <https://calcite.apache.org/docs/index.html> > <https://calcite.apache.org/do cs/index.html > <https://calcite.apache.org/docs/index.html>> > > > > *https://calcite.apache.org/do cs/howto.html > <https://calcite.apache.org/docs/howto.html> > <https://calcite.apache.org/do cs/howto.html > <https://calcite.apache.org/docs/howto.html>> > > <https://calcite.apache.org/do cs/howto.html > <https://calcite.apache.org/docs/howto.html> > <https://calcite.apache.org/do cs/howto.html > <https://calcite.apache.org/docs/howto.html>>>* > > > > I was able to run a basic java class which connects to the > postgresql and > > sqlserver engines. I was able to fire query on each of the engines > > separately. Now I want to query both the engines in a single > query. Could > > you please let me know if there is any special syntax for the > same. I was > > not able to find any examples where we are referring both the > engines in a > > same query. For eg : I have table1 on sqlserver and table 2 on > postgres . > > How to query both the tables at once. I have written the below > java class - > > > > import javax.sql.DataSource; > > > > import org.apache.calcite.adapter.jdb c.JdbcSchema; > > import org.apache.calcite.jdbc.Calcit eConnection; > > import org.apache.calcite.schema.Sche maPlus; > > > > / > > public class postgresTests { > > public static void main(String[] args) throws SQLException, > > ClassNotFoundException { > > final String dbUrl = "jdbc:sqlserver://localhost;da > > tabaseName=testcal"; > > final String dbUrl2 = > "jdbc:postgresql://localhost/b igdawg_catalog"; > > > > Connection con = DriverManager.getConnection(db Url, > "testAdmin", > > "test@1234"); > > Statement stmt1 = con.createStatement(); > > stmt1.execute("drop table if exists table2"); > > stmt1.execute("create table table2(id varchar(100) not > null primary > > key, field1 varchar(500))"); > > stmt1.execute("insert into table2 values('a', 'aaaa')"); > > stmt1.execute("insert into table2 values('b', 'bbbb')"); > > con.close(); > > > > Connection connection = DriverManager.getConnection("j > > dbc:calcite:"); > > CalciteConnection calciteConnection = > connection.unwrap(CalciteConne > > ction.class); > > SchemaPlus rootSchema = calciteConnection.getRootSchem a(); > > > > final DataSource ds = JdbcSchema.dataSource(dbUrl, > > "com.microsoft.sqlserver.jdbc. SQLServerDriver", "testAdmin", > "test@1234"); > > final DataSource ds2 = JdbcSchema.dataSource(dbUrl2, > > "org.postgresql.Driver", "pguser", "test"); > > rootSchema.add("DB1", JdbcSchema.create(rootSchema, "DB1", > ds, > > null, null)); > > rootSchema.add("DB2", JdbcSchema.create(rootSchema, "DB2", > ds2, > > null, null)); > > Statement stmt3 = connection.createStatement(); > > ResultSet rs = stmt3.executeQuery("select * from > \"DB1\".\"table2\" > > as a,DB2.\"table1\" as b where b.id <http://b.id> = \"a\""); > > > > while (rs.next()) { > > System.out.println(rs.getStrin g(1) + '=' + > rs.getString(2)); > > } > > > > } > > } > > > > could you please let me know if the syntax mentioned in > executeQuery () is > > proper one. > > > > Thanks, > > Ashwin > > > > > > > > > > > > On Sep 23, 2017 6:52 PM, "Julian Hyde" <[email protected] > <mailto:[email protected]>> wrote: > > > > What documentation did you read already? > > > > Julian > > > > > > On Sat, Sep 23, 2017 at 12:03 PM, AshwinKumar AshwinKumar > > <[email protected] <mailto:[email protected]> > wrote: > >> Hi , > >> > >> > >> I am completely new to apache calcite. I have just installed apache > > calcite > >> (version - calcite - 1.13.0) using the mvn install command. > Could you > >> please let me know the steps to connect to a postgres and sqlserver > >> instance through calcite. I want to how to get started with > this. Could > >> you please send some pointers. > >> > >> Thanks, > >> Ashwin > > > >
