Bugzilla Version 2.16.7
Bugzilla Bug 1741
  Database.getConnection()
     Query page      Enter new bug
Bug#: 1741   Platform:   Reporter: martin-fuchs at gmx dot net (Martin Fuchs)
Product:   OS:   Add CC:
Component:   Version:   CC:
Status: NEW   Priority:  
Resolution:   Severity:  
Assigned To: ferret at frii dot com (Bruce Snyder)   Target Milestone:  
URL:
Summary:

Attachment Type Modified Status Actions
Implementation of Database.getJdbcConnection() patch 2004-09-14 09:40 none Edit
Patch for org.exolab.castor.jdo.Database interface patch 2004-09-15 01:17 none Edit
Create a New Attachment (proposed patch, testcase, etc.) View All

Bug 1741 depends on: Show dependency tree
Show dependency graph
Bug 1741 blocks:
Votes: 0    Show votes for this bug    Vote for this bug

Additional Comments:


Leave as NEW 
Accept bug (change status to ASSIGNED)
Resolve bug, changing resolution to
Resolve bug, mark it as duplicate of bug #
Reassign bug to
Reassign bug to owner of selected component

View Bug Activity   |   Format For Printing

Description: Opened: 2004-09-14 09:39

While looking through in Castor's source code the following question
hit me one more time: Why is it made so difficult to retrieve the
underlying JDBC connections from Castor?

Yes, I saw the warning "Only for internal / advanced use !" in 
DatabaseImplgetConnection().
But some applications need access to the JDBC connection for example
to set some DB parameters at session initialization.

I see everywhere this construct:

Database  db = jdo.getDatabase();
db.begin();
Connection conn = (Connection) ((DatabaseImpl)db).getConnection();

A bit complicated just to get a connection object, if you ask me.

First: Why is the cast to DatabaseImpl necessary?
Well, it's needed because getConnection() is only present in DatabaseImpl,
not in the Database interface. What about declaring it in the Database
interface, perhaps with ajusted name getJdbcConnection() and
the return type java.sql.Connection ?
Any other derived database implementation could just return null,
if it doesn't use a JDBC connection. Well - will there ever be a database
implementation in Java, which doesn't use JDBC? I doubt.

Second: Why is the cast to Connection necessary?
DatabaseImpl.getConnection() simply calls TransactionContextImpl.getConnection
(),
which in any case returns a JDBC Connection object. Is there the
possibility to return any another type?

I would propose to change the return type of the getConnection() functions
from Object to java.sql.Connection. 

What do you think?

------- Additional Comment #1 From Martin Fuchs 2004-09-14 09:40 -------
Created an attachment (id=662)
Implementation of Database.getJdbcConnection()

------- Additional Comment #2 From Bruce Snyder 2004-09-15 01:08 -------
This one time, at band camp, Martin Fuchs said:

MF>while looking through in Castor's source code the following question
MF>hit me one more time: Why is it made so difficult to retrieve the
MF>underlying JDBC connections from Castor?
MF>
MF>Yes, I saw the warning "Only for internal / advanced use !" in
DatabaseImplgetConnection().
MF>But some applications need access to the JDBC connection for example
MF>to set some DB parameters at session initialization.
MF>
MF>I see everywhere this construct:
MF>
MF>Database  db = jdo.getDatabase();
MF>db.begin();
MF>Connection conn = (Connection) ((DatabaseImpl)db).getConnection();
MF>
MF>A bit complicated just to get a connection object, if you ask me.
MF>
MF>First: Why is the cast to DatabaseImpl necessary?
MF>Well, it's needed because getConnection() is only present in DatabaseImpl,
MF>not in the Database interface. What about declaring it in the Database
MF>interface, perhaps with ajusted name getJdbcConnection() and
MF>the return type java.sql.Connection ?
MF>Any other derived database implementation could just return null,
MF>if it doesn't use a JDBC connection. Well - will there ever be a database
MF>implementation in Java, which doesn't use JDBC? I doubt.

I'm not sure why the getConnection() method is not exposed in the
Database interface. Proabably just an oversight more than anything.
I have no problem exposing this method via the Database interface
but the name cannot change and here's why. All connections in Castor
are not always of type java.sql.Connection. There have always been
plans to make Castor support LDAP persistence. Currently it only
supports querying and marshalling the results to XML. I'm about to
start to work on adding LDAP persistence to Castor to leaving the
getConnection() method signature the way it is currently designed
is important.

