Thread: getTables or code problem?
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();
?
My problem is that in my application I want to have the full listing of
catalogs, choose one of them, so have the full listing of schemas (well, I
imagine that I can do this in a single step, because I cannot find any JDBC
method signature which expects a catalog as parameter to return schemas, so
it seems to me that schemas should be the same for all catalogues -?- ).
Choosing the schema I want to get the correct table listing.
Just another thing: the output of the code before seems to me a little odd:
the field 'table_cat' returns always null. Is this the reason? Is this
correct?
Maybe I misunderstood something, because there are others application (for
example, PgAdminIII) which give the correct list. But where is my error?
Thanks in advance
Bye
Alessandro Depase
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?
Kris Jurka
>
>
> 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
On Fri, 16 Jan 2004, Alessandro Depase wrote: > > 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. Thinking about this some more, I don't think an Exception is correct. We don't throw an Exception if the given schema doesn't exist for example. I think we should just return no rows. The other problem is that some getXXX methods don't take a catalog name. For example getSchemas(). > 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. The current getCatalogs() returns all databases whether you can connect to them or not. At the moment I'm leaning towards making this return only the current database. The reason for this is that consider a tool like a cross platform database manager/viewer. (In particular I'm thinking of DBVisualizer.) It presents you with a hierarchy which you can navigate down catalog->schema->table type->... For an application like this how could it possibly know that it can't acccess other catalogs? It wouldn't be so bad if navigating into another database came up with nothing, but it will return values from the current db for schemas and possibly table types. Perhaps the core of this problem may be that Connection.setCatalog does not throw an Exception when given a catalog name other than the current one. Anyway I'm just rambling now. Kris Jurka
> On Fri, 16 Jan 2004, Alessandro Depase wrote:
>
> > > 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.
>
> Thinking about this some more, I don't think an Exception is correct. We
> don't throw an Exception if the given schema doesn't exist for example. I
> think we should just return no rows. The other problem is that some
> getXXX methods don't take a catalog name. For example getSchemas().
Yes, I too noted that getXXX methods don't take a catalog mail in my first
mail and it seemed to me a little odd, but thinking about this some more, as
you suggest, I think that your interpretation may be correct (also about the
implementation returning no rows, instead of the Exception). In fact,
reading better the javadocs it seems that the parameters for all those
methods are to be thought as search patterns (but maybe there are
specifications which define this better?). And when you don't find something
you don't have to throw Exceptions. However, how can we discriminate the
case in which we really have no tables, but the catalog is that
corresponding to the actual connection from the case in which the catalog is
not that of the actual connection (these are, in my opinion, very different
situations)? However, maybe the correct answer is, again, in the
specifications. Else both solutions are, in my opinion, better than the
actual implementation, with a so little difference that could be just a
matter of personal preference (and I vote again for the Exception, just to
be able to discriminate the error from the empty result, which are different
things - why don't throw an Exception also if the schema does not exist,
obviously if the specification permits this?).
> > 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.
>
> The current getCatalogs() returns all databases whether you can connect to
> them or not. At the moment I'm leaning towards making this return only
> the current database. The reason for this is that consider a tool like a
> cross platform database manager/viewer. (In particular I'm thinking of
> DBVisualizer.) It presents you with a hierarchy which you can navigate
> down catalog->schema->table type->... For an application like this how
> could it possibly know that it can't access other catalogs? It wouldn't
> be so bad if navigating into another database came up with nothing, but it
> will return values from the current db for schemas and possibly table
> types.
Well, I think that the issue is important also for the getSchemas() method,
because, as you noted before, it takes no catalog as parameter.
Which is its behaviour at the moment? Does it returns schemas from all
catalogs or just the schemas for the current connection? If the first is the
actual implementation, maybe this can lead to navigation problems.
However, the reason to return catalogs and schema only for the current
connection is not, in my opinion, the one you reported before (how could it
possibly know that it can't access other catalogs?), because you could
return catalogs for which the current user is enabled to, or, eventually, an
application like DBVisualizer or pgAdmin can simply try, for example, to
open a new connection with the actual data and, in case of error, using a
pop-up window to ask the user the correct parameters for that connection
(and maybe there are a number of other ways...). This, however, could be the
reason not to return schemas from other catalogs, because I don't think that
the same workaround apply well.
But, as we wrote before, the reasons not to return other catalogs could be:
- security (very little problem, because the real block is elsewhere, but
why help hackers know that there are other catalogs?)
- as a matter of congruence with the other getXXX methods (above all the
getSchema, which has the same signature, if it returns only the schemas in
the current connection)
- what do specifications say?
Just a last note: when I ask for tables, the 'table_cat' field is null, as I
wrote in my first mail. This seems to be incorrect to me (but, maybe this is
not the correct place to tell this, because it is very probable that this is
not a JDBC problem, but a general problem). However I cannot imagine a real
effective use for this, above all if we consider what we wrote before: in
fact this field should be filled only and always with the only catalog to
which we are connected to. Or am I missing something?
> Perhaps the core of this problem may be that Connection.setCatalog does
> not throw an Exception when given a catalog name other than the current
> one.
And what is the effect for this?
Bye
Alessandro Depase
Kris, I agree with your thoughts: return no rows for other catalogs, and have getCatalogs only return the current one you are connected to. While this is limiting, it will provide the best (but certainly not ideal) user experience with tools like DBVisualizer. --Barry Kris Jurka wrote: > > On Fri, 16 Jan 2004, Alessandro Depase wrote: > > >>>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. > > > Thinking about this some more, I don't think an Exception is correct. We > don't throw an Exception if the given schema doesn't exist for example. I > think we should just return no rows. The other problem is that some > getXXX methods don't take a catalog name. For example getSchemas(). > > >>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. > > > The current getCatalogs() returns all databases whether you can connect to > them or not. At the moment I'm leaning towards making this return only > the current database. The reason for this is that consider a tool like a > cross platform database manager/viewer. (In particular I'm thinking of > DBVisualizer.) It presents you with a hierarchy which you can navigate > down catalog->schema->table type->... For an application like this how > could it possibly know that it can't acccess other catalogs? It wouldn't > be so bad if navigating into another database came up with nothing, but it > will return values from the current db for schemas and possibly table > types. > > Perhaps the core of this problem may be that Connection.setCatalog does > not throw an Exception when given a catalog name other than the current > one. > > Anyway I'm just rambling now. > > Kris Jurka > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings