PostgreSQL JDBC - ResultSetMetaData precise typ information

Author

ResultSetMetaData precise typ information

stefanlack@gmx.de

2005-07-21, 7:23 am

I'm interested in retrieving the most
precise type information as possible from PreparedStatements.
I'm using this driver: 8.1dev-400 JDBC 3

1) ResultSetMetaData.getTableName(int column) is returning "".
Isn't it possible to return the result of getBaseTableName(int
column) in
getTableName, like this

public abstract class AbstractJdbc2ResultS
etMetaData .. {
..
public String getTableName(int column) throws SQLException
{
//return "";
return getBaseTableName(col
umn);
}
..
}

2)Is there a way to determine "length information" for result types in
perpared statements?

The goal is not to get the length of the actual result after executing the
query, of course!
Example:
String query = "SELECT a, b,c FROM table1,table2;
PreparedStatement stmt = getConnection(). prepareStatement(que
ry);
ResultSetMetaData data = stmt.getMetaData();

For bit values
If "a" is a bit(x) column, is it possible to retrieve the value of x from
the ResultSetMetaData?
Both "data.getScale(index)" and "data.getPrecision(index)" are returning
"0", not the value expected (e.g. bit(21) -> 21)

The same problem holds for character varying(x)

One could use the
DatabaseMetaData.getTables(...) method. The retrieved ResultSet contains
many usefull data,
like
this:
rset.getInt("COLUMN_SIZE"),rset.getInt("DECIMAL_DIGITS"),rset.getInt("CHAR_OCTET_LENGTH").

But I can see no way to identify the query results' columns. In the example:
- is "a" referencing "table1" or "table2" ?
- In the query "Select a as b from table1;" the
data. getColumnName(index)
will return "b", so the true columnname is not
available.
May it be possible to get a reference to the original column, or is this
feature not part of of the current jdbc Specification?

3) ResultSetMetaData.isNullable(int column) returns not always expected
information when used for prepared Statements
Example:

String query = "SELECT a, b,c FROM table1 WHERE a notnull and b notnull
and c notnull
PreparedStatement stmt = getConnection(). prepareStatement(que
ry);
ResultSetMetaData data = stmt.getMetaData();

now, data.isNullable(1),data.isNullable(2),data.isNullable(3) are returning
1.
I'm aware of the fact that the column "a" is maybe nullable due to the table
definition,
but clearly in this query a,b,c can never be null values.

Thanks,

Stefan Lack

--
5 GB Mailbox, 50 FreeSMS http://www.gmx.net/de/go/promail
+++ GMX - die erste Adresse f�r Mail, Message, More +++

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Dave Cramer

2005-07-21, 9:23 am

On 21-Jul-05, at 7:45 AM, stefanlack@gmx.de wrote:

> I'm interested in retrieving the most
> precise type information as possible from PreparedStatements.
> I'm using this driver: 8.1dev-400 JDBC 3
>
> 1) ResultSetMetaData.getTableName(int column) is returning "".
> Isn't it possible to return the result of getBaseTableName(int

> column) in
> getTableName, like this
>
> public abstract class AbstractJdbc2ResultS
etMetaData .. {
> ..
> public String getTableName(int column) throws SQLException
> {
> //return "";
> return getBaseTableName(col
umn);
> }
> ..
> }

You're correct, does anyone know why getTableName is returning "",

when getBaseTableName is implemented ?

>
> 2)Is there a way to determine "length information" for result types in
> perpared statements?
>
> The goal is not to get the length of the actual result after
> executing the
> query, of course!
> Example:
> String query = "SELECT a, b,c FROM table1,table2;
> PreparedStatement stmt = getConnection(). prepareStatement(que
ry);
> ResultSetMetaData data = stmt.getMetaData();
>
> For bit values
> If "a" is a bit(x) column, is it possible to retrieve the value
> of x from
> the ResultSetMetaData?
> Both "data.getScale(index)" and "data.getPrecision(index)" are
> returning
> "0", not the value expected (e.g. bit(21) -> 21)
getPrecision is defined as the number of digits to the left of the
decimal, so this should be 1
getScale is defined as the number of digits to the right, this should
be 0
>
> The same problem holds for character varying(x)
This appears to be wrong in the driver, as it is returning 0.
>
> One could use the
> DatabaseMetaData.getTables(...) method. The retrieved ResultSet
> contains
> many usefull data,
> like
> this:
> rset.getInt("COLUMN_SIZE"),rset.getInt("DECIMAL_DIGITS"),rset.getInt
> ("CHAR_OCTET_LENGTH").
>
> But I can see no way to identify the query results' columns. In the
> example:
> - is "a" referencing "table1" or "table2" ?
> - In the query "Select a as b from table1;" the
> data. getColumnName(index)
will return "b", so the true columnname
> is not
> available.
> May it be possible to get a reference to the original column, or
> is this
> feature not part of of the current jdbc Specification?
>
> 3) ResultSetMetaData.isNullable(int column) returns not always
> expected
> information when used for prepared Statements
> Example:
>
> String query = "SELECT a, b,c FROM table1 WHERE a notnull and b
> notnull
> and c notnull
> PreparedStatement stmt = getConnection(). prepareStatement(que
ry);
> ResultSetMetaData data = stmt.getMetaData();
>
> now, data.isNullable(1),data.isNullable(2),data.isNullable(3) are
> returning
> 1.
> I'm aware of the fact that the column "a" is maybe nullable due to
> the table
> definition,
> but clearly in this query a,b,c can never be null values.

I'm pretty sure that isNullable(n) refers to the attribute of the
table, not the data itself.
>
>
> Thanks,
>
> Stefan Lack
>
> --
> 5 GB Mailbox, 50 FreeSMS http://www.gmx.net/de/go/promail
> +++ GMX - die erste Adresse f�r Mail, Message, More +++
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>

Dave Cramer
davec@postgresintl.com
www.postgresintl.com
ICQ #14675561
jabber davecramer@jabber.org
ph (519 939 0336 )

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Oliver Jowett

2005-07-21, 8:24 pm

Dave Cramer wrote:

> You're correct, does anyone know why getTableName is returning "",
>
> when getBaseTableName is implemented ?

See lengthy previous discussion in the archives. getTableName() needs to
return the table *alias* or the empty string if unknown. The backend
only gives us the actual table name (returned by getBaseTableName());
we
don't know what the alias is and return an empty string.

-O

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sponsored Links