You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@commons.apache.org by Hernan Silberman <hs...@pdi.com> on 2003/12/09 20:30:09 UTC
[DbUtils] Query/Update distinction...
I've been using DBUtils for a few weeks only so please excuse my green-ness...
I was wondering why this was possible in DBUtils without some sort of exception
being thrown:
//---------------------------------------------
QueryRunner theRunner = new QueryRunner(myDataSource);
theRunner.query( "delete * from sometable",
new Object[] {},
new ArrayListHandler() );
//---------------------------------------------
and similarly:
//---------------------------------------------
QueryRunner theRunner = new QueryRunner(myDataSource);
theRunner.update( "select somecol from sometable",
new Object[] {},
new ArrayListHandler() );
//---------------------------------------------
I looked through the DBUtils code and it seems that it relies on JDBC to throw a
SQLException when executeUpdate() is called on a statement representing
something other than an update, or when query() is called with something other
than a "select" statement:
/***
* Execute an SQL INSERT, UPDATE, or DELETE query.
*
* @param conn The connection to use to run the query.
* @param sql The SQL to execute.
* @param params The query replacement parameters.
* @return The number of rows updated.
* @throws SQLException
*/
public int update(Connection conn, String sql, Object[] params)
throws SQLException {
PreparedStatement stmt = null;
int rows = 0;
try {
stmt = this.prepareStatement(conn, sql);
this.fillStatement(stmt, params);
rows = stmt.executeUpdate();
} catch (SQLException e) {
this.rethrow(e, sql, params);
} finally {
DbUtils.close(stmt);
}
return rows;
}
The JDBC API docs for PreparedStatement.exectuteUpdate say to expect a
SQLException if the method is called on a Statement wrapping anything that
"produces a ResultSet object":
------------------------------------------------
public int executeUpdate(String sql) throws SQLException
Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE
statement or an SQL statement that returns nothing, such as an SQL DDL
statement.
Parameters:
sql - an SQL INSERT, UPDATE or DELETE statement or an SQL statement that returns
nothing
Returns: either the row count for INSERT, UPDATE or DELETE statements, or 0 for
SQL statements that return nothing
Throws: SQLException - if a database access error occurs or the given SQL
statement produces a ResultSet object
------------------------------------------------
So I'm puzzled that the code below, using JDBC directly, doesn't throw an
exception... the query theStmt is bound to is a select that definitely produces
a ResultSet:
String theSQL = "select somecol from sometable";
java.sql.PreparedStatement theStmt = theConnection.prepareStatement( theSQL );
theCount = theStmt.executeUpdate();
Seems like DbUtils is doing the right thing but the JDBC driver isn't throwing
the SQLException it said it should throw in this case?
I'm using Oracle 8.i and the Oracle OCI JDBC driver. I know this isn't a
DbUtils question per se, but I was wondering if this was something any of you
have encountered before, and if it's a simple mistake I'm making or a mistake
I've made interpreting the documentation above. I would expect to see an
exception if I tried to run a select as an update or an update as a query.
thanks!
Hernan
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org
Re: [DbUtils] Query/Update distinction...
Posted by Hernan Silberman <hs...@pdi.com>.
This is an Oracle bug. Fixed in v9.2.0.2 of the OCI driver:
http://oracle-docs.dartmouth.edu/dba-docs/patchset_9.2.0.2.htm#2258128
Apparently, it's okay to use this updated version of the OCI client / JDBC
driver with 8.1.7 databases. Look out for this one, I can imagine it being
a very high risk bug for many applications.
thanks...
Hernan
On Tue, 9 Dec 2003, Hernan Silberman wrote:
>
> I switched to the Oracle thin driver without changing my code and all of the
> appropriate exceptions are suddenly being thrown in my test cases.
>
> Very uncharacteristic of Oracle. I'll keep investigating this one.
>
> thanks...
> Hernan
>
>
>
> On Tue, 9 Dec 2003, David Graham wrote:
>
> > I don't use the Oracle driver so I don't know whether it has implemented
> > things properly. Other drivers I've used (JdbcOdbc Bridge, Postgres, DB2)
> > throw exceptions when executeUpdate or executeQuery are called with
> > incorrect types of SQL. DbUtils relies on the driver to be implemented
> > according to the JDBC spec so it doesn't and shouldn't check the SQL
> > before passing it along.
> >
> > David
> >
> > --- Hernan Silberman <hs...@pdi.com> wrote:
> > >
> > > I've been using DBUtils for a few weeks only so please excuse my
> > > green-ness...
> > >
> > > I was wondering why this was possible in DBUtils without some sort of
> > > exception
> > > being thrown:
> > >
> > > //---------------------------------------------
> > > QueryRunner theRunner = new QueryRunner(myDataSource);
> > >
> > > theRunner.query( "delete * from sometable",
> > > new Object[] {},
> > > new ArrayListHandler() );
> > > //---------------------------------------------
> > >
> > > and similarly:
> > >
> > > //---------------------------------------------
> > > QueryRunner theRunner = new QueryRunner(myDataSource);
> > >
> > > theRunner.update( "select somecol from sometable",
> > > new Object[] {},
> > > new ArrayListHandler() );
> > > //---------------------------------------------
> > >
> > > I looked through the DBUtils code and it seems that it relies on JDBC to
> > > throw a
> > > SQLException when executeUpdate() is called on a statement representing
> > > something other than an update, or when query() is called with something
> > > other
> > > than a "select" statement:
> > >
> > > /***
> > > * Execute an SQL INSERT, UPDATE, or DELETE query.
> > > *
> > > * @param conn The connection to use to run the query.
> > > * @param sql The SQL to execute.
> > > * @param params The query replacement parameters.
> > > * @return The number of rows updated.
> > > * @throws SQLException
> > > */
> > > public int update(Connection conn, String sql, Object[] params)
> > > throws SQLException {
> > > PreparedStatement stmt = null;
> > > int rows = 0;
> > >
> > > try {
> > > stmt = this.prepareStatement(conn, sql);
> > > this.fillStatement(stmt, params);
> > >
> > > rows = stmt.executeUpdate();
> > > } catch (SQLException e) {
> > > this.rethrow(e, sql, params);
> > > } finally {
> > > DbUtils.close(stmt);
> > > }
> > > return rows;
> > > }
> > >
> > > The JDBC API docs for PreparedStatement.exectuteUpdate say to expect a
> > > SQLException if the method is called on a Statement wrapping anything
> > > that
> > > "produces a ResultSet object":
> > >
> > > ------------------------------------------------
> > > public int executeUpdate(String sql) throws SQLException
> > >
> > > Executes the given SQL statement, which may be an INSERT, UPDATE, or
> > > DELETE
> > > statement or an SQL statement that returns nothing, such as an SQL DDL
> > > statement.
> > >
> > > Parameters:
> > > sql - an SQL INSERT, UPDATE or DELETE statement or an SQL statement that
> > > returns
> > > nothing
> > >
> > > Returns: either the row count for INSERT, UPDATE or DELETE statements,
> > > or 0 for
> > > SQL statements that return nothing
> > >
> > > Throws: SQLException - if a database access error occurs or the given
> > > SQL
> > > statement produces a ResultSet object
> > > ------------------------------------------------
> > >
> > >
> > > So I'm puzzled that the code below, using JDBC directly, doesn't throw
> > > an
> > > exception... the query theStmt is bound to is a select that definitely
> > > produces
> > > a ResultSet:
> > >
> > > String theSQL = "select somecol from sometable";
> > > java.sql.PreparedStatement theStmt = theConnection.prepareStatement(
> > > theSQL );
> > > theCount = theStmt.executeUpdate();
> > >
> > > Seems like DbUtils is doing the right thing but the JDBC driver isn't
> > > throwing
> > > the SQLException it said it should throw in this case?
> > >
> > > I'm using Oracle 8.i and the Oracle OCI JDBC driver. I know this isn't
> > > a
> > > DbUtils question per se, but I was wondering if this was something any
> > > of you
> > > have encountered before, and if it's a simple mistake I'm making or a
> > > mistake
> > > I've made interpreting the documentation above. I would expect to see
> > > an
> > > exception if I tried to run a select as an update or an update as a
> > > query.
> > >
> > > thanks!
> > > Hernan
> > >
> > >
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
> > > For additional commands, e-mail: commons-dev-help@jakarta.apache.org
> > >
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Free Pop-Up Blocker - Get it now
> > http://companion.yahoo.com/
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: commons-dev-help@jakarta.apache.org
> >
>
>
--
Hernan Silberman
PDI/Dreamworks
ext.29162 / 650-562-9162 / cell 415-810-5809
text pager: page-hsilberm@anim.dreamworks.com
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org
Re: [DbUtils] Query/Update distinction...
Posted by Hernan Silberman <hs...@pdi.com>.
I switched to the Oracle thin driver without changing my code and all of the
appropriate exceptions are suddenly being thrown in my test cases.
Very uncharacteristic of Oracle. I'll keep investigating this one.
thanks...
Hernan
On Tue, 9 Dec 2003, David Graham wrote:
> I don't use the Oracle driver so I don't know whether it has implemented
> things properly. Other drivers I've used (JdbcOdbc Bridge, Postgres, DB2)
> throw exceptions when executeUpdate or executeQuery are called with
> incorrect types of SQL. DbUtils relies on the driver to be implemented
> according to the JDBC spec so it doesn't and shouldn't check the SQL
> before passing it along.
>
> David
>
> --- Hernan Silberman <hs...@pdi.com> wrote:
> >
> > I've been using DBUtils for a few weeks only so please excuse my
> > green-ness...
> >
> > I was wondering why this was possible in DBUtils without some sort of
> > exception
> > being thrown:
> >
> > //---------------------------------------------
> > QueryRunner theRunner = new QueryRunner(myDataSource);
> >
> > theRunner.query( "delete * from sometable",
> > new Object[] {},
> > new ArrayListHandler() );
> > //---------------------------------------------
> >
> > and similarly:
> >
> > //---------------------------------------------
> > QueryRunner theRunner = new QueryRunner(myDataSource);
> >
> > theRunner.update( "select somecol from sometable",
> > new Object[] {},
> > new ArrayListHandler() );
> > //---------------------------------------------
> >
> > I looked through the DBUtils code and it seems that it relies on JDBC to
> > throw a
> > SQLException when executeUpdate() is called on a statement representing
> > something other than an update, or when query() is called with something
> > other
> > than a "select" statement:
> >
> > /***
> > * Execute an SQL INSERT, UPDATE, or DELETE query.
> > *
> > * @param conn The connection to use to run the query.
> > * @param sql The SQL to execute.
> > * @param params The query replacement parameters.
> > * @return The number of rows updated.
> > * @throws SQLException
> > */
> > public int update(Connection conn, String sql, Object[] params)
> > throws SQLException {
> > PreparedStatement stmt = null;
> > int rows = 0;
> >
> > try {
> > stmt = this.prepareStatement(conn, sql);
> > this.fillStatement(stmt, params);
> >
> > rows = stmt.executeUpdate();
> > } catch (SQLException e) {
> > this.rethrow(e, sql, params);
> > } finally {
> > DbUtils.close(stmt);
> > }
> > return rows;
> > }
> >
> > The JDBC API docs for PreparedStatement.exectuteUpdate say to expect a
> > SQLException if the method is called on a Statement wrapping anything
> > that
> > "produces a ResultSet object":
> >
> > ------------------------------------------------
> > public int executeUpdate(String sql) throws SQLException
> >
> > Executes the given SQL statement, which may be an INSERT, UPDATE, or
> > DELETE
> > statement or an SQL statement that returns nothing, such as an SQL DDL
> > statement.
> >
> > Parameters:
> > sql - an SQL INSERT, UPDATE or DELETE statement or an SQL statement that
> > returns
> > nothing
> >
> > Returns: either the row count for INSERT, UPDATE or DELETE statements,
> > or 0 for
> > SQL statements that return nothing
> >
> > Throws: SQLException - if a database access error occurs or the given
> > SQL
> > statement produces a ResultSet object
> > ------------------------------------------------
> >
> >
> > So I'm puzzled that the code below, using JDBC directly, doesn't throw
> > an
> > exception... the query theStmt is bound to is a select that definitely
> > produces
> > a ResultSet:
> >
> > String theSQL = "select somecol from sometable";
> > java.sql.PreparedStatement theStmt = theConnection.prepareStatement(
> > theSQL );
> > theCount = theStmt.executeUpdate();
> >
> > Seems like DbUtils is doing the right thing but the JDBC driver isn't
> > throwing
> > the SQLException it said it should throw in this case?
> >
> > I'm using Oracle 8.i and the Oracle OCI JDBC driver. I know this isn't
> > a
> > DbUtils question per se, but I was wondering if this was something any
> > of you
> > have encountered before, and if it's a simple mistake I'm making or a
> > mistake
> > I've made interpreting the documentation above. I would expect to see
> > an
> > exception if I tried to run a select as an update or an update as a
> > query.
> >
> > thanks!
> > Hernan
> >
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: commons-dev-help@jakarta.apache.org
> >
>
>
> __________________________________
> Do you Yahoo!?
> Free Pop-Up Blocker - Get it now
> http://companion.yahoo.com/
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-dev-help@jakarta.apache.org
>
--
Hernan Silberman
PDI/Dreamworks
ext.29162 / 650-562-9162 / cell 415-810-5809
text pager: page-hsilberm@anim.dreamworks.com
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org
Re: [DbUtils] Query/Update distinction...
Posted by David Graham <gr...@yahoo.com>.
I don't use the Oracle driver so I don't know whether it has implemented
things properly. Other drivers I've used (JdbcOdbc Bridge, Postgres, DB2)
throw exceptions when executeUpdate or executeQuery are called with
incorrect types of SQL. DbUtils relies on the driver to be implemented
according to the JDBC spec so it doesn't and shouldn't check the SQL
before passing it along.
David
--- Hernan Silberman <hs...@pdi.com> wrote:
>
> I've been using DBUtils for a few weeks only so please excuse my
> green-ness...
>
> I was wondering why this was possible in DBUtils without some sort of
> exception
> being thrown:
>
> //---------------------------------------------
> QueryRunner theRunner = new QueryRunner(myDataSource);
>
> theRunner.query( "delete * from sometable",
> new Object[] {},
> new ArrayListHandler() );
> //---------------------------------------------
>
> and similarly:
>
> //---------------------------------------------
> QueryRunner theRunner = new QueryRunner(myDataSource);
>
> theRunner.update( "select somecol from sometable",
> new Object[] {},
> new ArrayListHandler() );
> //---------------------------------------------
>
> I looked through the DBUtils code and it seems that it relies on JDBC to
> throw a
> SQLException when executeUpdate() is called on a statement representing
> something other than an update, or when query() is called with something
> other
> than a "select" statement:
>
> /***
> * Execute an SQL INSERT, UPDATE, or DELETE query.
> *
> * @param conn The connection to use to run the query.
> * @param sql The SQL to execute.
> * @param params The query replacement parameters.
> * @return The number of rows updated.
> * @throws SQLException
> */
> public int update(Connection conn, String sql, Object[] params)
> throws SQLException {
> PreparedStatement stmt = null;
> int rows = 0;
>
> try {
> stmt = this.prepareStatement(conn, sql);
> this.fillStatement(stmt, params);
>
> rows = stmt.executeUpdate();
> } catch (SQLException e) {
> this.rethrow(e, sql, params);
> } finally {
> DbUtils.close(stmt);
> }
> return rows;
> }
>
> The JDBC API docs for PreparedStatement.exectuteUpdate say to expect a
> SQLException if the method is called on a Statement wrapping anything
> that
> "produces a ResultSet object":
>
> ------------------------------------------------
> public int executeUpdate(String sql) throws SQLException
>
> Executes the given SQL statement, which may be an INSERT, UPDATE, or
> DELETE
> statement or an SQL statement that returns nothing, such as an SQL DDL
> statement.
>
> Parameters:
> sql - an SQL INSERT, UPDATE or DELETE statement or an SQL statement that
> returns
> nothing
>
> Returns: either the row count for INSERT, UPDATE or DELETE statements,
> or 0 for
> SQL statements that return nothing
>
> Throws: SQLException - if a database access error occurs or the given
> SQL
> statement produces a ResultSet object
> ------------------------------------------------
>
>
> So I'm puzzled that the code below, using JDBC directly, doesn't throw
> an
> exception... the query theStmt is bound to is a select that definitely
> produces
> a ResultSet:
>
> String theSQL = "select somecol from sometable";
> java.sql.PreparedStatement theStmt = theConnection.prepareStatement(
> theSQL );
> theCount = theStmt.executeUpdate();
>
> Seems like DbUtils is doing the right thing but the JDBC driver isn't
> throwing
> the SQLException it said it should throw in this case?
>
> I'm using Oracle 8.i and the Oracle OCI JDBC driver. I know this isn't
> a
> DbUtils question per se, but I was wondering if this was something any
> of you
> have encountered before, and if it's a simple mistake I'm making or a
> mistake
> I've made interpreting the documentation above. I would expect to see
> an
> exception if I tried to run a select as an update or an update as a
> query.
>
> thanks!
> Hernan
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-dev-help@jakarta.apache.org
>
__________________________________
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org