Re: [JDBC] DatabaseMetaData.getTables()
I forgot to mention I'm using the current CVS. On Sun, Oct 28, 2001 at 10:07:06AM +0100, Rene Pijlman wrote: On Sat, 27 Oct 2001 19:25:49 -0500, you wrote: There seems to be a problem with DatabaseMetaData.getTables() [...] It throws a NullPointerException: This may have been fixed by now: http://fts.postgresql.org/db/mw/msg.html?mid=1021572 Regards, René Pijlman [EMAIL PROTECTED] -- Jason Davies [EMAIL PROTECTED] PGP signature
Re: [JDBC] DatabaseMetaData.getTables()
On Sun, Oct 28, 2001 at 07:14:42AM -0500, Dave Cramer wrote: It appears the getBytes was previously being used to return a byte array of any arbitrary column. Fixes for blobs seem to have broken this. The question is as Jason pointed out which do we fix. It doesn't seem unreasonable to be able to return a byte array for any arbitray column. On the other hand is this the intended use? This is what the documentation says: public byte[] getBytes(int columnIndex) throws SQLException Retrieves the value of the designated column in the current row of this ResultSet object as a byte array in the Java programming language. The bytes represent the raw values returned by the driver. It seems to imply that it _should_ return a byte array for any arbitary column. But as usual, it's up to us to decide. I think it's reasonable, since we are working with byte arrays in the code anyway. -- Jason Davies [EMAIL PROTECTED] PGP signature
[JDBC] DatabaseMetaData.getTables()
Hi, There seems to be a problem with DatabaseMetaData.getTables() when I do the following: ResultSet R=conn.getMetaData().getTables(null, null, %, null); It throws a NullPointerException: java.lang.NullPointerException at org.postgresql.jdbc2.DatabaseMetaData.getTables(DatabaseMetaData.java:1732) at Test.main(Test.java:66) Looking at the source, ResultSet.getBytes() is called and it returns null, causing this exception to be thrown. However I can use ResultSet.getString() without a problem. I'm using 7.1.3 at the moment. Does ResultSet.getBytes() need to be fixed or should getTables() be modified? I'd be grateful for any insights. Or you can just tell me to use the latest cvs version of PostgreSQL :) What is the consensus on supporting older versions, will you phase out old code when 7.2 comes out? -- Jason Davies [EMAIL PROTECTED] PGP signature
Re: [JDBC] DatabaseMetaData.getImported/ExportedKeys() patch
On Fri, Oct 26, 2001 at 09:52:11AM -0700, Barry Lind wrote:
Jason,
Can you explain what this patch is attempting to fix? Given that we are
in beta with 7.2, I want to fully understand the problem here before
applying this patch. (Especially given that the diff is pretty large and
not a one or two line change).
I guess fix is slightly misleading. This patch implements
DatabaseMetaData.getExportedKeys() which was previously not implemented, and provides
a better implementation of DatabaseMetaData.getImportedKeys() which was previously
half-implemented.
The methods are identical for both jdbc1 and jdbc2 so that's why the diff is quite
large. Perhaps identical code should be put into a single class and have the code
which differs in jdbc1 and jdbc2 in subclasses?
Also what testing have you done on this? (When you ask us to 'check
that it works', it doesn't give me a high level of confidence that this
is well tested).
:-) I have tested it but only with 7.1.3 at the moment. What I meant to say is please
:check in case something major has been changed in 7.2. Also if the queries I'm using
:can be optimized in any way that would be great.
At the moment a value for PK_NAME (primary key name) is not returned but the main part
is implemented.
Sorry for the lack of info, I'll try and be more helpful next time :)
Jason Davies
thanks,
--Barry
Jason Davies wrote:
Hi,
Here is a diff for DatabaseMetaData.getImportedKeys() and
DatabaseMetaData.getExportedKeys(). Please check that it works :)
Thanks,
Jason Davies
[EMAIL PROTECTED]
Index: jdbc1/DatabaseMetaData.java
===
RCS file:
/projects/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/DatabaseMetaData.java,v
retrieving revision 1.35
diff -c -r1.35 DatabaseMetaData.java
*** jdbc1/DatabaseMetaData.java 2001/10/25 05:59:59 1.35
--- jdbc1/DatabaseMetaData.java 2001/10/26 01:21:02
***
*** 2260,2345
);
}
! private void importLoop(Vector tuples, java.sql.ResultSet keyRelation) throws
SQLException
! {
! String s, s2;
! String origTable = null, primTable = new String(), schema;
! int i;
! Vector v = new Vector();
!
! s = keyRelation.getString(1);
! s2 = s;
! //System.out.println(s);
!
! for (i = 0;;i++)
! {
! s = s.substring(s.indexOf(\\000) + 4);
! if (s.compareTo() == 0)
! {
! //System.out.println();
! break;
! }
! s2 = s.substring(0, s.indexOf(\\000));
! switch (i)
! {
! case 0:
! origTable = s2;
! break;
! case 1:
! primTable = s2;
! break;
! case 2:
! schema = s2;
! break;
! default:
! v.addElement(s2);
! }
! }
!
! java.sql.ResultSet rstmp = connection.ExecSQL(select * from +
origTable + where 1=0);
! java.sql.ResultSetMetaData origCols = rstmp.getMetaData();
!
! String stmp;
! // Vector tuples=new Vector();
! byte tuple[][];
!
! // the foreign keys are only on even positions in the Vector.
! for (i = 0;i v.size();i += 2)
{
! stmp = (String)v.elementAt(i);
!
! for (int j = 1;j = origCols.getColumnCount();j++)
{
! if (stmp.compareTo(origCols.getColumnName(j)) == 0)
{
! tuple = new byte[14][0];
!
! for (int k = 0;k 14;k++)
! tuple[k] = null;
!
! //PKTABLE_NAME
! tuple[2] = primTable.getBytes();
! //PKTABLE_COLUMN
! stmp = (String)v.elementAt(i + 1);
! tuple[3] = stmp.getBytes();
! //FKTABLE_NAME
! tuple[6] = origTable.getBytes();
! //FKCOLUMN_NAME
! tuple[7] =
origCols.getColumnName(j).getBytes
Re: [JDBC] Connection.setCatalog()
[[[ Original Message from Tom Lane [EMAIL PROTECTED] ]]] Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: Peter E. has previously commented that Postgres databases correspond most closely to the SQL concept of catalog cluster, not catalog. I most certainly did not. According to my interpretation: I sit corrected. If you want to define catalog == database, okay with me. regards, tom lane Great, here is a context diff of CVS for implementing the get/setCatalog methods in Connection - note: I've updated setCatalog(String catalog) from my previous diff so it checks whether it is already connected to the specified catalog. Thanks, Jason Davies [EMAIL PROTECTED] Connection.diff ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [JDBC] Connection.setCatalog()
[[[ Original Message from Tom Lane [EMAIL PROTECTED] ]]] [EMAIL PROTECTED] writes: Do you know whether PostgreSQL will support the catalogname.tablename syntax in SQL queries in the future? Schema support is on the to-do list. It's probably too late to imagine that it will get done for 7.2, but maybe for 7.3. Although the details haven't been argued out yet, I suspect that the existing concept of independent databases within an installation will remain in place (for backwards compatibility if nothing else). SQL-style schemas and catalogs will exist as new naming levels *within* what we now call a database. I see - I didn't realise this. As this is the case, there is no need for my database tool to list available databases since databases in postgresql are clusters. Thanks for clarifying that. Peter E. has previously commented that Postgres databases correspond most closely to the SQL concept of catalog cluster, not catalog. This agrees with my reading of SQL92 4.13: A cluster is an implementation-defined collection of catalogs. Exactly one cluster is associated with an SQL-session and it defines the totality of the SQL-data that is available to that SQL-session. Schemas and catalogs will exist within a database, and there will still be no possibility of cross-database accesses (but, hopefully, much less need for it either). Good. As long as everything conforms to some kind of standard we're okay :) While I haven't followed this discussion closely, it appears to me that you are trying to make setCatalog reconnect to a new database --- ie, a new cluster in SQL terminology. I think this is a bad idea, as it will create a backwards compatibility problem once we have actual catalogs for setCatalog to work with. I'd suggest that the right short-term thing is for setCatalog to either do nothing or throw an error. I agree. In which case the update Peter E did to DatabaseMetaData.getCatalogs() should be backed out. At least I've learnt how to use CVS and how to make context diffs :) regards, tom lane Jason Davies [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [JDBC] Connection.setCatalog()
[[[ Original Message from Peter Eisentraut [EMAIL PROTECTED] ]]] [EMAIL PROTECTED] writes: What do they mean by database though? It is vague because the definitions are not clear, but I interpret it like this: A catalog equals PostgreSQL database which is a subset of the whole database assuming database in this context to mean the whole server. I realise that the catalog is fixed but this is also the case for MySQL. Note it doesn't say in current connection so no reason why you can't create a new one. The bottom line is that supporting them would make life easier for people like me writing a database admin tool. This seems like a reasonable interpretation. Given that the alternative would be to not implement it at all, I would agree if you'd implement it as suggested. Great, thanks. Here is a context diff against current CVS for org.postgresql.Connection.get/setCatalog() - is this the correct place to implement the methods? - the no-op methods were replicated in both jdbc1 and jdbc2 subclasses. I've made the diff remove the no-op methods in the jdbc1 and jdbc2 subclasses. The DatabaseMetaData.supportsCatalogsInXXX() may need to be modified. These methods are all implemented correctly. I'm not sure about the stuff in DatabaseMetaData.getTables() for example - at the moment specifying null gets all the tables in the database which the driver is currently connected to. I think this is fine - but different database name patterns might be specified and they may have to be implemented? Yup. We'll just throw an SQLException in that case. Yes, that isn't really a problem. But _if_ you want to support catalogs maybe you have to go the whole way? Do you mean that DatabaseMetaData.getTables() in a catalog other than the current one should reset the connection to the new catalog? That sounds a bit too revolutionary to me, but then again, as above, the alternative is to not allow it at all. Yes. I agree that it's a bit pointless - it would make life easier for everyone if it was not allowed at all. Do you know whether PostgreSQL will support the catalogname.tablename syntax in SQL queries in the future? At the moment it seems as if catalogs are kinda half supported - you can list all catalogs on the system but you can't manipulate between them easily. For now at least I suggest we leave these methods alone. Thanks, Jason -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter Connection.diff ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [JDBC] DatabaseMetaData.getCatalogs()
[[[ Original Message from Tom Lane [EMAIL PROTECTED] ]]] [EMAIL PROTECTED] writes: Okay here's a context diff against CVS for getting table privileges. This appears not to be correct for current sources --- the privilege set has changed since 7.1. Ah you probably know better than me. It works in 7.1 for me, but does it have to be backwards compatible? Where can I find out how the new privilege set works? Thanks, Jason ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [JDBC] DatabaseMetaData.getCatalogs()
[[[ Original Message from Peter Eisentraut [EMAIL PROTECTED] ]]]
The list of columns you return is wrong. There is no COLUMN_NAME column.
Does PostgreSQL actually support privileges for individual columns?
TABLE_CAT should be filled in with the database name (should be available
via getConnection().getCatalog()). A missing TABLE_SCHEM should be set to
null (in the SQL sense). Also you have GRANTOR and GRANTEE mixed up,
besides the fact that PostgreSQL doesn't store the GRANTEE at all (yet).
Sorry about that. Surely the grantee is the username in the relacl field e.g.
{=,jason=arwR} and the grantor is the owner of the table?
Jason
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [JDBC] DatabaseMetaData.getCatalogs()
[[[ Original Message from Bruce Momjian [EMAIL PROTECTED] ]]] Just send over a context diff patch, either against 7.1.2 or CVS. Okay here's a context diff against CVS for getting table privileges. I may have more code to add (I noticed getColumnPrivileges() is not completed - could use my routine for parsing pg_class.relacl?). Jason Davies [EMAIL PROTECTED] diff ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
