[CALCITE-1533] While executing DML queries says table not found

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: jdbc-adapter
• Assignee: Unassigned
• Reporter: Prasad V S
• Created: 08/Dec/16 05:27
• Updated: 22/Feb/19 01:22

Description

I took latest update of CALCITE-1527 still im not able to execute DML queries

Code :

BasicDataSource dataSource = new BasicDataSource();

/* SQL Server */
dataSource.setUrl("jdbc:sqlserver://111.11.11.1:1433");
dataSource.setUsername("xxxxxxx");
dataSource.setPassword("xxxxxxxxx");
dataSource.setDefaultCatalog("TRUNKDB");
dataSource.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

Class.forName("org.apache.calcite.jdbc.Driver");
Connection connection =
DriverManager.getConnection("jdbc:calcite:");
CalciteConnection calciteConnection =
connection.unwrap(CalciteConnection.class);
SchemaPlus rootSchema = calciteConnection.getRootSchema();

/JDBC Schema/
JdbcSchema jdbcSchema = JdbcSchema.create(rootSchema, "TRUNKDB", dataSource, null, "dbo");
rootSchema.add("TRUNKDB", jdbcSchema);

Statement statement = connection.createStatement();
// Select statement is working
ResultSet resultSet = statement.executeQuery("select * from \"TRUNKDB\".\"survey\"");

final StringBuilder buf = new StringBuilder();
while (resultSet.next()) {
int n = resultSet.getMetaData().getColumnCount();
for (int i = 1; i <= n; i++)

{ buf.append(i > 1 ? "; " : "") .append(resultSet.getMetaData().getColumnLabel(i)) .append("=") .append(resultSet.getObject(i)); }

System.out.println(buf.toString());
buf.setLength(0);
}

//insert statement is not working
int resultSetd1 = statement.executeUpdate("INSERT INTO \"TRUNKDB\".\"survey\"(\"id\",\"name\" ) values (1,'s')");

int resultSetd2 = statement.executeUpdate("INSERT INTO \"TRUNKDB\".\"survey1\" (\"id\",\"name\" ) SELECT \"id\",\"name\" FROM \"TRUNKDB\".\"survey\"");
System.out.println(resultSetd1);
System.out.println(resultSetd2);

Both statement throws the following exception

Exception in thread "main" java.sql.SQLException: Error while executing SQL "INSERT INTO "TRUNKDB"."survey"("id","name" ) values (1,'s')": while executing SQL [INSERT INTO "TRUNKDB"."survey" ("id", "name")
VALUES (1, 's')]
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:156)
at org.apache.calcite.avatica.AvaticaStatement.executeLargeUpdate(AvaticaStatement.java:236)
at org.apache.calcite.avatica.AvaticaStatement.executeUpdate(AvaticaStatement.java:231)
at com.nanobi.calcite.CalciteMSSQL.main(CalciteMSSQL.java:150)
Caused by: java.lang.RuntimeException: while executing SQL [INSERT INTO "TRUNKDB"."survey" ("id", "name")
VALUES (1, 's')]
at org.apache.calcite.runtime.ResultSetEnumerable.enumerator(ResultSetEnumerable.java:154)
at org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:81)
at org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:196)
at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:67)
at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:44)
at org.apache.calcite.avatica.AvaticaConnection$1.execute(AvaticaConnection.java:607)
at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:599)
at org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:615)
at org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:148)
... 3 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'TRUNKDB.survey'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:662)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.apache.calcite.runtime.ResultSetEnumerable.enumerator(ResultSetEnumerable.java:144)
... 12 more