Re: JDBC and ResultSet problem

From: Silvio Bierman (sbierman_at_idfix.nl)
Date: 03/14/05 

Date: Mon, 14 Mar 2005 09:21:41 +0100

Hello Lee,

Several MSSQL JDBC drivers employ the same scheme, they create multiple
connections transparantly and use those for the multiple rowsets. We where
extremely bitten by this behaviour once using the JTDS driver.

Where multiple statements on the same connection do not suffer from locking
issues caused by MSSQLs tendency to lock al SELECT-ed records statements on
seperate connections do. Also, transactions only work on single connections
so the begin-transaction/rollback/commit logic is broken by this also.

Luckily we found out about this quickly and where able to setup a different
driver that supported dynamic cursors fixing the problem. I think it is a
very dangerous default behaviour of a JDBC driver...

Regards,

Silvio Bierman

"Lee Fesperman" <firstsql@ix.netcom.com> wrote in message
news:42322255.5693@ix.netcom.com...
> Mark Matthews wrote:
> > Lee Fesperman wrote:
> > > Daniel Dittmar wrote:
> > >>
> > >>I think MySQL allowed/allows only one open cursor at a time. Reading
all
> > >>the data into memory was probably the easiest way to simulate multiple
> > >>open cursors in the driver.
> > >
> > > The MySQL protocol only allows this, but their current JDBC driver
provides several
> > > workarounds. Check the connection properties for
com.mysql.jdbc.JdbcDriver.
> >
> > Lee's correct. Back a long time ago, I implemented the JDBC driver to
> > cover the 80%+ use case, which was small-to-medium sized result sets
> > with multiple open cursors (the multiple open cursors part is required
> > by the JDBC spec), so therefore the JDBC driver does buffer the complete
> > result set in memory by default.
> >
> > ....
>
> Thanks, I was just filling in until you showed up.
>
> > (btw, many JDBC drivers for SQL Server, including the ones shipped by MS
> > actually do something similar, if you ask for another result set and are
> > using cursors to fetch them rather than 'firehose' mode, they actually
> > open _another_connection_ to the database!)
>
> Originally, the SQL Server protocol for both MS and Sybase only supported
'firehose'
> mode. Our web language, Jive (available under GPL at
http://www.firstsql.com/jive/),
> supports nested queries by opening a new (internal) connection for the
nested query just
> for compatibility with this oddity in SQL Server's ODBC driver (Jive
automatically
> tracks open connections and closes them when no longer needed). Since it's
GPL, you get
> the source and can see for yourself this 'magic' ;^)
>
> MS SQL Server now supports "server-side" cursors that doesn't use firehose
mode. I don't
> know if Sybase does yet.
>
> Note: Firehose mode can be more efficient because it avoid the response
overhead.
> However, it normally sends each row as a separate packet which adds its
own overhead.
> The driver for FirstSQL/J (see my sig) uses the setFetchSize() hint in
JDBC to batch
> multiple rows in a packet to give a kind of 'best of both worlds'
implementation. I'm
> sure drivers for other databases provide similar functionality.
>
> --
> Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
> ==============================================================
> * The Ultimate DBMS is here!
> * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

Relevant Pages

  • Re: JDBC and ResultSet problem
    ... > Several MSSQL JDBC drivers employ the same scheme, ... > connections transparantly and use those for the multiple rowsets. ... > extremely bitten by this behaviour once using the JTDS driver. ...
    (comp.lang.java.databases)
  • Re: JDBC and ResultSet problem
    ... > Silvio Bierman wrote: ... >> connections transparantly and use those for the multiple rowsets. ... >> extremely bitten by this behaviour once using the JTDS driver. ...
    (comp.lang.java.databases)
  • Best MS jdbc driver for jdbc 1.0 centric features
    ... performance from our jdbc driver - ... I have done to the way we use connections with this driver haven't ... support, distributed transactions or 2-phase commit support, RowSet ...
    (comp.lang.java.databases)
  • Re: System 6 six master display driver board
    ... connections between MPU board, display driver, driver and slave cables, and ... If you see dark stains between the pins, ... Vibration problem while you're playing game affecting digits could be due to ... bad connections between MPU and driver board- interruptions in the ...
    (rec.games.pinball)
  • Asus A8V-E Deluxe and USB ports not working proporly
    ... Cool & Quiet Utility v1.022 and AMD CPU Driver v1.2.2.0 in Windows. ... had it not been that I sometimes loose the connections to my mouse and ... the drive is connected through the front or the rear USB-ports. ...
    (alt.comp.periphs.mainboard.asus)