db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: [jira] Updated: (DERBY-1107) For existing databases JDBC metadata queries do not get updated properly between maintenance versions.
Date Wed, 29 Mar 2006 19:13:40 GMT
"David W. Van Couvering" <David.Vancouvering@Sun.COM> writes:

> I haven't been following all this, and perhaps I am missing something,
> but isn't a large part of the problem due to the fact that we are
> invoking compiled procedures?  Wouldn't one way to solve the "metadata
> problem" be to just invoke the SQL directly from the client rather
> than storing it in the server?

Well, we are not discussing how the client should handle metadata on
upgrade, but how the embedded driver should handle it. These are two
separate issues which I think are orthogonal.

I'll summarize my understanding of these two issues (if someone who
knows these things reads this, please correct me if I'm wrong):

The SQL code for metadata queries is kept in metadata.properties. When
you create a database, Derby will read all the queries, compile them
and store them as SPSs (stored prepared statements). An SPS is not a
stored procedure, but a stored compiled query.

If you just modify a query in metadata.properties, the metadata call
will still use the old query since it is stored as an SPS. To make the
changes visible after a version change, Derby has this approach:

  - if you are running in soft upgrade mode, the SPSs will not be
    used. Instead, the query is read from metadata.properties. The
    users will therefore see results from the new query.

  - if you perform a hard upgrade to a new major/minor version, all
    the SPSs are dropped and regenerated. The new query will be
    visible to the users.

  - if you move from one version to a version with the same major and
    minor version (say, from to, the SPSs are
    invalidated, but not dropped. This means they will be recompiled,
    but still use the old SQL code. Hence, changes to an existing
    query in metadata.properties will not be visible to the users.

To solve the problem for the last case, there are at least three

  1) Drop and regenerate SPSs on all version changes.

  2) Stop using SPSs (use ordinary prepared statements instead).

  3) Instead of modifying an existing query, one could create a new
     one with a new name.

To me, option 1 seems like the best choice.

The client, on the other hand, executes a stored procedure on the
server in order to fetch metadata. The stored procedure forwards the
call to the embedded driver, and the embedded driver will use an SPS
to fetch the metadata. This way, the client will always get whatever
the server means is the correct data. If you use an old client against
a new server, you will get results from the new query, and if you use
a new client against an old server, you get results from the old query
(or an error if that query was not implemented in the old version of
the server). As long as the signature of the stored procedure is not
changed, any version of the client should work with any version of the

As you suggested, the client could invoke the SQL directly. This would
however cause some problems:

  - if there is a bug in a metadata query, it is not enough to upgrade
    the server, you would also have to upgrade all the clients.

  - if the server changes the name of a system table or a column in a
    system table, old clients will be broken.

The way it is now, if the bug is fixed on the server, it is also fixed
on the client.

Knut Anders

View raw message