Main Page » Mailing List » August 2021

Error executing an SQL query using «SQL Module»

Friday, 27 August 2021 · 10 messages

Artur Moor27 Aug 2021 11:17 #

*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 Grün27 Aug 2021 11:37 #

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

Quoted text · Artur Moor · 43 lines

On Fri, Aug 27, 2021 at 11:17 AM Artur Moor <skynexion at 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!

Artur Moor27 Aug 2021 12:29 #

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 at gmail.com>:

Quoted text · Christian Grün · 55 lines

> > 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 at 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 Grün27 Aug 2021 12:57 #

Quoted text · Artur Moor · 1 line

> 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

Quoted text · Artur Moor · 147 lines

> # 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 at 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 at 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!

Artur Moor27 Aug 2021 13:16 #

Hi, Christian,