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 :=
<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)