Postgres Pro
Downloads
Home   >   mailing lists

Thread: Why are clobs always "0"

Why are clobs always "0"

From
Arnie Morein
Date:
I have tested the most recent driver in three different SQL IDEs, and now with an application I'm writing that uses JDBC metadata, the comment on a field definition also isn't available as a string value.

The only thing I ever see regarding data type "text" field values are either a 0 or a 1; neither of which applies.

So why is this happening, even from the JDBC metadata results as well?

Re: Why are clobs always "0"

From
Adrian Klaver
Date:
On 12/1/19 9:31 AM, Arnie Morein wrote:
> I have tested the most recent driver in three different SQL IDEs, and 
> now with an application I'm writing that uses JDBC metadata, the comment 
> on a field definition also isn't available as a string value.
> 
> The only thing I ever see regarding data type "text" field values are 
> either a 0 or a 1; neither of which applies.
> 
> So why is this happening, even from the JDBC metadata results as well?


You are going to have to be more specific:

1) You refer to clob, but the clob datatype is not supported:
https://www.postgresql.org/docs/11/unsupported-features-sql-standard.html
So what are you referring to?

2) Comment on field definition is what exactly?

3) How are you fetching the metadata?


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Sv: Why are clobs always "0"

From
Andreas Joseph Krogh
Date:
På søndag 01. desember 2019 kl. 18:31:35, skrev Arnie Morein <arnie.morein@mac.com>:
I have tested the most recent driver in three different SQL IDEs, and now with an application I'm writing that uses JDBC metadata, the comment on a field definition also isn't available as a string value.
 
The only thing I ever see regarding data type "text" field values are either a 0 or a 1; neither of which applies.
 
So why is this happening, even from the JDBC metadata results as well?
 
Have you tried the NG-driver: https://github.com/impossibl/pgjdbc-ng
We use it with Blobs/Clobs and it's working good.
 
It would help us help you if you mention which IDEs you have tried, and provide configuration-paramteres, error-messages etc.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

Re: Why are clobs always "0"

From
Thomas Kellerer
Date:
Arnie Morein schrieb am 01.12.2019 um 18:31:
> I have tested the most recent driver in three different SQL IDEs, and
> now with an application I'm writing that uses JDBC metadata, the
> comment on a field definition also isn't available as a string
> value.
>
> The only thing I ever see regarding data type "text" field values are
> either a 0 or a 1; neither of which applies.
>
> So why is this happening, even from the JDBC metadata results as
> well?

The Postgres JDBC driver does not have any problems with the "text"
data type, neither with reporting it properly through DatabaseMetaData
(it's reported as Types.VARCHAR) nor with retrieving values from
such a column.

The column size for such a column is reported as 2147483647

Column comments are reliably returned in the column "REMARKS" in the
result of DatabaseMetaData.getColumns()

Given the following table:

     create table test (data text);
     comment on column test.data is 'The text column';

then the following Java code:

     ResultSet rs = connection.getDatabaseMetaData().getColumn(null, "public", "test", "%");
     rs.next();
     System.out.println("column_name: " + rs.getString("COLUMN_NAME"));
     System.out.println("column_size: " + rs.getInt("COLUMN_SIZE"));
     System.out.println("data_type: " + rs.getInt("DATA_TYPE"));
     System.out.println("remarks: " + rs.getString("REMARKS"));

will output:

   column_name: data
   column_size: 2147483647
   data_type: 12
   remarks: The text column

With 12 being the value of java.sql.Types.VARCHAR

Thomas