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 <basex-l...@id1.de> 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 <basex-l...@id1.de> 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)
