Skip to main content

Let's do some tidying up e.g. JDBC

26 replies [Last post]
soupdragon
Offline
Joined: 2006-01-07
Points: 0

Yes, I know it's more fun to add sexy new features and pursue the cutting edge aspects, but lets consider cleaning up some of the uglies that we've been living with so long we no longer imagine them being straighted out.

JDBC carries minor but oft-encountered irritants, for example. Like why on Earth does it not use java.util.Date for it's times and dates? Who decided we needed a java.sql.Date? (thus resulting in many classes which have to use two different classes called Date) Like the (lack of) proper BLOB/CLOB handling, the current system seems to have been written by someone who didn't quite grasp how LOBs work.

Yes, I know straighening out these things would be a pain, because of all the third-party implementations involved.

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
lancea
Offline
Joined: 2003-06-13
Points: 0

So postgres does not support BLOB/CLOB currently then via DDL/DML currently. Looks like from your description this is just a bolt on api for postgres and what we are doing in JDBC is in line with SQL 2003 standard

lancea
Offline
Joined: 2003-06-13
Points: 0

Well, a quick check of the Postgres 8.1 docs does not indicate BLOB or CLOB support. There is support for a Text column.

There is nothing i found in the ANSI 2003 SQL standard that indicates a requirement for requiring an independant existance of a LOB, that is not associated to a table.

I am no means an expert on Postgres as i do not use it, but if LOB support was there as a standard DataType, i would expect it to be documented

soupdragon
Offline
Joined: 2006-01-07
Points: 0

>
> There is nothing i found in the ANSI 2003 SQL
> standard that indicates a requirement for requiring
> an independant existance of a LOB, that is not
> associated to a table.
>

But then LOBS aren't really a part of SQL, as such, requiring a non-SQl data feed to load and read them.

> I am no means an expert on Postgres as i do not use
> it, but if LOB support was there as a standard
> DataType, i would expect it to be documented

I've used LOBs in postgress, you use the OID data type to store the handle.

The driver libray includes a special set of classes for handling LOBs, but you can use java.sql.Blob. I found I could create new records with Blobs by writing my own, simple, implementaion of the Blob interface.

lancea
Offline
Joined: 2003-06-13
Points: 0

CLOB/BLOBs are defined in the SQL standard and the support that JDBC has added is to support what is requried via the standard.

lancea
Offline
Joined: 2003-06-13
Points: 0

Not every application requires a ResultSet to be converted to some form of Collection. The RowSet pointer was just a way to guide you to an existing means to access the data via a Collection

lancea
Offline
Joined: 2003-06-13
Points: 0

Well, the original EG thought there was merit in creating a specific java.sql.Date class and that is what is there today and debating the design unfortunately wont change the implemenation. Date/Time/Timestamp/DateTime have always been problematic for Database vendors as they are all implemented different features.

So it is what it is and we just do our best to articulate the intended behavior in the best possible way that we can in the spec.

soupdragon
Offline
Joined: 2006-01-07
Points: 0

> java.sql.Date sets the hour/minutes/seconds to zero
> as is expected by a SQL Date datatype.
>

Seems compleletely unessesary. This could be handled by the choice of get/set Date/Time/Timestamp, all of which could take java.util.Date. The only case where there's any argument for subclassing might be setObject.

At least add setDate/Time/Timestamp methods that [i]accept[/i] java.util.Date.

And calling it java.sql.Date was a bad move, since you often need both kinds of Date in the same class.

soupdragon
Offline
Joined: 2006-01-07
Points: 0

Had a quick look at the JDBC 4 API, and hurrah! We're finally getting a createBlob method.

But there's still no delete on the Blob interface.

Blobs exist in the database as independant blocks of storage separate from the tables and referenced by a handle. They are outside SQL.

The natural way to create a record with a blob is:

o Create the blob on the database getting back a handle

o Write data to it, using the handle

o Put the handle into an SQL update/insert statement.

o Execute the statement.

The natural way to retrieve Blob data is;

o Execute a database query.

o Get a record, and from it a handle.

o Using the handle read the Blob data, when required.

But the JDBC blob objects kind of do it backwards. They actually store the data in a ByteBuffer, and create the Blob during PreparedStatement.setBlob() (at least the one I looked at the source of did it that way). Data is read into the buffer in getBlob().

The Blob object should contain the handle, not the data. When you a stream on it it should give you a stream which loads from or writes direct to the database.

lancea
Offline
Joined: 2003-06-13
Points: 0

The create method was added as a convenience method as different databases have different requirements for how a LOBs is created initially.

Vendors have different implementations WRT how they implement, access and store LOBs within their data structures. So how a given driver implements the various methods will vary.

WRT having a delete method, i am not sure there is the same need as create, but i can run it by the EG one more time.

soupdragon
Offline
Joined: 2006-01-07
Points: 0

> The create method was added as a convenience method
>
> WRT having a delete method, i am not sure there is
> the same need as create, but i can run it by the EG
> one more time.

For a delete method to be implemented the Blob object would need to contain the handle of the LOB, not the data, since it's the LOB itself that needs deleting. As it is you have to use vendor-dependent SQL functions to do the job.

I don't regard the createBlob as a convenience method, since that would imply there was a legitimate way of creating Blobs before. If there is, I haven't come accross it. In fact people have gone to extremely messey lengths, typicaly creating a record with an empty Blob, reading it back and updating it.

