Re: getTables or code problem? - Mailing list pgsql-jdbc

>
>
> On Fri, 16 Jan 2004, Alessandro Depase wrote:
>
> > Hi all,
> > I have a problem with the use of DatabaseMetaData.getTables.
> > This is the part of code I last tried:
> >
> > DatabaseMetaData conMD = connection.getMetaData();
> > System.out.println("Driver Name:\t" + conMD.getDriverName());
> > System.out.println("Driver Version:\t" + conMD.getDriverVersion());
> > String[] tableType = {"TABLE"};
> > ResultSet tables = conMD.getTables("db2","public",null,tableType); //I
tried
> > also "%" instead of null, with the same results and "", but founding
nothing
> > for (int j = 1; j <= tables.getMetaData().getColumnCount(); j++) {
> >     System.out.print(tables.getMetaData().getColumnName(j) + "\t");
> > }
> > System.out.println();
> > while (tables.next()) {
> >     for (int j = 1; j <= tables.getMetaData().getColumnCount(); j++)
> >     {
> >         System.out.print(tables.getObject(j) + "\t");
> >     }
> >     System.out.println();
> > }
> >
> > The result of the first 2 lines is interesting for you, I imagine:
> > Driver Name: PostgreSQL Native Driver
> > Driver Version: PostgreSQL 7.4 JDBC3 with SSL (build 209)
> >
> > What is the problem? The list I see as output is the list of the catalog
db1
> > (the one I connected to using the connection string:
> > jdbc:postgresql://myhost:5432/db1 - the schema is "public" in both
cases).
> >
> > Well, this can even sound good to me, but why can I see all the catalogs
> > using a
> > ResultSet catalogs = conMD.getCatalogs();
> > ?
>
> The way the postgresql server works is that you can only connect to one
> database at a time.  This means that is impossible to list the tables in a
> database that you are not connected to.  The thing is that you can get a
> list of all the databases from any database because pg_database is a
> global table.  It has been debated here before whether we should return
> only the database you are connected to in the results of getCatalogs().
> Another option would be to put in a check on a call like getTables to see
> if a catalog was passed in that is different from the connected catalog
> and either throw an Exception or return no results.
>
> What would make the most sense to you given the server's current
> restrictions?

Thanks for your answer: at least now I know that there are such
restrictions.
I think that one of the two options (Exception or no result) will be better
and less confusing that a list of tables belonging to another catalog. Let's
suppose that we have several catalogs with almost the same tables (some
fields different, a table more, a table less as sometimes happens when you
have different installation of the same system for different customers...)
it could be difficult to understand that we are getting the wrong result
(because, for example, we are connected with a wrong connection string) and
we could modify data where they should not be modified.

My vote is for the Exception: in this way, with a clear message, I can
easily understand what the problem is, while an empty result could be also a
bug in my code.

I don't know what you decided at the end of the debate about the result of
getCatalogs: my opinion (if you mind it) is that it's ok the actual result
(because, at least, one can choose to create a new connection to deal with
the other catalogues), above all if the user can see only catalogs to which
he has access to (the user in my test can log on every db, so I cannot tell
if the behaviour is that I just described).
Else, there could be a (very little) security problem.

Thanks again and bye

    Alessandro Depase