Regression when running DDL (CREATE SCHEMA or CREATE VIEW) in PreparedStatement

[lukaseder]

The following code:

try (Connection c = new com.microsoft.sqlserver.jdbc.SQLServerDriver().connect(url, properties)) {
    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());
    }
}

... works with:

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

... but it no longer works with:

    <version>6.2.0.jre8</version>

The error I'm getting is this:

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)
	at SQLServer.main(SQLServer.java:69)

A workaround is to resort to static statements. The same is true for CREATE VIEW statements:

// Works on both versions
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());
}

I think that's quite a significant regression. Other DDL statements might be affected as well.

[lukaseder]

The SQL Server Profiler shows:

JDBC driver version 6.1.0:

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,NULL,N'create schema x'
select @p1

JDBC driver version 6.2.0:

exec sp_executesql N'create schema x',NULL

[lukaseder]

Possibly related: #166. See also comment on Stack Overflow by @gordthompson: https://stackoverflow.com/q/44950970/521799

[AfsanehR-zz]

@lukaseder Thanks for raising this issue. We are investigating this.
@TobiasSQL Would appreciate your thoughts on this, as well.

[gordthompson]

Additional information: I saw the same results from SQL Profiler that @lukaseder reported. Subsequent fooling around with sqlcmd yielded

1> SELECT LEFT(@@VERSION, 67);
2> go

-------------------------------------------------------------------
Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) - 12.0.5203.0 (X64)

(1 rows affected)
1> EXEC sp_executesql N'create schema x', NULL;
2> go
Msg 214, Level 16, State 3, Server GORD-HP\SQLEXPRESS, Procedure sp_executesql, Line 1
Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'.
1> EXEC sp_executesql N'create schema x', N'';
2> go
Msg 156, Level 15, State 1, Server GORD-HP\SQLEXPRESS, Line 1
Incorrect syntax near the keyword 'schema'.
1> EXEC sp_executesql N'create schema x';
2> go
1>

That is, simply omitting the trailing NULL parameter seems to avoid the problem.

[TobiasSQL]

Correctamundo, this is a bug. @v-afrafi I suggested a change in your fix (otherwise looks great!).

[jorsol]

I was hit by this bug, probably the version 6.2 should be withdrawn and marked as flawed.

[AfsanehR-zz]

@jorsol @lukaseder @gordthompson Thank you for reporting this issue. We are in the process of releasing a hotfix soon, so in the meantime, we would suggest using the last driver version.

[luca-domenichini]

Hi,

as I see here https://github.com/Microsoft/mssql-jdbc/releases/tag/v6.2.1, v6.2.1 reverts some metadata caching commits previously done with #377.

Is the metadata caching planned to be released/reintroduced after some more testing?
..I saw good performance improvements in 6.2.0 with these changes..

[AfsanehR-zz]

Hi @mimmoz81 Thank you for your feedback regarding improvement of performance.
The feature was removed due to some regressions. We will be adding the feature to our next preview releases once we conduct more comprehensive tests. Please stay tuned for the updates!

[TobiasSQL]

@mimmoz81, adding to what @v-afrafi said, the issues found post release showed that even though we passed all of our regression tests we still have issues (I think mainly related to batch execution) which we need to make sure to get better test coverage for.

The feature is available in the dev branch and you can for sure use this if it works for you given the caveat mentioned above.

[AfsanehR-zz]

fixed by pr #388 . Thank you so much @lukaseder for creating this issue.

[lukaseder]

Thank YOU for fixing it so quickly :)