Re: jdbc, use of nested ResultSet loops.(longish, interesting I hope)

Quoting "bmatthewtaylor(at)yahoo(dot)co(dot)uk" <bmatthewtaylor(at)yahoo(dot)co(dot)uk>:

> A few days ago I posted a query regarding a problem I had with errors I
> was
> incurring doing some nested queries with the jdbc driver. (having
> recently
> done a re-install my email history is in limbo for the moment)
>
> Found some of my problems were in messy ugly code, fixed some of the
> problems by creating a new connection for the loop that was throwing
> errors.
>
> Still curious on what would be 'good' style to use.
>
> Curious if anyone knows what can/should work and if this varies with
> jdbc/odbc drivers. I assume the jdbc standards dictate certain behaviour
>
> for jdbc conformance, is there a spec I should be reading? Does the
> conformance of drivers with the spec vary widely from one implementation
> to
> another?? (I'm interested in the differences between the Oracle
> classes12
> and Postgresql-jdbc drivers specifically but general comments sought)

The JDBC Specifications (1.2, 2.0 and soon 3.0 [May I was told yesterday]).
Look on http://java.sun.com

> pseudocode examples
> 1. Non nested loops.
> get connection
> create statement object.
>
> use statement object to create ResultSet object using SQL query.
> scroll thru ResulSet object to retrieve data.
> close ResultSet object.
>
> use statement object to create ResultSet object using SQL query.
> scroll thru ResulSet object to retrieve data.
> close ResultSet object.
>
> .... repeat as many times as required.

Perfectly fine. Technically you can't have two ResultSet's open from one
Statement, but you may reuse Statement once the ResultSet's been closed. In
fact the specs say that Statement should implicitly close it's open ResultSet
before returning the new one.

> close statement object
> close database connection object.
>
> 2. Nested loops, I use this format when I'm say printing a record of
> information to a table, and need to present a combo box selection (inner
>
> nested loop) that can only be generated once the row data is known
> (outer
> loop).
>
> get connection
> create statement object A.
> create statement object B.
>
> use statement object A to create ResultSet object AA using SQL query.
> scroll thru ResulSet object AA to retrieve data.
>
> use statement object B to create ResultSet object BB using SQL query.
> scroll thru ResulSet object BB to retrieve data.
> close ResultSet object BB.
>
>
> close ResultSet object AA.
>
> close statement object
> close database connection object.

Perfectly fine according to the specs. You can have as many
Statement/PreparedStatement/CallableStatement objects open at any one time.

Some drivers have problems because they don't manage the network protocol
correctly and get in a mess. Not sure about Oracle (I use 8i) but PostgreSQL
works fine here (see the Thread Safety tests in the source).

>
> I found (by trial and error, and a little commonsense)
> - nested Resultsets do not seem to be the way to go. (couldn't get this
> style to work with Oracle classes12 jdbc driver)

Hmmm, what version are you using of oracle's classes? I think this is one point
we beat them then ;-)

> - Using multiple statements off the one database connection seems to
> work OK.
>
> Other 'thing' I picked up was that Prepared Statements are supposed to
> be
> much faster (by 20-40%) according to the sun jdbc API reference book.

Yes, except we currently implement them in the driver. When the backend handles
prepared statements, the performance boost will suddenly appear. The boost is
where the Query Planner precompiles the query once, then the plan is executed
for each update. Currently the plan is compiled for each call.

>
> Comments anyone??
>
> brief code example below.
>
>
> (this code compiled and ran OK, you may need to change to suit your
> postgres datafile name.)
>
> import java.sql.*;
> import java.util.*;
> import java.io.*;
> import java.lang.*;
> import javax.servlet.*;
> import javax.servlet.http.*;
> /*
> Adds forecast to nominated project.
> */
> public class simpleExample extends HttpServlet {

Ooo Servlets, started playing with them yesterday ;-)

[snip]

Peter

--
Peter Mount peter(at)retep(dot)org(dot)uk
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/