And yes, I accept that it's probably too late to get rid of java.sql.Date. However let's at least add a constructor from java.util.Date to it (and it's fellows), and setDate(java.util.Date date) etc. methods to the PreparedStatement interface. That way we could forget about it in new code.

lancea
Offline
Joined: 2003-06-13
Points: 0

create is a convience method so that you have a portable way to create a LOB without going through different SQL commands for the various backends which is what you have to do today.

I am still not seeing the use case for delete given when you delete the row, the LOB for the given row is also removed.

soupdragon
Offline
Joined: 2006-01-07
Points: 0

>
> I am still not seeing the use case for delete given
> when you delete the row, the LOB for the given row is
> also removed.

Have you checked? It was my understanding that LOBs have an independant existance, can be shared by multiple database rows, and need to be explicitly deleted. I've always deleted them using vendor-specific lo_delete SQL functions before deleting the row. You do a select statment to achieve the LOB deletion.

As far as I know if you just delete the row you'll never get the LOB space back.

Maybe some of the more sophisticated databases keep a use count or something but this seems unlikely. Generally you use a generalise "OID" field type to store LOB handles.

The system I've looked through the source of is Postgress, which actually has a separated API for LOB handling.

soupdragon
Offline
Joined: 2006-01-07
Points: 0

Hmm.. A quick google later... Looks like Oracle, at least, does delete LOBs implicitly. Pretty sure PostgressSQL (which I've used Blobs more on) doesn't.

lancea
Offline
Joined: 2003-06-13
Points: 0

java.sql.Date sets the hour/minutes/seconds to zero as is expected by a SQL Date datatype.

With respect to Blob/Clob handling, there have been several improvements we have added as part of the JDBC 4 specification work. I would suggest that you check out the pending changes and if you still have additional suggestions, let me know.

Regards
Lance
JDBC specification Lead

coxcu
Offline
Joined: 2003-06-11
Points: 0

Lance,

Could you enhance the documentation to make these issues clear? The Javadoc mentions the "definition of SQL DATE" to justify some of its semantics. A link and an extended quote would really enhance the value of the documentation.

java.sql.Date
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Date.html

Also, when some form of immutable date (like Joda) is finally added to the platform, is there any reason not to support it in JDBC?

"java.sql.Time uses the default TimeZone"
http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=4487450

Thanks,
Curt

lancea
Offline
Joined: 2003-06-13
Points: 0

Hi Curt,

thanks for the suggestion. The current wording at the top of the javadoc indicates the following:

To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.

Doesn't that already address your concerns? If you are looking for something else, please let me know.

When/if something like Joda is added, we will certainly discuss the viability of adding it to the JDBC spec.

WRT Time/TimeZone, this is a thorny issue from Ansi SQL Standard as well. We briefly discussed trying to do something in JDBC 4, but it is going to take time to flush it out to do it correctly i am afraid. So it is on the list of items to review for the next update to JDBC.

Many thanks for the post
Lance

coxcu
Offline
Joined: 2003-06-11
Points: 0

Here's what I was thinking:

"definition of SQL DATE" would have a hyperlink pointing to the definition of SQL DATE being referenced.

The text for the definition of SQL DATE would appear blockquoted in the document. Additionally some visual clues may added to make the fact that it is foreign text obvious.

After the blockquote would come some discussion of how the semantics of java.sql.Date are derived from the definition.

mthornton
Offline
Joined: 2003-06-10
Points: 0

> "definition of SQL DATE" would have a hyperlink
> pointing to the definition of SQL DATE being
> referenced.

As far as I know there isn't an authoritative copy of the relevant standard available on the web (for free). Some standards organisations require you to pay for a copy.

coxcu
Offline
Joined: 2003-06-11
Points: 0

If no suitable definition is freely available, then there should be instructions on how to obtain a suitable definition. The ISBN or Amazon links of books that contain it would also be useful.

lancea
Offline
Joined: 2003-06-13
Points: 0

The JDBC spec (PDF) does list the document numbers for each of the relevant ANSI SQL docs.

Unfortunately the only place i am aware you can purchase these is vai www.ansi.org

coxcu
Offline
Joined: 2003-06-11
Points: 0

To people that already know what the Javadoc intends to say, then no clarification is required. For the rest of us, I suggest something like:

================
For a complete definition of SQL DATE see
"Information technology - Database languages - SQL - Part 1: Framework (SQL/Framework)"
INCITS/ISO/IEC 9075-1-2003
p. xxx-yyy

This document is available from www.ansi.org
===============

Is that the right document? Depending on the terms of document, you might be able to provide a very useful passage from it.

Speaking of the JDBC PDF, it would be nice if there was a download of the JDK documentation that contained all of the documentation for all of the relevant JSRs.

Finally, it would be really nice if printed copies of more of this documentation was available. A huge amount of time and effort is spent producing these PDFs. If they were available as bound volumes they would be more useful and more widely read. Will the license of JSR-221 allow anybody who downloads it to publish the spec, or would special permission be required?

lancea
Offline
Joined: 2003-06-13
Points: 0

Thanks for the suggestion, i will have to ponder this some more as if i do it for one, i have lots of areas to do this for. The javadocs are considered part of the spec for JDBC which is why the appendix info was not added to the javadocs previously.

Thank you again for the comment and suggestions, i appreciate it.

sasdav
Offline
Joined: 2003-09-04
Points: 0

I agree.
Something that's always irritated me is the ResultSet. Once you've got it you have to convert it to something else (Vector, List, etc) before the data is really useful. And the only way to do that is to move through the ResultSet one record at a time. Surely we can come up with something more efficient than that.

lancea
Offline
Joined: 2003-06-13
Points: 0

CachedRowSet.toCollection() can be used to convert a ResultSet to a Collection object.

sasdav
Offline
Joined: 2003-09-04
Points: 0

Yes, I know that a RowSet is a wrapper around a ResultSet, but come on. Are you really suggesting that before we can use a ResultSet we have to turn it into something else?

mthornton
Offline
Joined: 2003-06-10
Points: 0

ResultSet's are what databases return from queries. There are plenty of applications where this is exactly what is required. If you want something else, this is the starting point from which other structures are created.