*Dear all,*
*When executing an SQL query with the SQL module:* sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), let $connection := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") return sql:execute($connection, "SELECT * FROM TABLE")
*I get the following error message:* Error: Stopped at /Users/sa/staging/file, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen. Compiling: - inline let $connection_0 := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") - simplify FLWOR expression: sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") ! sql:execute(., "SELECT * FROM TABLE") Optimized Query: (sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") ! sql:execute(., "SELECT * FROM TABLE")) Query: sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), let $connection := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") return sql:execute($connection, "SELECT * FROM TABLE") Query Plan: <QueryPlan compiled="true" updating="false"> <List type="item()*"> <SqlInit name="sql:init" type="empty-sequence()" size="0"> <Str type="xs:string" size="1">com.microsoft.sqlserver.jdbc.SQLServerDriver</Str> </SqlInit> <IterMap type="item()*"> <SqlConnect name="sql:connect" type="xs:integer" size="1"> <Str type="xs:string" size="1">jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME</Str> <Str type="xs:string" size="1">username</Str> <Str type="xs:string" size="1">pa$$w0rd</Str> </SqlConnect> <SqlExecute name="sql:execute" type="item()*"> <ContextValue type="xs:integer" size="1"/> <Str type="xs:string" size="1">SELECT * FROM TABLE</Str> </SqlExecute> </IterMap> </List> </QueryPlan>
*Does anyone have an idea what the problem is?*
*Any hints are welcome!*
Hi Artur,
Thanks for the full info view output.
The issue seems to be specific to MS SQL. Could you run basex on command line with the -d flag and share the output with us?
Thanks in advance Christian
On Fri, Aug 27, 2021 at 11:17 AM Artur Moor skynexion@gmail.com wrote:
Dear all,
When executing an SQL query with the SQL module: sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), let $connection := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") return sql:execute($connection, "SELECT * FROM TABLE")
I get the following error message: Error: Stopped at /Users/sa/staging/file, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen. Compiling:
- inline let $connection_0 := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd")
- simplify FLWOR expression: sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") ! sql:execute(., "SELECT * FROM TABLE")
Optimized Query: (sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") ! sql:execute(., "SELECT * FROM TABLE")) Query: sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), let $connection := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") return sql:execute($connection, "SELECT * FROM TABLE") Query Plan:
<QueryPlan compiled="true" updating="false"> <List type="item()*"> <SqlInit name="sql:init" type="empty-sequence()" size="0"> <Str type="xs:string" size="1">com.microsoft.sqlserver.jdbc.SQLServerDriver</Str> </SqlInit> <IterMap type="item()*"> <SqlConnect name="sql:connect" type="xs:integer" size="1"> <Str type="xs:string" size="1">jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME</Str> <Str type="xs:string" size="1">username</Str> <Str type="xs:string" size="1">pa$$w0rd</Str> </SqlConnect> <SqlExecute name="sql:execute" type="item()*"> <ContextValue type="xs:integer" size="1"/> <Str type="xs:string" size="1">SELECT * FROM TABLE</Str> </SqlExecute> </IterMap> </List> </QueryPlan>
Does anyone have an idea what the problem is?
Any hints are welcome!
Hi Christian,
# cat mssql-min.xq sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), let $c := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") return sql:execute($c, "SELECT * FROM TABL")
# basex -d ~/staging/mssql-min.xq Aug. 27, 2021 12:16:21 PM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL WARNUNG: TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum. com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) org.basex.query.QueryException: An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.query.QueryError.get(QueryError.java:1429) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:123) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) ... 14 more org.basex.core.BaseXException: Stopped at /Users/sa/staging/mssql-min.xq, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.core.Command.execute(Command.java:94) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) Caused by: org.basex.query.QueryException: An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.query.QueryError.get(QueryError.java:1429) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:123) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) ... 5 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) ... 14 more Stopped at /Users/sa/staging/mssql-min.xq, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen.
Same invocation on version 9.5 of basex runs without errors: # ./basex95/bin/basex -d ~/staging/mssql-min.xq Aug. 27, 2021 12:25:49 PM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL WARNUNG: TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum. sql:row…</sql:row>
I think the warning about TSL negotiation can be ignored.
Am Fr., 27. Aug. 2021 um 11:38 Uhr schrieb Christian Grün christian.gruen@gmail.com:
Hi Artur,
Thanks for the full info view output.
The issue seems to be specific to MS SQL. Could you run basex on command line with the -d flag and share the output with us?
Thanks in advance Christian
On Fri, Aug 27, 2021 at 11:17 AM Artur Moor skynexion@gmail.com wrote:
Dear all,
When executing an SQL query with the SQL module: sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), let $connection := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") return sql:execute($connection, "SELECT * FROM TABLE")
I get the following error message: Error: Stopped at /Users/sa/staging/file, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen. Compiling:
- inline let $connection_0 := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd")
- simplify FLWOR expression: sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") ! sql:execute(., "SELECT * FROM TABLE")
Optimized Query: (sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") ! sql:execute(., "SELECT * FROM TABLE")) Query: sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), let $connection := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") return sql:execute($connection, "SELECT * FROM TABLE") Query Plan:
<QueryPlan compiled="true" updating="false"> <List type="item()*"> <SqlInit name="sql:init" type="empty-sequence()" size="0"> <Str type="xs:string" size="1">com.microsoft.sqlserver.jdbc.SQLServerDriver</Str> </SqlInit> <IterMap type="item()*"> <SqlConnect name="sql:connect" type="xs:integer" size="1"> <Str type="xs:string" size="1">jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME</Str> <Str type="xs:string" size="1">username</Str> <Str type="xs:string" size="1">pa$$w0rd</Str> </SqlConnect> <SqlExecute name="sql:execute" type="item()*"> <ContextValue type="xs:integer" size="1"/> <Str type="xs:string" size="1">SELECT * FROM TABLE</Str> </SqlExecute> </IterMap> </List> </QueryPlan>
Does anyone have an idea what the problem is?
Any hints are welcome!
return sql:execute($c, "SELECT * FROM TABL")
Some wild guesses:
• I assume that "TABL" is correct? • If your database contains a lot of entries: What happens if you request small results? • What happens if you execute "SELECT 1 + 1"? • Do you get the same error with sql:prepare and sql:execute-prepared? • You could check the web for "makeFromDriverError SQLServerException: The connection is closed"
I have currently no MS SQL instance running; maybe someone else has?
Cheers, Christian
# basex -d ~/staging/mssql-min.xq Aug. 27, 2021 12:16:21 PM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL WARNUNG: TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum. com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) org.basex.query.QueryException: An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.query.QueryError.get(QueryError.java:1429) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:123) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) ... 14 more org.basex.core.BaseXException: Stopped at /Users/sa/staging/mssql-min.xq, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.core.Command.execute(Command.java:94) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) Caused by: org.basex.query.QueryException: An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.query.QueryError.get(QueryError.java:1429) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:123) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) ... 5 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) ... 14 more Stopped at /Users/sa/staging/mssql-min.xq, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen.
Same invocation on version 9.5 of basex runs without errors: # ./basex95/bin/basex -d ~/staging/mssql-min.xq Aug. 27, 2021 12:25:49 PM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL WARNUNG: TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum. sql:row…</sql:row>
I think the warning about TSL negotiation can be ignored.
Am Fr., 27. Aug. 2021 um 11:38 Uhr schrieb Christian Grün christian.gruen@gmail.com:
Hi Artur,
Thanks for the full info view output.
The issue seems to be specific to MS SQL. Could you run basex on command line with the -d flag and share the output with us?
Thanks in advance Christian
On Fri, Aug 27, 2021 at 11:17 AM Artur Moor skynexion@gmail.com wrote:
Dear all,
When executing an SQL query with the SQL module: sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), let $connection := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") return sql:execute($connection, "SELECT * FROM TABLE")
I get the following error message: Error: Stopped at /Users/sa/staging/file, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen. Compiling:
- inline let $connection_0 := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd")
- simplify FLWOR expression: sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") ! sql:execute(., "SELECT * FROM TABLE")
Optimized Query: (sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") ! sql:execute(., "SELECT * FROM TABLE")) Query: sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), let $connection := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") return sql:execute($connection, "SELECT * FROM TABLE") Query Plan:
<QueryPlan compiled="true" updating="false"> <List type="item()*"> <SqlInit name="sql:init" type="empty-sequence()" size="0"> <Str type="xs:string" size="1">com.microsoft.sqlserver.jdbc.SQLServerDriver</Str> </SqlInit> <IterMap type="item()*"> <SqlConnect name="sql:connect" type="xs:integer" size="1"> <Str type="xs:string" size="1">jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME</Str> <Str type="xs:string" size="1">username</Str> <Str type="xs:string" size="1">pa$$w0rd</Str> </SqlConnect> <SqlExecute name="sql:execute" type="item()*"> <ContextValue type="xs:integer" size="1"/> <Str type="xs:string" size="1">SELECT * FROM TABLE</Str> </SqlExecute> </IterMap> </List> </QueryPlan>
Does anyone have an idea what the problem is?
Any hints are welcome!
Hi, Christian,
I assume that "TABL" is correct?
YES
If your database contains a lot of entries: What happens if you request small results?
Same error
What happens if you execute "SELECT 1 + 1"?
Same error
Do you get the same error with sql:prepare and sql:execute-prepared?
Yes
You could check the web for "makeFromDriverError SQLServerException: The connection is closed".
I think this is not the right direction because the error occurs only on the version 9.6 and 9.6.1 all previous versions work fine.
Am Fr., 27. Aug. 2021 um 12:57 Uhr schrieb Christian Grün christian.gruen@gmail.com:
return sql:execute($c, "SELECT * FROM TABL")
Some wild guesses:
• I assume that "TABL" is correct? • If your database contains a lot of entries: What happens if you request small results? • What happens if you execute "SELECT 1 + 1"? • Do you get the same error with sql:prepare and sql:execute-prepared? • You could check the web for "makeFromDriverError SQLServerException: The connection is closed"
I have currently no MS SQL instance running; maybe someone else has?
Cheers, Christian
# basex -d ~/staging/mssql-min.xq Aug. 27, 2021 12:16:21 PM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL WARNUNG: TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum. com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) org.basex.query.QueryException: An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.query.QueryError.get(QueryError.java:1429) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:123) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) ... 14 more org.basex.core.BaseXException: Stopped at /Users/sa/staging/mssql-min.xq, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.core.Command.execute(Command.java:94) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) Caused by: org.basex.query.QueryException: An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.query.QueryError.get(QueryError.java:1429) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:123) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) ... 5 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) ... 14 more Stopped at /Users/sa/staging/mssql-min.xq, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen.
Same invocation on version 9.5 of basex runs without errors: # ./basex95/bin/basex -d ~/staging/mssql-min.xq Aug. 27, 2021 12:25:49 PM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL WARNUNG: TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum. sql:row…</sql:row>
I think the warning about TSL negotiation can be ignored.
Am Fr., 27. Aug. 2021 um 11:38 Uhr schrieb Christian Grün christian.gruen@gmail.com:
Hi Artur,
Thanks for the full info view output.
The issue seems to be specific to MS SQL. Could you run basex on command line with the -d flag and share the output with us?
Thanks in advance Christian
On Fri, Aug 27, 2021 at 11:17 AM Artur Moor skynexion@gmail.com wrote:
Dear all,
When executing an SQL query with the SQL module: sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), let $connection := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") return sql:execute($connection, "SELECT * FROM TABLE")
I get the following error message: Error: Stopped at /Users/sa/staging/file, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen. Compiling:
- inline let $connection_0 := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd")
- simplify FLWOR expression: sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") ! sql:execute(., "SELECT * FROM TABLE")
Optimized Query: (sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") ! sql:execute(., "SELECT * FROM TABLE")) Query: sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), let $connection := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") return sql:execute($connection, "SELECT * FROM TABLE") Query Plan:
<QueryPlan compiled="true" updating="false"> <List type="item()*"> <SqlInit name="sql:init" type="empty-sequence()" size="0"> <Str type="xs:string" size="1">com.microsoft.sqlserver.jdbc.SQLServerDriver</Str> </SqlInit> <IterMap type="item()*"> <SqlConnect name="sql:connect" type="xs:integer" size="1"> <Str type="xs:string" size="1">jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME</Str> <Str type="xs:string" size="1">username</Str> <Str type="xs:string" size="1">pa$$w0rd</Str> </SqlConnect> <SqlExecute name="sql:execute" type="item()*"> <ContextValue type="xs:integer" size="1"/> <Str type="xs:string" size="1">SELECT * FROM TABLE</Str> </SqlExecute> </IterMap> </List> </QueryPlan>
Does anyone have an idea what the problem is?
Any hints are welcome!
I think this is not the right direction because the error occurs only on the version 9.6 and 9.6.1 all previous versions work fine.
Thanks, that’s an important hint. I’ll try to reproduce this in a while (but it may take some time; I’ll be offline for a couple of days in September).
Am Fr., 27. Aug. 2021 um 12:57 Uhr schrieb Christian Grün christian.gruen@gmail.com:
return sql:execute($c, "SELECT * FROM TABL")
Some wild guesses:
• I assume that "TABL" is correct? • If your database contains a lot of entries: What happens if you request small results? • What happens if you execute "SELECT 1 + 1"? • Do you get the same error with sql:prepare and sql:execute-prepared? • You could check the web for "makeFromDriverError SQLServerException: The connection is closed"
I have currently no MS SQL instance running; maybe someone else has?
Cheers, Christian
# basex -d ~/staging/mssql-min.xq Aug. 27, 2021 12:16:21 PM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL WARNUNG: TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum. com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) org.basex.query.QueryException: An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.query.QueryError.get(QueryError.java:1429) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:123) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) ... 14 more org.basex.core.BaseXException: Stopped at /Users/sa/staging/mssql-min.xq, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.core.Command.execute(Command.java:94) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) Caused by: org.basex.query.QueryException: An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.query.QueryError.get(QueryError.java:1429) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:123) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) ... 5 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) ... 14 more Stopped at /Users/sa/staging/mssql-min.xq, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen.
Same invocation on version 9.5 of basex runs without errors: # ./basex95/bin/basex -d ~/staging/mssql-min.xq Aug. 27, 2021 12:25:49 PM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL WARNUNG: TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum. sql:row…</sql:row>
I think the warning about TSL negotiation can be ignored.
Am Fr., 27. Aug. 2021 um 11:38 Uhr schrieb Christian Grün christian.gruen@gmail.com:
Hi Artur,
Thanks for the full info view output.
The issue seems to be specific to MS SQL. Could you run basex on command line with the -d flag and share the output with us?
Thanks in advance Christian
On Fri, Aug 27, 2021 at 11:17 AM Artur Moor skynexion@gmail.com wrote:
Dear all,
When executing an SQL query with the SQL module: sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), let $connection := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") return sql:execute($connection, "SELECT * FROM TABLE")
I get the following error message: Error: Stopped at /Users/sa/staging/file, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen. Compiling:
- inline let $connection_0 := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd")
- simplify FLWOR expression: sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") ! sql:execute(., "SELECT * FROM TABLE")
Optimized Query: (sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") ! sql:execute(., "SELECT * FROM TABLE")) Query: sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), let $connection := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") return sql:execute($connection, "SELECT * FROM TABLE") Query Plan:
<QueryPlan compiled="true" updating="false"> <List type="item()*"> <SqlInit name="sql:init" type="empty-sequence()" size="0"> <Str type="xs:string" size="1">com.microsoft.sqlserver.jdbc.SQLServerDriver</Str> </SqlInit> <IterMap type="item()*"> <SqlConnect name="sql:connect" type="xs:integer" size="1"> <Str type="xs:string" size="1">jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME</Str> <Str type="xs:string" size="1">username</Str> <Str type="xs:string" size="1">pa$$w0rd</Str> </SqlConnect> <SqlExecute name="sql:execute" type="item()*"> <ContextValue type="xs:integer" size="1"/> <Str type="xs:string" size="1">SELECT * FROM TABLE</Str> </SqlExecute> </IterMap> </List> </QueryPlan>
Does anyone have an idea what the problem is?
Any hints are welcome!
Christian, thank you very much for your support!
Probably there is somebody who has a MS SQL database and can check the sql module in Basex 9.6.1.
Am Fr., 27. Aug. 2021 um 13:18 Uhr schrieb Christian Grün christian.gruen@gmail.com:
I think this is not the right direction because the error occurs only on the version 9.6 and 9.6.1 all previous versions work fine.
Thanks, that’s an important hint. I’ll try to reproduce this in a while (but it may take some time; I’ll be offline for a couple of days in September).
Am Fr., 27. Aug. 2021 um 12:57 Uhr schrieb Christian Grün christian.gruen@gmail.com:
return sql:execute($c, "SELECT * FROM TABL")
Some wild guesses:
• I assume that "TABL" is correct? • If your database contains a lot of entries: What happens if you request small results? • What happens if you execute "SELECT 1 + 1"? • Do you get the same error with sql:prepare and sql:execute-prepared? • You could check the web for "makeFromDriverError SQLServerException: The connection is closed"
I have currently no MS SQL instance running; maybe someone else has?
Cheers, Christian
# basex -d ~/staging/mssql-min.xq Aug. 27, 2021 12:16:21 PM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL WARNUNG: TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum. com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) org.basex.query.QueryException: An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.query.QueryError.get(QueryError.java:1429) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:123) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) ... 14 more org.basex.core.BaseXException: Stopped at /Users/sa/staging/mssql-min.xq, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.core.Command.execute(Command.java:94) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) Caused by: org.basex.query.QueryException: An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.query.QueryError.get(QueryError.java:1429) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:123) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) ... 5 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) ... 14 more Stopped at /Users/sa/staging/mssql-min.xq, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen.
Same invocation on version 9.5 of basex runs without errors: # ./basex95/bin/basex -d ~/staging/mssql-min.xq Aug. 27, 2021 12:25:49 PM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL WARNUNG: TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum. sql:row…</sql:row>
I think the warning about TSL negotiation can be ignored.
Am Fr., 27. Aug. 2021 um 11:38 Uhr schrieb Christian Grün christian.gruen@gmail.com:
Hi Artur,
Thanks for the full info view output.
The issue seems to be specific to MS SQL. Could you run basex on command line with the -d flag and share the output with us?
Thanks in advance Christian
On Fri, Aug 27, 2021 at 11:17 AM Artur Moor skynexion@gmail.com wrote:
Dear all,
When executing an SQL query with the SQL module: sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), let $connection := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") return sql:execute($connection, "SELECT * FROM TABLE")
I get the following error message: Error: Stopped at /Users/sa/staging/file, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen. Compiling:
- inline let $connection_0 := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd")
- simplify FLWOR expression: sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") ! sql:execute(., "SELECT * FROM TABLE")
Optimized Query: (sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") ! sql:execute(., "SELECT * FROM TABLE")) Query: sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), let $connection := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") return sql:execute($connection, "SELECT * FROM TABLE") Query Plan:
<QueryPlan compiled="true" updating="false"> <List type="item()*"> <SqlInit name="sql:init" type="empty-sequence()" size="0"> <Str type="xs:string" size="1">com.microsoft.sqlserver.jdbc.SQLServerDriver</Str> </SqlInit> <IterMap type="item()*"> <SqlConnect name="sql:connect" type="xs:integer" size="1"> <Str type="xs:string" size="1">jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME</Str> <Str type="xs:string" size="1">username</Str> <Str type="xs:string" size="1">pa$$w0rd</Str> </SqlConnect> <SqlExecute name="sql:execute" type="item()*"> <ContextValue type="xs:integer" size="1"/> <Str type="xs:string" size="1">SELECT * FROM TABLE</Str> </SqlExecute> </IterMap> </List> </QueryPlan>
Does anyone have an idea what the problem is?
Any hints are welcome!
…and I should have slowed down: I found a FindBug “bug fix” that, I believe, has caused the issue [1]. Could you give the latest snapshot a try[2]?
[1] https://github.com/BaseXdb/basex/commit/993d137f108461e4eec862640d23eb4ba98b... [2] https://files.basex.org/releases/latest/
On Fri, Aug 27, 2021 at 1:22 PM Artur Moor skynexion@gmail.com wrote:
Christian, thank you very much for your support!
Probably there is somebody who has a MS SQL database and can check the sql module in Basex 9.6.1.
Am Fr., 27. Aug. 2021 um 13:18 Uhr schrieb Christian Grün christian.gruen@gmail.com:
I think this is not the right direction because the error occurs only on the version 9.6 and 9.6.1 all previous versions work fine.
Thanks, that’s an important hint. I’ll try to reproduce this in a while (but it may take some time; I’ll be offline for a couple of days in September).
Am Fr., 27. Aug. 2021 um 12:57 Uhr schrieb Christian Grün christian.gruen@gmail.com:
return sql:execute($c, "SELECT * FROM TABL")
Some wild guesses:
• I assume that "TABL" is correct? • If your database contains a lot of entries: What happens if you request small results? • What happens if you execute "SELECT 1 + 1"? • Do you get the same error with sql:prepare and sql:execute-prepared? • You could check the web for "makeFromDriverError SQLServerException: The connection is closed"
I have currently no MS SQL instance running; maybe someone else has?
Cheers, Christian
# basex -d ~/staging/mssql-min.xq Aug. 27, 2021 12:16:21 PM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL WARNUNG: TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum. com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) org.basex.query.QueryException: An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.query.QueryError.get(QueryError.java:1429) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:123) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) ... 14 more org.basex.core.BaseXException: Stopped at /Users/sa/staging/mssql-min.xq, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.core.Command.execute(Command.java:94) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) Caused by: org.basex.query.QueryException: An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.query.QueryError.get(QueryError.java:1429) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:123) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) ... 5 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) ... 14 more Stopped at /Users/sa/staging/mssql-min.xq, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen.
Same invocation on version 9.5 of basex runs without errors: # ./basex95/bin/basex -d ~/staging/mssql-min.xq Aug. 27, 2021 12:25:49 PM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL WARNUNG: TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum. sql:row…</sql:row>
I think the warning about TSL negotiation can be ignored.
Am Fr., 27. Aug. 2021 um 11:38 Uhr schrieb Christian Grün christian.gruen@gmail.com:
Hi Artur,
Thanks for the full info view output.
The issue seems to be specific to MS SQL. Could you run basex on command line with the -d flag and share the output with us?
Thanks in advance Christian
On Fri, Aug 27, 2021 at 11:17 AM Artur Moor skynexion@gmail.com wrote: > > Dear all, > > When executing an SQL query with the SQL module: > sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), > let $connection := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") > return sql:execute($connection, "SELECT * FROM TABLE") > > I get the following error message: > Error: > Stopped at /Users/sa/staging/file, 3/19: > [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen. > Compiling: > - inline let $connection_0 := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") > - simplify FLWOR expression: sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") ! sql:execute(., "SELECT * FROM TABLE") > Optimized Query: > (sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") ! sql:execute(., "SELECT * FROM TABLE")) > Query: > sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), let $connection := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") return sql:execute($connection, "SELECT * FROM TABLE") > Query Plan: > <QueryPlan compiled="true" updating="false"> > <List type="item()*"> > <SqlInit name="sql:init" type="empty-sequence()" size="0"> > <Str type="xs:string" size="1">com.microsoft.sqlserver.jdbc.SQLServerDriver</Str> > </SqlInit> > <IterMap type="item()*"> > <SqlConnect name="sql:connect" type="xs:integer" size="1"> > <Str type="xs:string" size="1">jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME</Str> > <Str type="xs:string" size="1">username</Str> > <Str type="xs:string" size="1">pa$$w0rd</Str> > </SqlConnect> > <SqlExecute name="sql:execute" type="item()*"> > <ContextValue type="xs:integer" size="1"/> > <Str type="xs:string" size="1">SELECT * FROM TABLE</Str> > </SqlExecute> > </IterMap> > </List> > </QueryPlan> > > Does anyone have an idea what the problem is? > > Any hints are welcome!
The build https://files.basex.org/releases/latest/BaseX962-20210827.132409.zip runs without errors!
Am Fr., 27. Aug. 2021 um 13:26 Uhr schrieb Christian Grün christian.gruen@gmail.com:
…and I should have slowed down: I found a FindBug “bug fix” that, I believe, has caused the issue [1]. Could you give the latest snapshot a try[2]?
[1] https://github.com/BaseXdb/basex/commit/993d137f108461e4eec862640d23eb4ba98b... [2] https://files.basex.org/releases/latest/
On Fri, Aug 27, 2021 at 1:22 PM Artur Moor skynexion@gmail.com wrote:
Christian, thank you very much for your support!
Probably there is somebody who has a MS SQL database and can check the sql module in Basex 9.6.1.
Am Fr., 27. Aug. 2021 um 13:18 Uhr schrieb Christian Grün christian.gruen@gmail.com:
I think this is not the right direction because the error occurs only on the version 9.6 and 9.6.1 all previous versions work fine.
Thanks, that’s an important hint. I’ll try to reproduce this in a while (but it may take some time; I’ll be offline for a couple of days in September).
Am Fr., 27. Aug. 2021 um 12:57 Uhr schrieb Christian Grün christian.gruen@gmail.com:
return sql:execute($c, "SELECT * FROM TABL")
Some wild guesses:
• I assume that "TABL" is correct? • If your database contains a lot of entries: What happens if you request small results? • What happens if you execute "SELECT 1 + 1"? • Do you get the same error with sql:prepare and sql:execute-prepared? • You could check the web for "makeFromDriverError SQLServerException: The connection is closed"
I have currently no MS SQL instance running; maybe someone else has?
Cheers, Christian
# basex -d ~/staging/mssql-min.xq Aug. 27, 2021 12:16:21 PM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL WARNUNG: TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum. com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) org.basex.query.QueryException: An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.query.QueryError.get(QueryError.java:1429) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:123) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) ... 14 more org.basex.core.BaseXException: Stopped at /Users/sa/staging/mssql-min.xq, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.core.Command.execute(Command.java:94) at org.basex.api.client.LocalSession.execute(LocalSession.java:132) at org.basex.api.client.Session.execute(Session.java:36) at org.basex.core.CLI.execute(CLI.java:92) at org.basex.BaseX.<init>(BaseX.java:107) at org.basex.BaseX.main(BaseX.java:43) Caused by: org.basex.query.QueryException: An SQL exception occurred: Das Resultset wurde geschlossen. at org.basex.query.QueryError.get(QueryError.java:1429) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:123) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.IterMap$1.next(IterMap.java:75) at org.basex.query.QueryContext.next(QueryContext.java:347) at org.basex.query.expr.List$1.next(List.java:164) at org.basex.query.scope.MainModule$1.next(MainModule.java:104) at org.basex.core.cmd.AQuery.query(AQuery.java:107) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:93) ... 5 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Das Resultset wurde geschlossen. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.checkClosed(SQLServerResultSet.java:450) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:992) at org.basex.query.func.sql.SqlExecute$1.next(SqlExecute.java:84) ... 14 more Stopped at /Users/sa/staging/mssql-min.xq, 3/19: [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen.
Same invocation on version 9.5 of basex runs without errors: # ./basex95/bin/basex -d ~/staging/mssql-min.xq Aug. 27, 2021 12:25:49 PM com.microsoft.sqlserver.jdbc.TDSChannel enableSSL WARNUNG: TLSv1 was negotiated. Please update server and client to use TLSv1.2 at minimum. sql:row…</sql:row>
I think the warning about TSL negotiation can be ignored.
Am Fr., 27. Aug. 2021 um 11:38 Uhr schrieb Christian Grün christian.gruen@gmail.com: > > Hi Artur, > > Thanks for the full info view output. > > The issue seems to be specific to MS SQL. Could you run basex on > command line with the -d flag and share the output with us? > > Thanks in advance > Christian > > > On Fri, Aug 27, 2021 at 11:17 AM Artur Moor skynexion@gmail.com wrote: > > > > Dear all, > > > > When executing an SQL query with the SQL module: > > sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), > > let $connection := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") > > return sql:execute($connection, "SELECT * FROM TABLE") > > > > I get the following error message: > > Error: > > Stopped at /Users/sa/staging/file, 3/19: > > [sql:error] An SQL exception occurred: Das Resultset wurde geschlossen. > > Compiling: > > - inline let $connection_0 := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") > > - simplify FLWOR expression: sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") ! sql:execute(., "SELECT * FROM TABLE") > > Optimized Query: > > (sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") ! sql:execute(., "SELECT * FROM TABLE")) > > Query: > > sql:init("com.microsoft.sqlserver.jdbc.SQLServerDriver"), let $connection := sql:connect("jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME", "username", "pa$$w0rd") return sql:execute($connection, "SELECT * FROM TABLE") > > Query Plan: > > <QueryPlan compiled="true" updating="false"> > > <List type="item()*"> > > <SqlInit name="sql:init" type="empty-sequence()" size="0"> > > <Str type="xs:string" size="1">com.microsoft.sqlserver.jdbc.SQLServerDriver</Str> > > </SqlInit> > > <IterMap type="item()*"> > > <SqlConnect name="sql:connect" type="xs:integer" size="1"> > > <Str type="xs:string" size="1">jdbc:sqlserver://SQLHOST:1433;databaseName=DBNAME</Str> > > <Str type="xs:string" size="1">username</Str> > > <Str type="xs:string" size="1">pa$$w0rd</Str> > > </SqlConnect> > > <SqlExecute name="sql:execute" type="item()*"> > > <ContextValue type="xs:integer" size="1"/> > > <Str type="xs:string" size="1">SELECT * FROM TABLE</Str> > > </SqlExecute> > > </IterMap> > > </List> > > </QueryPlan> > > > > Does anyone have an idea what the problem is? > > > > Any hints are welcome!
Awesome.
On Fri, Aug 27, 2021 at 1:33 PM Artur Moor skynexion@gmail.com wrote:
The build https://files.basex.org/releases/latest/BaseX962-20210827.132409.zip runs without errors!
basex-talk@mailman.uni-konstanz.de
-
Artur Moor -
Christian Grün