MF>Second: Why is the cast to Connection necessary?
MF>DatabaseImpl.getConnection() simply calls TransactionContextImpl.getConnection(),
MF>which in any case returns a JDBC Connection object. Is there the
MF>possibility to return any another type?

The cast to java.sql.Connection is explained above. Castor has
always had plans to connected to data sources other than relational
databases. By allowing this method call to return a java.lang.Object,
we're allowing other types of connections to be returned.

MF>I would propose to change the return type of the getConnection() functions
MF>from Object to java.sql.Connection.

I disagree here and have explained the reaasons for this above.

MF>What do you think?

I've been pondering this myself over the last month or so. Especially
in just the last couple of days. There are many applications that
need access to the JDBC connection simply to set parameters on it
(e.g. batching, holdability, read-only, transaction isolation
(especially this one!), etc.). The conclusion I've come to is that
we should expose such parameters via method calls. This brings up
another issue, however. We can't place JDBC-specific method calls
on the Database interface. Because of this, I've been thinking about
creating a new interface model like so:

                 +------------+
                 | DataSource |
                 +------------+
                       #
                       |
          +------------+------------+
          |                         |
 +----------------------+   +----------------+
 | RelationalDataSource |   | LdapDataSource |
 +----------------------+   +----------------+
           .                           .
          /_\                         /_\
           |                           |
           |                           |
 +--------------------------+   +--------------------+
 | RelationalDataSourceImpl |   | LdapDataSourceImpl |
 +--------------------------+   +--------------------+

For a description of this ASCII UML, please see the following doc:

    http://c2.com/cgi/wiki?UmlAsciiArt

In the diagram above, the DataSource interface would replace the
current Database interface. The second layer allows any additional
DataSource type to be added (JCA connectors to AS/400, etc.). The
third layer is a replacemet for the current DatabaseImpl.

Bear in mind that changes this significant would not go into the
TRUNK. We'd need to create a 1_0 or 2_0 branch for it. With the big
changes we're thinking about, we need to leave the TRUNK alone and
only do minor changes and bugs fixes.

Bruce


--------------------------------------------------------

MF>I can show you my typical needs for session intializations:
MF>
MF>    Statement stmt = conn.createStatement();
MF>
MF>    try {
MF>        stmt.execute("set role ROLE_XXX identified by PASSWORD");
MF>        stmt.execute("alter session set nls_language = GERMAN");
MF>        stmt.execute("alter session set nls_territory = GERMANY");
MF>        stmt.execute("alter session set nls_numeric_characters = ',.'");
MF>    } catch(...
MF>
MF>This just requires the ability to execute some SQL statements.
MF>Would this also be possible with the method calls, you are thinking about?

No, I was only speaking of exposing method calls on the java.sql.Connetion
object. The stmt.execute() calls above can be achieved using the
java.sql.Connection object obtained from Database.getConnection().
Or am I missing something?


Bruce

------- Additional Comment #3 From Martin Fuchs 2004-09-15 01:16 -------
OK, the following patch will contain only the extension of the Database 
interface by getConnection() to expose DatabaseImpl.getConnection() without the 
need for a (DatabaseImpl) typecast.

------- Additional Comment #4 From Martin Fuchs 2004-09-15 01:17 -------
Created an attachment (id=663)
Patch for org.exolab.castor.jdo.Database interface

------- Additional Comment #5 From Werner Guttmann 2004-09-15 13:53 -------
>I'm not sure why the getConnection() method is not exposed in the
>Database interface.
Not am I, but I thin the original intention was to make it 'quite difficult' to
obtain a JDBC Connection from a Datbase instance. Here's why:

a) Castor JDO is an o/r mapper, first and foremost. We do not want people to
fiddl around with data through direct SQL calls.
b) One must NOT close this connection instance, as this would interfer with
Castor's locking and transaction business.

Because of this, I am really not sure whether we should expose getConnection
even further.

I've seen all the design ideas, bug I do not have the time to comment now.

------- Additional Comment #6 From Martin Fuchs 2004-09-16 01:04 -------
> Not am I, but I thin the original intention was to make it 'quite difficult' 
to
> obtain a JDBC Connection from a Datbase instance. Here's why:

