Re: How to detect views w/ JDBC?

From: Bob Bunch (OCP) <bunchb_at_hotmail.com>
Date: Mon, 18 Apr 2005 10:05:33 -0400
Message-Id: <pan.2005.04.18.14.05.30.812000@hotmail.com>

On Mon, 18 Apr 2005 06:32:28 -0700, Rauf Sarwar wrote:

> Bob Bunch (OCP) wrote:

>> On Mon, 18 Apr 2005 03:39:58 +0000, HansF wrote:
>>
>> > On Sun, 17 Apr 2005 23:14:22 -0400, Bob Bunch (OCP) interested us

> by

>> > writing:
>> >
>> >> Perhaps I should've clarified that this is for a code generator,

> NOT a

>> >> to-be-live application.  I'm generating classes based on a given
>> >> schema's tables/views, and need to determine WHAT a given entity

> is, so

>> >> I can NOT generate insert/update/delete methods if the entity is a
>> >> view.
>> >
>> > 1) If you are thinking about database independence, listen to Jim.
>> >
>> > 2) Oracle has vews '???_TABLES' and '???_VIEWS' that list tables

> and

>> > views.  ???=USER for those owned by the logged-in user'; =ALL for

> those

>> > accessible by the user; =DBA for all, if the user has DBA rights.
>> >
>> > 3) Some of Oracle's views are updateable.  If you are unfamiliar

> with

>> > this, suggest you reread the manuals and 'Reilly book 'Mastering

> Oracle

>> > SQL' to get more info.
>> >
>> > Also check out '???_UPDATABLE_COLUMNS'
>> >
>> > 4) In many cases, you should seriously consider turning those

> methods

>> > into triggers (or PL/SQL routines called by triggers).  That helps

> to

>> > ensure complete data consistency regardless of which mechanism your
>> > users find to bypass your application code.
>> >
>> > (Unless you tie their hands behind their backs, someone will find

> out

>> > that Excel and ODBC can ruin what you are doing.  They will then

> delight

>> > in telling you that there is a major bug in your system, based on

> the

>> > report they generated after mucking with your datay.)
>> >
>> > 5) Oracle's online docco at http://docs.oracle.com is handy.  In

> this

>> > case, concentrate on the Concepts manual and the Reference manual

> for

>> > the relevant version,
>> >
>> > 6) Realize that what you are doing is somewhat version dependent.

> Be

>> > prepared for long term maintenance.
>>
>> 1) Jim didn't tell me HOW TO do anything.
>>
>> 2) I'm not just a Java coder; I've been an Oracle DBA for > 10 years,

> 7.1

>> --> 10g. *Of course* I could hit the user_/all_/dba_ views to get

> what I

>> want (I've written Swing apps in the past that did all kinds of

> neat/nifty

>> DBA stuff), but this *must be* DB agnostic. MySQL, Sybase, DB2 -- do

> they

>> have the same views? No.
>>
>> 3) And some are not, hence my question.
>>
>> 4) I'm fully aware of Oracle's SP/SF/trigger capabilities.  This is

> BY NO

>> MEANS platform agnostic, and believe me - I'm a big proponent of

> using

>> said capabilities!
>>  However, it's not appropriate for this situaion.
>>
>> 5) Been there done that - do they have examples for how to determine,

> via

>> standard JDBC calls ONLY, how to detect a view?
>>
>> 6) No it isn't - what I'm TRYING to do is DB agnostic, therefore

> would

>> work on not just other vendors, but any version of Oracle that JDBC

> will

>> hook up to.
>>
>> Bottom line - please _fully_ read my situation befoe telling me to do
>> bunch of Oracle-specific stuff.  I would just LOVE it if I could ONLY

> EVER

>> support Oracle - I have 99.9% of my experience w/ this great

> platform;

>> however, this is not my current reality for this particular project.

> If

>> you don't know how to do this, I appreciate your help, but please

> don't

>> just tell me how to do it w/ Oracle-specifics.  I already know that.

> ;)
>
> What you are looking for is DatabaseMetaData object. e.g. This prints
> all Table names and view names with their types. You can build on it.
>
> import java.sql.*;
>
> public class Test {
>
> public static void main (String[] args)
> throws Exception {
>
> DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
>
> String url = "jdbc:oracle:oci:@test"; Connection conn =
> DriverManager.getConnection (url, "me", "me");
>
> String s = new String("TABLE\nVIEW"); String[] str =
> s.split("\n");
> DatabaseMetaData dmd = conn.getMetaData(); ResultSet rs =
> dmd.getTables(null, null, null, str); while (rs.next()) {
> System.out.println(rs.getString(3) + " " + rs.getString(4));
> }
> rs.close();
> conn.close();
> }
> }
>
> Regards
> /Rauf

Thanks a lot for the tip! Exactly the direction I needed to go! ;) Received on Mon Apr 18 2005 - 09:05:33 CDT