Wow, thanks for the comprehensive tests. Looking forward to your PR.
Am 01.02.2018 7:44 nachm. schrieb "Jan Dölle" basex-list@id1.de:
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%7Derror desc:"An SQL exception occurred: Values not bound to statement" code:Q{http://basex.org/modules/sql%7Dunexpected desc:"Unexpeced exception java.lang.ArrayIndexOutOfBoundsException (java.lang.ArrayIndexOutOfBoundsException: 1)"
mysql: code:Q{http://basex.org/modules/sql%7Derror desc:"An SQL exception occurred: No value specified for parameter 1" code:Q{http://basex.org/modules/sql%7Derror 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%7Derror desc:"An SQL exception occurred: Für den Parameter 1 wurde kein Wert angegeben." code:Q{http://basex.org/modules/sql%7Derror 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%7Derror desc:"An SQL exception occurred: Fehlender IN- oder OUT-Parameter auf Index:: 1" code:Q{http://basex.org/modules/sql%7Derror desc:"An SQL exception occurred: Ungültiger Spaltenindex"
--- test-script:
declare variable $params0 := sql:parameters </sql:parameters>;
declare variable $params2 := sql:parameters <sql:parameter type="int">1</sql:parameter> <sql:parameter type="int">2</sql:parameter> </sql:parameters>;
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-list@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-list@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)