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!