Calcite connecting to sql server

Question

I am trying out Calcite to connect to SQL server. trying to run very simple example to just pull data from person table.

Here is the code i am using Schema:

public static class PersonSchema
{

   public final Person[] persons;   

  public PersonSchema(Person[] persons)
 {
     this.persons = persons;
 }

 public PersonSchema(){
    persons=null;
 }
}

public static class Person {
   public final int personId;
   public final String firstName;
   public final String lastName;

   public Person(int personId, String firstName, String lastName) {
      this.personId = personId;
      this.firstName = firstName;
      this.lastName = lastName;
    }
  }
}

Here is the main code

  @Test public void testRealSqlServerData_MI() throws Exception {
      Class.forName("org.apache.calcite.jdbc.Driver");
      Properties info = new Properties();
      info.setProperty("lex", "SQL_SERVER");
      info.setProperty("UserName", "USER_XXX");
      info.setProperty("Password", "XXXXXXX");
      Connection connection =         DriverManager.getConnection
     ("jdbc:calcite://SPG;databaseName=Dept", info);

     CalciteConnection calciteConnection =
          connection.unwrap(CalciteConnection.class);


      SchemaPlus rootSchema = calciteConnection.getRootSchema();
      SchemaPlus schema = rootSchema.add("PersonSchema", 
          new      ReflectiveSchema(new PersonSchema()));         

      Statement statement = calciteConnection.createStatement();
      ResultSet resultSet = statement.executeQuery(
          "select e.personid, e.firstname, e.lastname \n"
          + "from person_tbl as e\n");            

      resultSet.close();
      statement.close();
      connection.close();
  } 

sql server settings are like this. Server - SPG DB - Dept schema - dbo

Issues:

1. I am getting Table not found, so looks like its not reading the schema.
2. I would like to understand schema creation on calcite because real time sqls will be more complicated with many joins.
3. What is best way to connect to sql server. Is above code in right direction.

Thanks in advance.

Rishi

Answer 1

Try double-quoting the table names and columns in your query.

ResultSet resultSet = statement.executeQuery(
      "select e.\"personid\", e.\"firstname\", e.\"lastname\" \n"
      + "from \"person_tbl\" as e\n");

Answer 2

you may try this:

info.setProperty("caseSensitive", "false");