无法在 SQL Server 中通过 JDBC 调用 CREATE SCHEMA

Question

我使用的是官方的SQL Server JDBC驱动程序：

<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>6.2.0.jre8</version>
</dependency>

要在此处运行此代码，请执行以下操作：

try (Connection c = new com.microsoft.sqlserver.jdbc.SQLServerDriver().connect(u, p)) {
try (PreparedStatement s1 = c.prepareStatement("create schema x");
PreparedStatement s2 = c.prepareStatement("drop schema x")) {
System.out.println(s1.execute());
System.out.println(s2.execute());
}
}

但是我收到此错误：

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'schema'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1547)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:528)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:461)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2689)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:224)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:204)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:445)

显然，这些语句是正确的，它们作为静态语句工作：

try (Statement s1 = c.createStatement();
Statement s2 = c.createStatement()) {
System.out.println(s1.execute("create schema x"));
System.out.println(s2.execute("drop schema x"));
}

这是有意的，还是 JDBC 驱动程序中的错误？

Answer 1

这似乎是 6.2.0 版的回归。它曾经在 6.1.0 版中工作。我已向Microsoft报告此问题：https://github.com/Microsoft/mssql-jdbc/issues/370

CREATE VIEW语句也会受到影响：

// Works on both versions 6.2.0 and 6.1.0
try (Statement s1 = c.createStatement();
Statement s2 = c.createStatement()) {
System.out.println(s1.execute("create view x as select 1 a"));
System.out.println(s2.execute("drop view x"));
}
// Works only on version 6.1.0
try (PreparedStatement s1 = c.prepareStatement("create view x as select 1 a");
PreparedStatement s2 = c.prepareStatement("drop view x")) {
System.out.println(s1.execute());
System.out.println(s2.execute());
}

(我在Stack Overflow上记录了这一点，因为运行DDL的几个工具，包括jOOQ，Hibernate，MyBatis，Flyway等可能会受到影响(