AW: [c-jdbc] Differences in behaviour of JDBC drivers

["Madl Alfred" <A.Madl@xxxxxxxxxxx>]

Hi Emmanuel !

Just wondering how you use "cursors" in C-JDBC without knowing the
differences in their handling by different JDBC drivers !?

If you do not use cursors correctly / necessary by the driver vendor,
you are in BIG TROUBLE !

Just assume the following scenario:

Your user / application does a "search" for "customers" in the database.
You present the first 25 lines of the result and some paging-links for
the next pages. OK ?

Lets say you have one million customers.

Now the user searches for "...customer.name like 'A%'..." and about
70.000 customers would qualify. So you do the query via JDBC without a
cursor and the JDBC driver will LOAD ALL qualified records into memory
at once !!! You read 26 of them (to now that there are more then 25) and
throw the rest away ?

Having some hundred users doing that concurrently...and C-JDBC/the
application is dead !

Using cursors and a fetchsize this desaster can be avoided...but it must
be done depending on the implementation of the JDBC driver ! In a
hetereogenous DB cluster this must be handled by C-JDBC for every DB
driver vendor differently !

Just my 2 cents.

Alfred


-----Ursprüngliche Nachricht-----
Von: Emmanuel Cecchet [mailto:Emmanuel.Cecchet@xxxxxxxxxxxx] 
Gesendet: Freitag, 30. Jänner 2004 19:16
An: Madl Alfred
Cc: c-jdbc@xxxxxxxxxxxxx; Raicevic Igor; ObjectWeb Architecture
Betreff: Re: [c-jdbc] Differences in behaviour of JDBC drivers


Hi Alfred,

> Because we have more and more issues with differences in the behaviour
> of JDBC drivers of different vendors I wanted to ask you, if you have 
> the same issues in C-JDBC.
>
For issues concerning JDBC driver URLs, it is up to the C-JDBC user to 
provide the correct URL in its configuration file. For example, with 
MySQL some of them add extra parameters in the JDBC URL for automatic 
reconnection of MySQL specific features.
As we require mostly only JDBC 1.0 features from the underlying driver, 
we have few issues. Some Sybase driver were not working properly but we 
don't have a lot of issues.

> We have identified several issues like that between all usual JDBC
> drivers of PostGres, MySQL, McKoi, Hypersonic, SQLServer, Oracle, DB2,

> Informix? in our projects ?Octopus? and ?DODS?.
>
> What are your experiences in this area ?
>
The mains problems arise when we try to fetch the database schema. Some 
drivers don't implement the feature or do not really implement what is 
told in the JDBC spec.

> Would it make sense to share something like ?common JDBC configuration
> files? which specify the differences (like 
> ?callSetCursorName=true/false?) between JDBC drivers which then could 
> be used at runtime to do things correctly ? We currently have 
> something like that based on XML in Octopus and DODS, would like to 
> merge their configs and are looking out for people having to same 
> issues to cooperate. Maybe a very small but shared ?JDBC 
> chameleon?-project just to contain a common XML based list of switches

> for different JDBC drivers ?
>
That would be definetely a great resource to have this kind of 
information somewhere. At the moment, we are completely relying on 
Octopus to manage database specific stuff when replicating databases. 
After that, we just use simple JDBC calls that are correctly implemented

by all drivers.

> Emmanuel: Is this something to discuss on the architecture/college 
> list ?
>
We can definetely crosspost this conversation on the architecture list. 
If we want to submit a small project on ObjectWeb, we can then contact 
the college.

Emmanuel

-- 
************************************************
Emmanuel Cecchet
Research Scientist, INRIA Rhône-Alpes
SARDES Project (http://sardes.inrialpes.fr)
phone : +33 (0)4 76 61 52 48 (GMT+1)
fax   : +33 (0)4 76 61 52 52
************************************************

Attachment: smime.p7s
Description: S/MIME cryptographic signature