mp911de
commented
3 years ago This is somewhat related to #66 as we don't have support for XML yet.
Closed lukaseder closed 2 years ago
mp911de
commented
3 years ago This is somewhat related to #66 as we don't have support for XML yet.
lukaseder
commented
3 years ago Maybe. This works:
System.out.println(
Flux.from(cf.create())
.flatMap(c -> c.createStatement("select (select 1 as a for xml path)").execute())
.flatMap(it -> it.map((r, m) -> r.get(0)))
.collectList()
.block()
);The result is produced as String. This also doesn't work:
System.out.println(
Flux.from(cf.create())
.flatMap(c -> c.createStatement("select 1 as a for json path").execute())
.flatMap(it -> it.map((r, m) -> r.get(0)))
.collectList()
.block()
);The issue is related to the default cursor usage in the driver. Setting fetchSize to zero bypasses cursor usage. With JDBC, I'm able to reproduce the issue via:
Statement statement = c.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet resultSet = statement.executeQuery("select 1 as a for xml path");resulting in
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The FOR XML clause is not allowed in a CURSOR statement.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteCursored(SQLServerStatement.java:2030)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:846)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:767)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3274)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:692)
at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:110)
at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)I see, I hadn't thought of that possibility. But the equivalent of JDBC's ResultSet.TYPE_SCROLL_SENSITIVE is not necesssary in this case, as a default, right?
mp911de
commented
3 years ago It's not required as we assume by default forward-only cursors and setting the fetch size is the main indicator for the cursor page size. Setting the fetch size to zero expresses the intent to fetch all data directly and so we bypass the cursor.
Users can configure some defaults, whether to prefer cursored execution or if the statement starts with SELECT to apply application-specific customizations, but for a client library, it makes sense to set fetch size to zero when consuming results as JSON/XML.
lukaseder
commented
3 years ago Could there be a more explicit setting to govern this behaviour? I don't like the idea of setting the fetch size on behalf of the user too much, even if it's only for these FOR XML / FOR JSON queries, in case of which the fetch size doesn't matter since everything is aggregated in a single value... As a last resort, that would be doable, of course, but it seems like an accidental side effect.
Bug Report
Versions
Current Behavior
Run a query containing the T-SQL
FORclause with R2DBC and get this error:Steps to reproduce
Expected behavior/code
This should work out of the box, just like with JDBC and produce: