[jira] [Created] (CALCITE-1533) While executing DML Queries says table not found

["Prasad V S (JIRA)" <ji...@apache.org>]

Prasad V S created CALCITE-1533:
-----------------------------------

             Summary: While executing DML Queries says table not found 
                 Key: CALCITE-1533
                 URL: https://issues.apache.org/jira/browse/CALCITE-1533
             Project: Calcite
          Issue Type: Bug
            Reporter: Prasad V S
            Assignee: Julian Hyde


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




--
This message was sent by Atlassian JIRA
(v6.3.4#6332)