I tried the example with the following testing calcitedatabse class,
String sql = "select count(*) from emps";
Schema schema = new ReflectiveSchema(new HrSchema(emps1,
depts1));
CalciteDatabase db = new CalciteDatabase(schema, "hr");
try {
Long l = db.exeGetLong(sql);
System.out.println("sql result " + l);
} catch (SQLException | ValidationException | SqlParseException
| RelConversionException e) {
e.printStackTrace();
}
and the simple counting sql failed with :
java.sql.SQLException: exception while executing query: null
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.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:576)
at
org.apache.calcite.avatica.AvaticaPreparedStatement.executeQuery(AvaticaPreparedStatement.java:137)
at
com.xsmartware.common.calcite.CalciteDatabase.executeQuery(CalciteDatabase.java:77)
at
com.xsmartware.common.calcite.CalciteDatabase.executeQuery(CalciteDatabase.java:84)
at
com.xsmartware.common.calcite.CalciteDatabase.exeGetLong(CalciteDatabase.java:90)
at
com.xsmartware.javatest.calcite.CalCiteTest.test6(CalCiteTest.java:149)
at com.xsmartware.javatest.calcite.CalCiteTest.run(CalCiteTest.java:107)
at
org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:758)
at
org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:748)
at
org.springframework.boot.SpringApplication.run(SpringApplication.java:309)
at
org.springframework.boot.SpringApplication.run(SpringApplication.java:1301)
at
org.springframework.boot.SpringApplication.run(SpringApplication.java:1290)
at
com.xsmartware.javatest.JavaTestApplication.main(JavaTestApplication.java:9)
Caused by: java.lang.NullPointerException
at Baz.bind(Unknown Source)
at
org.apache.calcite.jdbc.CalcitePrepare$CalciteSignature.enumerable(CalcitePrepare.java:363)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable(CalciteConnectionImpl.java:338)
at
org.apache.calcite.jdbc.CalciteMetaImpl._createIterable(CalciteMetaImpl.java:578)
at
org.apache.calcite.jdbc.CalciteMetaImpl.createIterable(CalciteMetaImpl.java:569)
at
org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:184)
at
org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:64)
at
org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:43)
at
org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:572)
... 12 more
public class CalciteDatabase {
static {
try {
Class.forName("org.apache.calcite.jdbc.Driver");
}catch(Throwable t ) {
throw new RuntimeException(t);
}
}
private final CalciteConnection conn;
private final FrameworkConfig config;
public CalciteDatabase(Schema schema, String schemaName)
throws SQLException {
conn = openConnection();
SchemaPlus rootSchema = Frameworks.createRootSchema(true);
rootSchema.add(schemaName, schema);
config = Frameworks.newConfigBuilder()
.defaultSchema(rootSchema.getSubSchema(schemaName))
.parserConfig(SqlParser.config().withCaseSensitive(false))
.build();
}
public SchemaPlus getRootSchema() {
return config.getDefaultSchema().getParentSchema();
}
public SchemaPlus getDefaultSchema() {
return config.getDefaultSchema();
}
private CalciteConnection openConnection() throws SQLException{
Properties info = new Properties();
info.setProperty("lex", "JAVA");
Connection connection = DriverManager.getConnection("jdbc:calcite:",
info);
CalciteConnection conn = connection.unwrap(CalciteConnection.class);
return conn;
}
public ResultSet executeQuery(RelNode relRoot) throws SQLException {
relRoot.getCluster().getPlanner().setRoot(
relRoot.getCluster().getPlanner().changeTraits(
relRoot,
relRoot.getCluster().traitSet().replace(EnumerableConvention.INSTANCE)));
final RelNode bestExp = relRoot.getCluster().getPlanner().findBestExp();
final RelRunner runner = conn.unwrap(RelRunner.class);
try (PreparedStatement statement = runner.prepareStatement(bestExp)) {
return statement.executeQuery();
}
}
public ResultSet executeQuery(String sql)
throws SQLException, ValidationException,
SqlParseException, RelConversionException {
return executeQuery(CalciteUtils.parseSql(sql, config));
}
public Long exeGetLong(String sql)
throws SQLException, ValidationException,
SqlParseException, RelConversionException {
ResultSet rs = executeQuery(sql);
Long ret = null;
if(rs.next()) {
ret = rs.getLong(1);
}
rs.close();
return ret;
}
}
------------------ Original ------------------
From: "Gavin Ray";<[email protected]>;
Send time: Saturday, Feb 19, 2022 0:37 AM
To: "dev"<[email protected]>;
Subject: Re: can we set a default schema for calcite connection to avoid
writing schema names in sql
Sure
Here is a singleton object that uses FrameworkConfig in Kotlin to manage a
single master RootSchema + CalciteConnection:
https://github.com/GavinRay97/GraphQLCalcite/blob/e4ba2426edb546bda9bd5bd87a61764850138348/src/main/kotlin/CalciteSchemaManager.kt#L23-L117
And here is the same in Java:
https://github.com/GavinRay97/GraphQLCalcite/blob/1070d179b67d803f05975cf416c392b010823069/src/main/java/com/example/calcitewrappers/DatabaseManager.java#L22-L60
Hope this is helpful =)
On Fri, Feb 18, 2022 at 10:06 AM xiaobo <[email protected]> wrote:
> Hi Gavin,
>
> Can you share a complete example of using a FrameworkConfig
> object to open a calcite connection, thanks.
>
> SchemaPlus rootSchema = Frameworks.createRootSchema(true);
> Schema schema = new ReflectiveSchema(new HrSchema2(emps1));
> rootSchema.add("hr", schema);
>
> FrameworkConfig builder = Frameworks.newConfigBuilder()
> .defaultSchema(rootSchema.getSubSchema("hr"))
> .parserConfig(SqlParser.config().withCaseSensitive(false))
> .build();
>
>
>
>
> ------------------ Original ------------------
> From: "Gavin Ray";<[email protected]>;
> Send time: Sunday, Feb 13, 2022 10:25 AM
> To: "dev"<[email protected]>;
>
> Subject: Re: can we set a default schema for calcite connection to avoid
> writing schema names in sql
>
>
>
> You can create an empty root schema, add a ReflectiveSchema, and then set
> this as the default schema:
>
> val rootSchema: SchemaPlus = Frameworks.createRootSchema(true)
> val hrReflectiveSchema = ReflectiveSchema(HrSchema())
> rootSchema.add("hr", hrReflectiveSchema)
>
> Frameworks.newConfigBuilder()
> .defaultSchema(rootSchema.getSubSchema("hr"))
>
> On Sat, Feb 12, 2022 at 9:14 PM xiaobo <[email protected]>
> wrote:
>
> > Hi Gavin,
> > Thanks for your help,
> >
> > the defaultSchema(SchemaPlus defaultSchema) method need a SchemaPlus
> which
> > only can be get after a connection is opened, but we want to set a target
> > subschema such as RelfectiveShcema to be the default one, and we guess
> the
> > default schema setting operation should be done before the connection is
> > opened.
> >
> >
> >
> > ------------------ Original ------------------
> > From: "Gavin Ray";<[email protected]>;
> > Send time: Sunday, Feb 13, 2022 1:43 AM
> > To: "dev"<[email protected]>;
> >
> > Subject: Re: can we set a default schema for calcite connection to avoid
> > writing schema names in sql
> >
> >
> >
> > Hey Xiabo,
> >
> > You can do this, however it is easiest to do from the "FrameworkConfig"
> > object, like this:
> >
> > import org.apache.calcite.tools.FrameworkConfig
> > // Need to set case-sensitive to false, or else it tries to
> > // look up capitalized table names and fails
> > //
> > // IE: "EMPS" instead of "emps"
> > val frameworkConfig: FrameworkConfig = Frameworks.newConfigBuilder()
> > .defaultSchema(connection.rootSchema)
> > .parserConfig(SqlParser.config().withCaseSensitive(false))
> > .build()
> >
> > Hope this helps =)
> >
> > On Fri, Feb 11, 2022 at 9:09 PM xiaobo <[email protected]>
> > wrote:
> >
> > > sorry for the html escape characters,
> > > we tried the following and it does not work
> > >
> > > Class.forName("org.apache.calcite.jdbc.Driver");
> > > Properties info = new Properties();
> > > info.setProperty("lex", "JAVA");
> > > info.setProperty(InternalProperty.CASE_SENSITIVE.name
> (),
> > > "false");
> > > info.setProperty("defaultSchema", "hr");
> > > Connection connection =
> > > DriverManager.getConnection("jdbc:calcite:", info);
> > > CalciteConnection conn =
> > > connection.unwrap(CalciteConnection.class);
> > > SchemaPlus rootSchema = conn.getRootSchema();
> > > Schema schema = new ReflectiveSchema(target);
> > > rootSchema.add(schemaName, schema);
> > > return conn;
> > >
> > >
> > >
> > >
> > > ------------------ Original ------------------
> > > From: "xiaobo ";<[email protected]>;
> > > Send time: Friday, Feb 11, 2022 11:20 PM
> > > To: "dev"<[email protected]>;
> > >
> > > Subject: can we set a default schema for calcite connection to avoid
> > > writing schema names in sql
> > >
> > >
> > >
> > > we have tried the following and it does not work
> > >
> > >
> > > Class.forName("org.apache.calcite.jdbc.Driver");
> > > Properties
> info
> > =
> > > new Properties();
> > >
> > > info.setProperty("lex", "JAVA");
> > >
> > > info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
> > > "false");
> > >
> > > info.setProperty("defaultSchema", "hr");
> > > try {
> > >
> > > Connection connection =
> > >
> > >
> > > DriverManager.getConnection("jdbc:calcite:", info);
> > >
> > > CalciteConnection conn =
> > >
> > >
> > > connection.unwrap(CalciteConnection.class);
> > >
> > > SchemaPlus rootSchema = conn.getRootSchema();
> > >
