Thread: Metadata for tables, schema, and numeric

Metadata for tables, schema, and numeric

From
Mike Finn
Date:
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


Re: Metadata for tables, schema, and numeric

From
Bruce Momjian
Date:
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

Re: Metadata for tables, schema, and numeric

From
Mike Finn
Date:
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

Re: Metadata for tables, schema, and numeric

From
Peter Eisentraut
Date:
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


Re: Metadata for tables, schema, and numeric

From
Ola Sundell
Date:
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


Re: Metadata for tables, schema, and numeric

From
Mike Finn
Date:
> 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

Re: Metadata for tables, schema, and numeric

From
Tom Lane
Date:
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

Re: Metadata for tables, schema, and numeric

From
Peter Eisentraut
Date:
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


Re: Metadata for tables, schema, and numeric

From
Ola Sundell
Date:
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