Main Page » Mailing List » January 2018

SqlExecutePrepared, Oracle and getParameterCount again

Tuesday, 16 January 2018 · 4 messages · last reply 1 Feb 2018
JD
Jan Dölle16 Jan 2018 20:02 #
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 at 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)
CG
Christian Grün17 Jan 2018 13:03 #
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
Quoted text · Jan Dölle · 41 lines
On Tue, Jan 16, 2018 at 8:02 PM, Jan Dölle <basex-list at 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 at 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) > >
JD
Jan Dölle1 Feb 2018 19:44 #
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)
)
Quoted text · Christian Grün · 59 lines
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 at 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 at 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) >> >>
CG
Christian Grün1 Feb 2018 19:48 #
Wow, thanks for the comprehensive tests. Looking forward to your PR.
Quoted text · Jan Dölle · 1 line
Am 01.02.2018 7:44 nachm. schrieb "Jan Dölle" <basex-list at 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}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)
)
Quoted text · Jan Dölle · 64 lines
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 at 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 at 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) >> >> >>

Written for BaseX 8.6. BaseX may have changed since: please check the current documentation.

« Error: Processing instruction has illegal name: xmlHistorical reasons in the Database Module »
⚡Generated with XQuery