[
https://issues.apache.org/jira/browse/CALCITE-1533?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15741101#comment-15741101
]
Julian Hyde commented on CALCITE-1533:
--------------------------------------
I don't have a SQL Server database to test against, but for anyone trying to
debug this, here are the places I'd look. First, check case sensitivity. Maybe
the table and schema exist in SQL Server, but different case.
Second, check whether the JDBC adapter is supplying the right values for
"catalog" and "schema". SQL Server's catalog structure is different from most
databases, with the "dbo" user, and then databases.
Are you able to select from this table via Calcite? If so what name does the
JDBC adapter generate in the query?
> 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
> Components: jdbc-adapter
> 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)
