Thread: Reading schema information

Sorry for missing the email subject in my previous email !

I am trying to get some information about a coloum using the method:

 DatabaseMetaData.getString("IS_GENERATEDCOLUMN")

However it is throwing an exception:

org.postgresql.util.PSQLException: The column name IS_GENERATEDCOLUMN
was not found in this ResultSet.

Based on javadoc for getColumns method:


http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)


We have:

IS_GENERATEDCOLUMN String => Indicates whether this is a generated column

YES --- if this a generated column
NO --- if this not a generated column
empty string --- if it cannot be determined whether this is a generated column


My questions is, is this a bug ? if not, how can I obtain this
information about a column (if it's generated or not) ?


Thank you.

Mansour Al Akeel wrote:
> Sorry for missing the email subject in my previous email !
>
> I am trying to get some information about a coloum using the method:
>
>   DatabaseMetaData.getString("IS_GENERATEDCOLUMN")
>
> However it is throwing an exception:
>
> org.postgresql.util.PSQLException: The column name IS_GENERATEDCOLUMN
> was not found in this ResultSet.
>
> Based on javadoc for getColumns method:
>
>
http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)
>
>
> We have:
>
> IS_GENERATEDCOLUMN String => Indicates whether this is a generated column
>
> YES --- if this a generated column
> NO --- if this not a generated column
> empty string --- if it cannot be determined whether this is a generated column
>
>
> My questions is, is this a bug ? if not, how can I obtain this
> information about a column (if it's generated or not) ?
>
>
> Thank you.
>

Hello,

Try the following attached code, may have some syntax errors.

A complete example of collecting database and table meta
data my be obtained from my project on GoogleCode.

danap.

http://code.google.com/p/myjsqlview/

http://code.google.com/p/myjsqlview/source/browse/trunk/myjsqlview/src/com/dandymadeproductions/myjsqlview/datasource/DatabaseProperties.java

http://code.google.com/p/myjsqlview/source/browse/trunk/myjsqlview/src/com/dandymadeproductions/myjsqlview/gui/panels/TableTabPanel_PostgreSQL.java

************************
Statement sqlStatement = dbConnection.createStatement();
String sqlStatementString = "SELECT * FROM " + "myTable" + " LIMIT 1";

ResultSet db_resultSet = sqlStatement.executeQuery(sqlStatementString);

DatabaseMetaData dbMetaData = dbConnection.getMetaData();
ResultSetMetaData tableMetaData = db_resultSet.getMetaData();

ResultSet rs1 = dbMetaData.getColumns("myDatabase",
                                       tableMetaData.getCatalogName(1),
                                       tableMetaData.getSchemaName(1),
                                       tableMetaData.getTableName(1));
String isGenerated = rs1.getString("IS_GENERATEDCOLUMN");

dmp wrote:
> Mansour Al Akeel wrote:
>> Sorry for missing the email subject in my previous email !
>>
>> I am trying to get some information about a coloum using the method:
>>
>>   DatabaseMetaData.getString("IS_GENERATEDCOLUMN")
>>
>> However it is throwing an exception:
>>
>> org.postgresql.util.PSQLException: The column name IS_GENERATEDCOLUMN
>> was not found in this ResultSet.
>>
>> Based on javadoc for getColumns method:
>>
>>
http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)
>>
>>
>>
>> We have:
>>
>> IS_GENERATEDCOLUMN String => Indicates whether this is a generated column
>>
>> YES --- if this a generated column
>> NO --- if this not a generated column
>> empty string --- if it cannot be determined whether this is a generated column
>>
>>
>> My questions is, is this a bug ? if not, how can I obtain this
>> information about a column (if it's generated or not) ?
>>
>>
>> Thank you.
>>

Hello,

I'm sorry, after testing, currently the given name IS_GENERATEDCOLUMN is
given the error as you describe. The index of 23 can be used to obtain the
results desired.

Did you search the mailing list for a report or issue on this?

danap.

Hello,

Yes I did a search and couldn't find anything. This line of code is
generating the same exception:

                 String SCOPE_CATALOG = fields.getString("SCOPE_CATALOG");



On Mon, May 25, 2015 at 6:58 PM, dmp <danap@ttc-cmc.net> wrote:
> dmp wrote:
>>
>> Mansour Al Akeel wrote:
>>>
>>> Sorry for missing the email subject in my previous email !
>>>
>>> I am trying to get some information about a coloum using the method:
>>>
>>>   DatabaseMetaData.getString("IS_GENERATEDCOLUMN")
>>>
>>> However it is throwing an exception:
>>>
>>> org.postgresql.util.PSQLException: The column name IS_GENERATEDCOLUMN
>>> was not found in this ResultSet.
>>>
>>> Based on javadoc for getColumns method:
>>>
>>>
>>>
http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)
>>>
>>>
>>>
>>> We have:
>>>
>>> IS_GENERATEDCOLUMN String => Indicates whether this is a generated column
>>>
>>> YES --- if this a generated column
>>> NO --- if this not a generated column
>>> empty string --- if it cannot be determined whether this is a generated
>>> column
>>>
>>>
>>> My questions is, is this a bug ? if not, how can I obtain this
>>> information about a column (if it's generated or not) ?
>>>
>>>
>>> Thank you.
>>>
>
> Hello,
>
> I'm sorry, after testing, currently the given name IS_GENERATEDCOLUMN is
> given the error as you describe. The index of 23 can be used to obtain the
> results desired.
>
> Did you search the mailing list for a report or issue on this?
>
> danap.
>

Please note, I am still using :

Connection conn = DriverManager.getConnection(info.getUrl(),
info.getUser(), info.getPassword());

        DatabaseMetaData metaData = conn.getMetaData();


So I am getting the meta data of the whole data base, and I didn't try
your method yet. You method obtains the meta data for each table as
far as I understand.



On Mon, May 25, 2015 at 6:49 PM, Mansour Al Akeel
<mansour.alakeel@gmail.com> wrote:
> Hello,
>
> Yes I did a search and couldn't find anything. This line of code is
> generating the same exception:
>
>                  String SCOPE_CATALOG = fields.getString("SCOPE_CATALOG");
>
>
>
> On Mon, May 25, 2015 at 6:58 PM, dmp <danap@ttc-cmc.net> wrote:
>> dmp wrote:
>>>
>>> Mansour Al Akeel wrote:
>>>>
>>>> Sorry for missing the email subject in my previous email !
>>>>
>>>> I am trying to get some information about a coloum using the method:
>>>>
>>>>   DatabaseMetaData.getString("IS_GENERATEDCOLUMN")
>>>>
>>>> However it is throwing an exception:
>>>>
>>>> org.postgresql.util.PSQLException: The column name IS_GENERATEDCOLUMN
>>>> was not found in this ResultSet.
>>>>
>>>> Based on javadoc for getColumns method:
>>>>
>>>>
>>>>
http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)
>>>>
>>>>
>>>>
>>>> We have:
>>>>
>>>> IS_GENERATEDCOLUMN String => Indicates whether this is a generated column
>>>>
>>>> YES --- if this a generated column
>>>> NO --- if this not a generated column
>>>> empty string --- if it cannot be determined whether this is a generated
>>>> column
>>>>
>>>>
>>>> My questions is, is this a bug ? if not, how can I obtain this
>>>> information about a column (if it's generated or not) ?
>>>>
>>>>
>>>> Thank you.
>>>>
>>
>> Hello,
>>
>> I'm sorry, after testing, currently the given name IS_GENERATEDCOLUMN is
>> given the error as you describe. The index of 23 can be used to obtain the
>> results desired.
>>
>> Did you search the mailing list for a report or issue on this?
>>
>> danap.
>>

dmp wrote:
> dmp wrote:
>> Mansour Al Akeel wrote:
>>> Sorry for missing the email subject in my previous email !
>>>
>>> I am trying to get some information about a coloum using the method:
>>>
>>>   DatabaseMetaData.getString("IS_GENERATEDCOLUMN")
>>>
>>> However it is throwing an exception:
>>>
>>> org.postgresql.util.PSQLException: The column name IS_GENERATEDCOLUMN
>>> was not found in this ResultSet.
>>>
>>> Based on javadoc for getColumns method:
>>>
>>>
http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)
>>>
>>>
>>>
>>>
>>> We have:
>>>
>>> IS_GENERATEDCOLUMN String => Indicates whether this is a generated column
>>>
>>> YES --- if this a generated column
>>> NO --- if this not a generated column
>>> empty string --- if it cannot be determined whether this is a generated column
>>>
>>>
>>> My questions is, is this a bug ? if not, how can I obtain this
>>> information about a column (if it's generated or not) ?
>>>
>>>
>>> Thank you.
>>>
>
> Hello,
>
> I'm sorry, after testing, currently the given name IS_GENERATEDCOLUMN is
> given the error as you describe. The index of 23 can be used to obtain the
> results desired.
>
> Did you search the mailing list for a report or issue on this?
>
> danap.
>

Hello,

Sorry for an earlier error.

rs = dbMetaData.getColumns(tableMetaData.getCatalogName(1),
             tableMetaData.getSchemaName(1),
             tableMetaData.getTableName(1), "%");

On further check, org.postgresql.Abstractjdbc2DatabaseMetaData.getColumns() does
not implement the named Field IS_GENERATEDCOLUMN. As indicated using an index
of 23 instead of the named Field should work.

danap.

Done. :)


