Thread: JDBC3 and 7.4.1

JDBC3 and 7.4.1

From
Ranjeet Kapur
Date:
Hi,
 
We just moved our database frorm the windows port of 7.3.1 to 7.4.1 on Linux. We have a java application accessing the database. After doing the port, recreating the database(using my db creation scripts) and the values (using pg_dump), changing the JDBC driver I could connect to the DB (using my app), add data, but selects would return 0 result sets (no exceptions thown). The same selects using psql and pgAdmin work perfectly. I am using the JDBC3 driver for 7.4.1.
 
Jus for fun I loaded up 7.3.1 on another linux machine, recreated the DB (using pg_dump of the 7.4.1 DB) and psql, changed drivers to JDBC3 for 7.3.1, pointed my app to this DB and everything worked great.
 
Here is a snippet of code which does a select and checks to see the select size.
 
Statement select_stmnt = conn.createStatement();
             String query = "SELECT CODIGO_INTERNO, NOMBRE_PRODUCTO, PRECIO, CATEGORIA FROM INVENTARIO.INFO_PRODUCTO_TABLA " +
                            "WHERE CODIGO_INTERNO ILIKE '" + codigo + "%'" + " ORDER BY CODIGO_INTERNO ASC";
             
             ResultSet rs = select_stmnt.executeQuery(query);
             System.out.println("Select Size = " + rs.getFetchSize());
             num_entries = rs.getFetchSize();
 
Am I going nuts ??? Any help would be greatly appreciated.
 
Thanks
Ranjeet
 
 


Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.

Re: JDBC3 and 7.4.1

From
Kris Jurka
Date:

On Fri, 20 Feb 2004, Ranjeet Kapur wrote:

> Hi,
>
>              ResultSet rs = select_stmnt.executeQuery(query);
>              System.out.println("Select Size = " + rs.getFetchSize());
>              num_entries = rs.getFetchSize();
>

getFetchSize does not return the number of result rows.  It did prior to
7.4, but that was just an implementation artifact and should not have been
relied on.

Kris Jurka



Re: JDBC3 and 7.4.1

From
Ranjeet Kapur
Date:
Thanks, I just did a few experiments and  found the same thing, that getFetchSize() was returning 0. Is there anything else I could use to achieve the same result, namely how many rows in the select ?? I am very new to DB programming that�s why I probably misread the documentation on getFetchSize().
 
Ranjeet


Kris Jurka <books@ejurka.com> wrote:


On Fri, 20 Feb 2004, Ranjeet Kapur wrote:

> Hi,
>
> ResultSet rs = select_stmnt.executeQuery(query);
> System.out.println("Select Size = " + rs.getFetchSize());
> num_entries = rs.getFetchSize();
>

getFetchSize does not return the number of result rows. It did prior to
7.4, but that was just an implementation artifact and should not have been
relied on.

Kris Jurka



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.

Re: JDBC3 and 7.4.1

From
Oliver Jowett
Date:
Ranjeet Kapur wrote:
> Thanks, I just did a few experiments and  found the same thing, that
> getFetchSize() was returning 0. Is there anything else I could use to
> achieve the same result, namely how many rows in the select ?? I am very
> new to DB programming that´s why I probably misread the documentation on
> getFetchSize().

Add a COUNT(*) to the query, if you can; then the count will appear as a
column in your resultset.

Alternatively:

   Statement stmt =
connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
   ResultSet rs = stmt.executeQuery(query);

   // Count rows.
   rs.last();
   int resultSetSize = rs.getRow();

   // Process actual resultset
   rs.beforeFirst();
   while (rs.next()) {
     // do stuff
   }

but this requires iterating over the resultset twice, and using
TYPE_SCROLL_INSENSITIVE prevents the current driver from using cursors
to incrementally fetch data, so you will have the entire resultset in
memory on the Java side.

If you don't need the total count before processing the resultset, just
count as you process each result row (then you don't need a
TYPE_SCROLL_INSENSITIVE ResultSet, either).

-O