Re: [basex-talk] SqlExecutePrepared, Oracle and getParameterCount again
Hi Christian, I removed the parameter check and test on this databases with less and too much parameters: All except SQLite throw a SQLException. SQLite throws ArrayIndexOutOfBoundsException. So I will remove the parameter check, catch also all other exceptions and throw them as SQL_ERROR_X to avoid internal BaseX errors and assume it was related to the SQL processing. If you agree, I will prepare a pull request. Cheers, Jan --- sqlite: code:Q{http://basex.org/modules/sql}error desc:"An SQL exception occurred: Values not bound to statement" code:Q{http://basex.org/modules/sql}unexpected desc:"Unexpeced exception java.lang.ArrayIndexOutOfBoundsException (java.lang.ArrayIndexOutOfBoundsException: 1)" mysql: code:Q{http://basex.org/modules/sql}error desc:"An SQL exception occurred: No value specified for parameter 1" code:Q{http://basex.org/modules/sql}error desc:"An SQL exception occurred: Parameter index out of range (2 > number of parameters, which is 1)." postgresql: code:Q{http://basex.org/modules/sql}error desc:"An SQL exception occurred: Für den Parameter 1 wurde kein Wert angegeben." code:Q{http://basex.org/modules/sql}error desc:"An SQL exception occurred: Der Spaltenindex 2 ist außerhalb des gültigen Bereichs. Anzahl Spalten: 1." oracle: code:Q{http://basex.org/modules/sql}error desc:"An SQL exception occurred: Fehlender IN- oder OUT-Parameter auf Index:: 1" code:Q{http://basex.org/modules/sql}error desc:"An SQL exception occurred: Ungültiger Spaltenindex" --- test-script: declare variable $params0 := ; declare variable $params2 := 1 2 ; declare function local:query($prep, $params) { try { sql:execute-prepared($prep, $params) } catch * { prof:dump($err:code, "code:"), prof:dump($err:description, "desc:" ) } }; declare function local:test($conn, $sql) { let $prep := sql:prepare($conn, $sql) return ( local:query($prep, $params0), local:query($prep, $params2) ) }; sql:init("org.sqlite.JDBC"), let $conn := sql:connect("jdbc:sqlite:database.db") return ( local:test($conn, "select ? as v"), sql:close($conn) ) Am 17.01.2018 um 13:03 schrieb Christian Grün: Hi Jan, Could you check for us what happens if the parameter count check is skipped, and the wrong number of parameters is specified? What kind of errors are raised if a user tried to assign too few or too many parameters to a prepared update statement? I would tend to treat all SQL implementations as similar as possible. Maybe we could get rid of the explicit parameter count check – which already is very Oracle-specific – and try to include the check in the error handling of the SQLExecutePrepared.setParam function. Thanks in advance, Christian On Tue, Jan 16, 2018 at 8:02 PM, Jan Dölle wrote: Hi, I found any other issue on using Oracle SQL in BaseX by SqlExecutePrepared. It caused by stmt.getParameterMetaData().getParameterCount(). The current workaround ignores the thrown Exception and will continue the statement execution. But produce may open cursors on in the Database. So finally it will run into ora-1000 "Maximum open cursors exceeded." By totally avoiding the call of getParameterCount this issue will avoid. So I will provide an other fix. What do you prefer: a) check for "Oracle" in stmt.getConnection().getMetaData().getDatabaseProductName() and skip the parameter test at all or b) add an other option "checkParameters" (default true) like "timeout" and skip getParameterCount if it is set to false. I personal prefer a). Cheers, Jan -- Jan Dölle E-Mail: basex-l...@id1.de Telefon +49-69-244502-0 Home: www.id1.de Information Design One AG, Baseler Straße 10, 60329 Frankfurt am Main Registereintrag: Amtsgericht Frankfurt am Main, HRB 52596 Vorstand: Robert Peters, Aufsichtsrat: Christian Hecht (Vorsitz)
[basex-talk] BaseX 9.0 sql:prepare not working
Hi, the function sql:prepare it not longer working Best Regards Jan ( sql:init("org.sqlite.JDBC"), let $conn := sql:connect("jdbc:sqlite:database.db") let $prep := sql:prepare($conn, "select 'Nothing'") let $result := sql:execute-prepared($prep) return ( $result, sql:close($prep), sql:close($conn) ) ) -> Improper use? Potential bug? Your feedback is welcome: Contact: basex-talk@mailman.uni-konstanz.de Version: BaseX 9.0 beta Java: Oracle Corporation, 1.8.0_151 OS: Linux, amd64 Stack Trace: java.lang.NullPointerException at org.basex.util.Token.string(Token.java:87) at org.basex.query.func.sql.JDBCConnections.add(JDBCConnections.java:42) at org.basex.query.func.sql.SqlPrepare.item(SqlPrepare.java:27) at org.basex.query.func.sql.SqlPrepare.item(SqlPrepare.java:1) at org.basex.query.expr.ParseExpr.value(ParseExpr.java:71) at org.basex.query.expr.gflwor.Let$LetEval.next(Let.java:177) at org.basex.query.expr.gflwor.GFLWOR$1.next(GFLWOR.java:87) at org.basex.query.QueryContext.next(QueryContext.java:398) at org.basex.query.expr.List$1.next(List.java:119) at org.basex.query.QueryContext.next(QueryContext.java:398) at org.basex.query.scope.MainModule$1.next(MainModule.java:122) at org.basex.query.QueryContext.next(QueryContext.java:398) at org.basex.query.QueryContext.cache(QueryContext.java:620) at org.basex.query.QueryProcessor.cache(QueryProcessor.java:112) at org.basex.core.cmd.AQuery.query(AQuery.java:86) 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.gui.GUI.exec(GUI.java:430) at org.basex.gui.GUI.lambda$4(GUI.java:373) at java.lang.Thread.run(Thread.java:748) -- Jan Dölle E-Mail: basex-l...@id1.de Telefon +49-69-244502-0 Home: www.id1.de Information Design One AG, Baseler Straße 10, 60329 Frankfurt am Main Registereintrag: Amtsgericht Frankfurt am Main, HRB 52596 Vorstand: Robert Peters, Aufsichtsrat: Christian Hecht (Vorsitz)
[basex-talk] SqlExecutePrepared, Oracle and getParameterCount again
Hi, I found any other issue on using Oracle SQL in BaseX by SqlExecutePrepared. It caused by stmt.getParameterMetaData().getParameterCount(). The current workaround ignores the thrown Exception and will continue the statement execution. But produce may open cursors on in the Database. So finally it will run into ora-1000 "Maximum open cursors exceeded." By totally avoiding the call of getParameterCount this issue will avoid. So I will provide an other fix. What do you prefer: a) check for "Oracle" in stmt.getConnection().getMetaData().getDatabaseProductName() and skip the parameter test at all or b) add an other option "checkParameters" (default true) like "timeout" and skip getParameterCount if it is set to false. I personal prefer a). Cheers, Jan -- Jan Dölle E-Mail: basex-l...@id1.de Telefon +49-69-244502-0 Home: www.id1.de Information Design One AG, Baseler Straße 10, 60329 Frankfurt am Main Registereintrag: Amtsgericht Frankfurt am Main, HRB 52596 Vorstand: Robert Peters, Aufsichtsrat: Christian Hecht (Vorsitz)
[basex-talk] Some timeDate arithmetic fail
Hi, please try this: xs:dateTime("2017-07-07T18:30:00.1") - xs:dayTimeDuration("PT1S") = 2017-07-07T18:30:59.1 ? or that: xs:dateTime("2017-07-07T18:00:59.1") - xs:dayTimeDuration("PT1M") = 2017-07-07T18:00:59.1 ? I think, there is a bug :( Best Regards Jan -- Jan Dölle E-Mail: basex-l...@id1.de Telefon +49-69-244502-0 Home: www.id1.de Information Design One AG, Baseler Straße 10, 60329 Frankfurt am Main Registereintrag: Amtsgericht Frankfurt am Main, HRB 52596 Vorstand: Robert Peters, Aufsichtsrat: Christian Hecht (Vorsitz)
Re: [basex-talk] Strange behavior while adding existing elements into new element.
Hi Christian, update {} as "quick copy" work. Beside I checked the $sequence-of-elements for the special situation and it only contains elements from a document created by parse-xml. But the function which creates the sequence compares document elements against some database elements and makes a choice to pick elements from document or either db. Thanks for your help Jan Am 20.06.2017 um 20:21 schrieb Christian Grün: Hi Jan, This reminds me of an open GitHub issue [1], but it’s just a guess. Does your query work as expected if you append "update { }" to your newly constructed element? let $new-element := element el { $sequence-of-elements } update { } In BaseX, there are two different types of nodes: fragment nodes (which are created by XQuery node constructors) and database nodes (which are compact representations of XML trees). In your example, the $sequence-of-elements (which, I guess, is a sequence of database nodes) will be wrapped with an element constructor. By using "update {}", your node will be copied and transformed to a database node. See [2] for some more details on the update keyword. Quite obviously this is just a workaround to test how your query behaves. If it behaves correctly, I am pretty sure that [1] would need to be fixed to get your query running correctly. Cheers, Christian [1] https://github.com/BaseXdb/basex/issues/919 [2] http://docs.basex.org/wiki/Update#update On Tue, Jun 20, 2017 at 8:09 PM, Jan Dölle wrote: Hi, just before you asking me. No we wasn't to isolate the Problem for now. Working on a complex transaction with database involved, we get the following phenomenon. declare function m:paths($el as element()) as xs:string* { distinct-values( for $e in $item/descendant::* return string-join($e/ancestor-or-self::*/local-name(.), "/") ) }; This function should produce a list of paths to all child of $el. And it works! But sometimes we get some strange results. We created new element constructed from others, may existing, elements. let $sequence-of-elements := collect-some-elements($from-somewhere) let $new-element := element el { $sequence-of-elements } return paths($new-element) Most of the time we get as expected results like: el/c1 el/c1/c12 el/c2 el/c2/c21 el/c2/c22 el/.. But sometimes it looks like that parents of the copied elements are not set correctly: oldParentOfc1/c1 oldParentOfc1/c1/c12 otherOld/ParentOfc2/c2 otherOld/ParentOfc2/c2/c21 otherOld/ParentOfc2/c2/c22 ... If we do a copy of the result like parse-xml(serialize($new-element))/* and call paths() again, everything looks now as expected. We call the function what products such results, isolated within a test-script with exact the same inputs. But suddenly the result was correct. Then we copied the inputs of the function within the integration (parse-xml, serialize). But it failed again. Also notable is, that the "bad result" is stable. It produces always the same result on same inputs (no random). So we have no idea to isolate the issue. Any suggestions? Best Regards Jan -- Jan Dölle E-Mail: basex-l...@id1.de Telefon +49-69-244502-0 Home: www.id1.de Information Design One AG, Baseler Straße 10, 60329 Frankfurt am Main Registereintrag: Amtsgericht Frankfurt am Main, HRB 52596 Vorstand: Robert Peters, Aufsichtsrat: Christian Hecht (Vorsitz)
[basex-talk] Strange behavior while adding existing elements into new element.
Hi, just before you asking me. No we wasn't to isolate the Problem for now. Working on a complex transaction with database involved, we get the following phenomenon. declare function m:paths($el as element()) as xs:string* { distinct-values( for $e in $item/descendant::* return string-join($e/ancestor-or-self::*/local-name(.), "/") ) }; This function should produce a list of paths to all child of $el. And it works! But sometimes we get some strange results. We created new element constructed from others, may existing, elements. let $sequence-of-elements := collect-some-elements($from-somewhere) let $new-element := element el { $sequence-of-elements } return paths($new-element) Most of the time we get as expected results like: el/c1 el/c1/c12 el/c2 el/c2/c21 el/c2/c22 el/.. But sometimes it looks like that parents of the copied elements are not set correctly: oldParentOfc1/c1 oldParentOfc1/c1/c12 otherOld/ParentOfc2/c2 otherOld/ParentOfc2/c2/c21 otherOld/ParentOfc2/c2/c22 ... If we do a copy of the result like parse-xml(serialize($new-element))/* and call paths() again, everything looks now as expected. We call the function what products such results, isolated within a test-script with exact the same inputs. But suddenly the result was correct. Then we copied the inputs of the function within the integration (parse-xml, serialize). But it failed again. Also notable is, that the "bad result" is stable. It produces always the same result on same inputs (no random). So we have no idea to isolate the issue. Any suggestions? Best Regards Jan -- Jan Dölle E-Mail: basex-l...@id1.de Telefon +49-69-244502-0 Home: www.id1.de Information Design One AG, Baseler Straße 10, 60329 Frankfurt am Main Registereintrag: Amtsgericht Frankfurt am Main, HRB 52596 Vorstand: Robert Peters, Aufsichtsrat: Christian Hecht (Vorsitz)
[basex-talk] BaseX 8.6: commands scripts (bxs) within GUI is not longer working
Hi List, thank for 8.6! But, it looks like that commands scripts (bxs) not longer working within GUI. Command line mode is fine. Jan Command: EXECUTE 1+2 Error: Improper use? Potential bug? Your feedback is welcome: Contact: basex-talk@mailman.uni-konstanz.de Version: BaseX 8.6 Java: Oracle Corporation, 1.8.0_121 OS: Linux, amd64 Stack Trace: java.lang.NullPointerException at org.basex.query.QueryInfo.toString(QueryInfo.java:110) at org.basex.core.cmd.AQuery.query(AQuery.java:105) at org.basex.core.cmd.XQuery.run(XQuery.java:22) at org.basex.core.Command.run(Command.java:257) at org.basex.core.cmd.Execute.run(Execute.java:54) at org.basex.core.Command.run(Command.java:257) at org.basex.core.Command.execute(Command.java:95) at org.basex.gui.GUI.exec(GUI.java:471) at org.basex.gui.GUI.access$300(GUI.java:44) at org.basex.gui.GUI$6.run(GUI.java:413) Compiling: - pre-evaluating (1 + 2) Optimized Query: 3 Query: 1+2 Query plan:
[basex-talk] Preparing BaseX 8.5.4 (please don't forget Issue #1341)
Hi, we noticed you prepare release 8.5.4. Please don't forget to fix issue: SQL with BaseX 8.5.3 #1341 Thx Jan -- Jan Dölle E-Mail: basex-l...@id1.de Telefon +49-69-244502-0 Home: www.id1.de Information Design One AG, Baseler Straße 10, 60329 Frankfurt am Main Registereintrag: Amtsgericht Frankfurt am Main, HRB 52596 Vorstand: Robert Peters, Aufsichtsrat: Christian Hecht (Vorsitz)
[basex-talk] Starting long running jobs from a command script (bxs) block other command scripts
Hi, I'm starting a job within a command script (startup.bxs). The job is a worker running the whole life of the application. It is not possible to run an other command script (shutdown.bxs) as long the job hasn't terminated. Calling xquery-scripts (shutdown.xq) will be possible anyway. Any Idea what's blocking the shutdown.bxs command script? Thank in advanced Jan --- startup.bxs: Q{java:java.lang.System}setProperty("xq.machine.shutdown", "false") trace("Hello") shutdown.bxs (not able to run after startup.bxs): Q{java:java.lang.System}setProperty("xq.machine.shutdown", "true") shutdown.xq (will work): Q{java:java.lang.System}setProperty("xq.machine.shutdown", "true") -- Jan Dölle E-Mail: basex-l...@id1.de Telefon +49-69-244502-0 Home: www.id1.de Information Design One AG, Baseler Straße 10, 60329 Frankfurt am Main Registereintrag: Amtsgericht Frankfurt am Main, HRB 52596 Vorstand: Robert Peters, Aufsichtsrat: Christian Hecht (Vorsitz)