Thread: Metadata for tables, schema, and numeric
Using 7.1 the following don't ever seem to return anything
getCatalogName(int)
getSchemaName(int)
getTableName(int)
has this been implemented in a newer version of the JDBC driver
If yes... where to get it?
If no... is it planned?
As well
getPrecision(int)
always returns 0 for type numeric(p,s). I checked the source for the driver
(jdbc2) and it seems to always set this to zero as it is not a type checked
for in the switch list (or cascading if's -- it was yesterday when I checked).
Is this because the backend does not provide this information to a client or
is it simply a matter of little more code to the driver to explicitly check
for a few more types?
gb=> \d customer
Table "customer"
Attribute | Type | Modifier
---------------------------+---------------+----------
seqcustomer | numeric(15,0) | not null
name | text | not null
<other fields>
and executing this query
select customer.seqCustomer from customer
gives the following
getColumnName: seqcustomer
getCatalogName:
getColumnLabel: seqcustomer
getSchemaName:
getTableName:
getPrecision: 0
getScale: 0
Thanks in advance.
Mike
===================
Mike Finn
Tactical Executive Systems
mike.finn-pgsql-jdbc@tacticalExecutive.com
Please try the newer jdbc driver at:
http://jdbc.fastcrypt.com
This may fix those problems.
> Using 7.1 the following don't ever seem to return anything
>
> getCatalogName(int)
> getSchemaName(int)
> getTableName(int)
>
> has this been implemented in a newer version of the JDBC driver
> If yes... where to get it?
> If no... is it planned?
>
> As well
> getPrecision(int)
> always returns 0 for type numeric(p,s). I checked the source for the driver
> (jdbc2) and it seems to always set this to zero as it is not a type checked
> for in the switch list (or cascading if's -- it was yesterday when I checked).
>
> Is this because the backend does not provide this information to a client or
> is it simply a matter of little more code to the driver to explicitly check
> for a few more types?
>
>
> gb=> \d customer
> Table "customer"
> Attribute | Type | Modifier
> ---------------------------+---------------+----------
> seqcustomer | numeric(15,0) | not null
> name | text | not null
> <other fields>
>
> and executing this query
>
> select customer.seqCustomer from customer
>
> gives the following
>
> getColumnName: seqcustomer
> getCatalogName:
> getColumnLabel: seqcustomer
> getSchemaName:
> getTableName:
> getPrecision: 0
> getScale: 0
>
>
> Thanks in advance.
> Mike
>
> ===================
> Mike Finn
> Tactical Executive Systems
> mike.finn-pgsql-jdbc@tacticalExecutive.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Wednesday 04 July 2001 09:06, you wrote: > Please try the newer jdbc driver at: > > http://jdbc.fastcrypt.com > > This may fix those problems. > Bruce thank-you for your response. The new driver jdbc7.1-1.3.jar did in fact report precision, and scale properly -- and for that I am grateful. Without trying to push my luck, however... there is still no return value from getCatalogName(int) getSchemaName(int) getTableName(int) Is this something that can be done or is the data just not available from postgresql? (ie can I expect to see it in a future release?) Thanks again. Mike =================== Mike Finn Tactical Executive Systems mike.finn@tacticalExecutive.com
Mike Finn writes: > there is still no return value from > > getCatalogName(int) > getSchemaName(int) > getTableName(int) > > Is this something that can be done or is the data just not available from > postgresql? (ie can I expect to see it in a future release?) These functions are fundamentally flawed, because it's impossible to determine the table name that a result column came from, except in the most simple cases in which case you don't need these functions. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
On Thu, 5 Jul 2001, Peter Eisentraut wrote: > Mike Finn writes: > > > there is still no return value from > > > > getCatalogName(int) > > getSchemaName(int) > > getTableName(int) > > > > Is this something that can be done or is the data just not available from > > postgresql? (ie can I expect to see it in a future release?) > > These functions are fundamentally flawed, because it's impossible to > determine the table name that a result column came from, except in the > most simple cases in which case you don't need these functions. Ho, hum. I disagree. You can either parse the query at the client side, which is flawed, imo, or you can make the backend send the information along with the rest of the Field metadata. Yes, I know that would be a major change, that would break all existing interfaces. Yes, I know it would be a pain in the latter regions to fix. One thing you might want to consider, though, is that the information is necessary for the UpdateableResultSets. How, else, are you going to execute the updates? Ola -- Ola Sundell ola@miranda.org - olas@wiw.org - ola.sundell@upright.se http://miranda.org/~ola
> These functions are fundamentally flawed, because it's impossible to > determine the table name that a result column came from, except in the > most simple cases in which case you don't need these functions. Oh well. Thanks - again. Mike P.S. Just for my own understanding ... Wouldn't the backend have to know which table and schema(=db) the column came from just to be able to parse the query for execution? I would have thought this is the case and it just that the current protocol does not provide for this info to be 'released' to the client end. =================== Mike Finn Tactical Executive Systems mike.finn@tacticalExecutive.com
Ola Sundell <ola@miranda.org> writes:
> On Thu, 5 Jul 2001, Peter Eisentraut wrote:
>> These functions are fundamentally flawed, because it's impossible to
>> determine the table name that a result column came from, except in the
>> most simple cases in which case you don't need these functions.
> I disagree. You can either parse the query at the client side, which is
> flawed, imo, or you can make the backend send the information along with
> the rest of the Field metadata.
Peter's point is that in the general case there is no reasonable answer.
A few counterexamples:
select a.f1 + b.f2 as sum from a,b where ...
Which table would you like to say the output column "sum" is from?
select * from a inner join b using (id);
This query will join a and b on "a.id = b.id". Per SQL92, the output
will have only one column named id, not two. Which table would you like
to say the column came from?
select f1 from a UNION select f2 from b;
The usual question.
> One thing you might want to consider,
> though, is that the information is necessary for the
> UpdateableResultSets. How, else, are you going to execute the updates?
While I haven't read the JDBC spec, I'd have to guess that the entire
notion of an updateable result set is defined only for a very small
subset of possible SELECT queries --- small enough that deducing the
requested information is pretty trivial. In particular, I don't
believe that the notion of an updateable result set can be considered
well-defined for *any* query that involves a join. Therefore there is
only one table in the FROM clause, and deducing the correct result
for getTableName() is trivial.
regards, tom lane
Tom Lane writes: > While I haven't read the JDBC spec, I'd have to guess that the entire > notion of an updateable result set is defined only for a very small > subset of possible SELECT queries FYI: | Due to differences in database implementations, the JDBC API does not | specify an exact set of SQL queries which must yield an updatable result | set for JDBC drivers that support updatability. Developers can, however, | generally expect queries which meet the following criteria to produce an | updatable result set: | | 1. The query references only a single table in the database. | 2. The query does not contain any join operations. | 3. The query selects the primary key of the table it references. | | In addition, an SQL query should also satisfy the conditions listed | below if inserts are to be performed. | | 4. The query selects all of the non-nullable columns in the | underlying table. | 5. The query selects all columns that don't have a default value. I would argue that a minority of all queries in real life statisfy 1. through 3, in addition to the restriction of not using functions. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
On Thu, 5 Jul 2001, Peter Eisentraut wrote: > Tom Lane writes: > > > While I haven't read the JDBC spec, I'd have to guess that the entire > > notion of an updateable result set is defined only for a very small > > subset of possible SELECT queries > > FYI: <snip> Point taken. In the future I will, of course, consult the spec before making wild assumptions. One question that pops up in my mind, after having read the jdbc spec, is what to do, if someone tries to update a ResultSet which is a UNION, for instance. If I was using the jdbc driver, and I did just that, I'd at least like to get some sort of error message, telling me that my course of action was flawed. Ola -- Ola Sundell ola@miranda.org - olas@wiw.org - ola.sundell@upright.se http://miranda.org/~ola