Getting the last modified date of a table

Thread view:

Antti Karanta - 02 Feb 2005 08:37 GMT

Hi!

I was wondering whether there is a universal way to ask via jdbc when a
db table was last modified. I am looking for a way that does not need any
structural modifications the the table itself or the schema (e.g.
inserting a new col or creating a separate table to write this info to).

I did some searching on the subject and it seems this information is not
available, at least not on all dbs, but just thought I'd ask if anyone
else has any experience w/ this.

--Antti--

Reply to this Message

Wiseguy - 18 Feb 2005 04:46 GMT

"Antti Karanta" <firstname.lastname@napa.fi> scribbled on the stall wall:

> I was wondering whether there is a universal way to ask via jdbc when a
> db table was last modified. I am looking for a way that does not need any
> structural modifications the the table itself or the schema (e.g.
> inserting a new col or creating a separate table to write this info to).

I am not aware of any JDBC method that gets that metadata. Since JDBC aims
to be transportable, and not all common databases support that function,
I doubt it would be part of the JDBC API.

Asking this is suspect though. Question why you even need that data. It
is far preferable to put an update timestamp field in the table itself.

How does the (date table was last updated) fit into the relational model
itself? I don't think it does if you use the model correctly.

Reply to this Message

Antti Karanta - 28 Feb 2005 12:42 GMT

> "Antti Karanta" <firstname.lastname@napa.fi> scribbled on the stall wall:
>> I was wondering whether there is a universal way to ask via jdbc
[quoted text clipped - 8 lines]
> to be transportable, and not all common databases support that function,
> I doubt it would be part of the JDBC API.

I don't see this as a reason to exclude it. The JDBC api has quite a few
things that address features that may not be provided by the driver / db,
e.g. stored procedures, all transaction isolation levels etc. The key
point is being able to ask whether the feature you want to use is
supported, see e.g.
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/DatabaseMetaData.html#supportsT
ransactionIsolationLevel(int).

> Asking this is suspect though. Question why you even need that data. It
> is far preferable to put an update timestamp field in the table itself.

Yes, I fully agree.

The case (in short) is this: I am developing an extension to an old
application that stores some data as tables, in its own proprietary
format. It has become desirable to be able to import tables from an rdbms
and handle them just like the native tables of the app itself. Now, the
native app's own tables have a timestamp telling when the table itself was
last modified. In order for the handling of data from an rdbms to be as
simple as possible it would be nice if this info was available as it is
used in some of the app's internal logic.

Since the tables the user wants to handle are given at runtime I can
have no information about them except for what I can find in the metadata.
I can not assume that I can modify the schema in any way - the user may
not have the rights for it and even if he did he might find it undesirable
that the tables' structures are tampered with.

> How does the (date table was last updated) fit into the relational model
> itself? I don't think it does if you use the model correctly.

Yes, this would be the case if I got to design the schema myself for a
particular purpose.

--Antti--

Reply to this Message

Getting the last modified date of a table

Free Magazines