On Mon, May 25, 2015 at 7:50 PM, Dave Cramer <pg@fastcrypt.com> wrote:
> Looks like a bug, can you please file it on github
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
> On 25 May 2015 at 11:21, dmp <danap@ttc-cmc.net> wrote:
>>
>> dmp wrote:
>>>
>>> dmp wrote:
>>>>
>>>> Mansour Al Akeel wrote:
>>>>>
>>>>> Sorry for missing the email subject in my previous email !
>>>>>
>>>>> I am trying to get some information about a coloum using the method:
>>>>>
>>>>>   DatabaseMetaData.getString("IS_GENERATEDCOLUMN")
>>>>>
>>>>> However it is throwing an exception:
>>>>>
>>>>> org.postgresql.util.PSQLException: The column name IS_GENERATEDCOLUMN
>>>>> was not found in this ResultSet.
>>>>>
>>>>> Based on javadoc for getColumns method:
>>>>>
>>>>>
>>>>>
http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> We have:
>>>>>
>>>>> IS_GENERATEDCOLUMN String => Indicates whether this is a generated
>>>>> column
>>>>>
>>>>> YES --- if this a generated column
>>>>> NO --- if this not a generated column
>>>>> empty string --- if it cannot be determined whether this is a generated
>>>>> column
>>>>>
>>>>>
>>>>> My questions is, is this a bug ? if not, how can I obtain this
>>>>> information about a column (if it's generated or not) ?
>>>>>
>>>>>
>>>>> Thank you.
>>>>>
>>>
>>> Hello,
>>>
>>> I'm sorry, after testing, currently the given name IS_GENERATEDCOLUMN is
>>> given the error as you describe. The index of 23 can be used to obtain
>>> the
>>> results desired.
>>>
>>> Did you search the mailing list for a report or issue on this?
>>>
>>> danap.
>>>
>>
>> Hello,
>>
>> Sorry for an earlier error.
>>
>> rs = dbMetaData.getColumns(tableMetaData.getCatalogName(1),
>>             tableMetaData.getSchemaName(1),
>>             tableMetaData.getTableName(1), "%");
>>
>> On further check,
>> org.postgresql.Abstractjdbc2DatabaseMetaData.getColumns() does
>> not implement the named Field IS_GENERATEDCOLUMN. As indicated using an
>> index
>> of 23 instead of the named Field should work.
>>
>>
>> danap.
>>
>>
>>
>>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>

On Mon, 25 May 2015 09:21:34 -0600, dmp <danap@ttc-cmc.net> wrote:
> dmp wrote:
>> dmp wrote:
>>> Mansour Al Akeel wrote:
...
>>>> We have:
>>>>
>>>> IS_GENERATEDCOLUMN String => Indicates whether this is a generated
>>>> column
>>>>
>>>> YES --- if this a generated column
>>>> NO --- if this not a generated column
>>>> empty string --- if it cannot be determined whether this is a
>>>> generated column
>>>>
>>>>
>>>> My questions is, is this a bug ? if not, how can I obtain this
>>>> information about a column (if it's generated or not) ?
>>>>
>> Hello,
>>
>> I'm sorry, after testing, currently the given name IS_GENERATEDCOLUMN
is
>> given the error as you describe. The index of 23 can be used to obtain
>> the
>> results desired.
>>
>> Did you search the mailing list for a report or issue on this?
>
> Sorry for an earlier error.
>
> rs = dbMetaData.getColumns(tableMetaData.getCatalogName(1),
>              tableMetaData.getSchemaName(1),
>              tableMetaData.getTableName(1), "%");
>
> On further check,
> org.postgresql.Abstractjdbc2DatabaseMetaData.getColumns() does
> not implement the named Field IS_GENERATEDCOLUMN. As indicated using an
> index
> of 23 instead of the named Field should work.

Index 23 is IS_AUTOINCREMENT which does not have the same meaning as
IS_GENERATEDCOLUMN (index 24) which was added to the JDBC specification in
Java 7 / JDBC 4.1. The former is only for one specific column type: auto
increment (eg serial), while the latter is for all calculated or otherwise
generated columns.

Mark

 > Index 23 is IS_AUTOINCREMENT which does not have the same meaning as
 > IS_GENERATEDCOLUMN (index 24) which was added to the JDBC specification in
 > Java 7 / JDBC 4.1. The former is only for one specific column type: auto
 > increment (eg serial), while the latter is for all calculated or otherwise
 > generated columns.
 >
 > Mark

Yes, that is correct. I was looling at the code array indexing not the
API. rs.getString(24) is the correct index and IS_GENERATEDCOLUMN is not
implemented in getColumns().

danap.