Then this goal of making it 'quite difficult' has been reached.  ;-)

> a) Castor JDO is an o/r mapper, first and foremost. We do not want people to 
fiddl around with data through direct SQL calls.

But you see, there is the need for some way to get access to the underlying SQL 
connection in many applications. I see two application cases here:

- Initializing the database connection by setting some DB parameters or calling 
some stored procedures in the database.
- Calling stored procedures later on to process business logic.

So we should try to come up with a more convenient solution.

> b) One must NOT close this connection instance, as this would interfer with 
Castor's locking and transaction business.

If forbidding to call close() for the connection instance is the main reason 
for the design decision, what do you think about exposing two methods 
createJdbcStatement() and createPreparedJdbcStatement()? This would be 
sufficient for most application cases, while not allowing direct access to the 
connection object.

Btw. a callback mechanism to allow initializing a database connection just 
after Castor created it, would also be a very kind thing.

------- Additional Comment #7 From Werner Guttmann 2004-09-17 03:01 -------
> Initializing the database connection by setting some DB parameters 
> or calling some stored procedures in the database.
In the case of setting parameters, are you doing this by setting properties.
Iow, can you please give a short example of what you'd be doing (e.g. using
vanilla JDBC calls).

> Calling stored procedures later on to process business logic.
Why couldn't you use SQL CALL statements ? Just wondering ?

------- Additional Comment #8 From Werner Guttmann 2004-09-17 03:04 -------
> Btw. a callback mechanism to allow initializing a database connection 
> just after Castor created it, would also be a very kind thing.
Imho, I'd really favour implementing such a feature. As far as I can see, one
might have to deal with the fact that the underlying JDBC connection is not
created at Database instantiation time. But I still believe that - as long as a
handler is registered properly - it will be called, eventually.

------- Additional Comment #9 From Martin Fuchs 2004-09-17 03:34 -------
This example is already in the mails from above, but OK - here again.

This is an example of changing some database parameters using JDBC execute 
calls:

Statement stmt = conn.createStatement();

    try {
        stmt.execute("set role ROLE_XXX identified by PASSWORD");
        stmt.execute("alter session set nls_language = GERMAN");
        stmt.execute("alter session set nls_territory = GERMANY");
        stmt.execute("alter session set nls_numeric_characters = ',.'");
    } catch(...

To use the "CALL SQL" syntax I would have to create a OQLQuery. (Correct me if 
I have overseen another possibility.)
But OQLQuery expects a query with result set. I can't just issue a DDL command 
or call a database procedure without return data. For example this doesn't 
work, I get an 'Stored procedure call must end with "AS <class-name>"' 
exception:

OQLQuery oql = db.getOQLQuery("CALL SQL alter session set nls_language = 
GERMAN");

------- Additional Comment #10 From Werner Guttmann 2004-09-17 07:41 -------
> This example is already in the mails from above, but OK - here again.
Oops, sorry , but I was picking up on the use of the word 'properties' again ...
;-).

------- Additional Comment #11 From Werner Guttmann 2004-09-17 07:43 -------
> To use the "CALL SQL" syntax I would have to create a OQLQuery. (Correct 
> me if I have overseen another possibility.) But OQLQuery expects a query with 
> result set.
Correct, and I wish I could correct you ... but unfortunately your assessment is
true. Maybe we should allow for 'updates' to be used as part of a CALL SQL
statement.

------- Additional Comment #12 From Martin Fuchs 2004-10-30 10:50 -------
Extending OQL to allow 'update' statements may also be a possibility.
You think about something like the following, right?
  db.execute("CALL SQL alter session set nls_language = GERMAN");

But then why no immediately allow to issue SQL statements, using e.g.:
  db.executeSQL("alter session set nls_language = GERMAN");

Exposing an Database.execute() function as in the first example would imply,
there is also the possibility to execute OQL update statements like:
  db.execute("update someClass set attrib1 = 1234");
And this is something, I think, we don't want to provide. Instead Castor can be
used to unmarshal the object using a OQL query, altered in memory and updated
automatically at commit time.

     Query page      Enter new bug
This is Bugzilla: the Mozilla bug system. For more information about what Bugzilla is and what it can do, see bugzilla.org.
Actions: New | Query | bug # | Reports   New Account | Log In