Thread: TEXT columns should indentify as java.sql.Types.CLOB

TEXT columns should indentify as java.sql.Types.CLOB

From

Toni Helenius

Date:

16 August 2010, 09:11:46

Hello,

I'm using Postgres 8.3.11 database and the latest JDBC driver 8.4 Build 701 (JDBC 4) + Java 6. In our databases there
areTEXT type columns. However if I make a query to identify these fields in Java, the field DATA TYPE is VARCHAR and
thelength is 2147483647. Type name is correct; "TEXT". But as we need database independent code, we are using DATA TYPE
asI presume is correct. And I think these TEXT fields should return java.sql.Types.CLOB as DATA TYPE instead of
VARCHAR.

Here is some code:

Connection fromConn;
DatabaseMetaData metaFrom;
String userFrom;

fromConn = from.getConnection();
metaFrom = fromConn.getMetaData();
userFrom = ((PooledConnection)from).getTableOwner();

ResultSet cols = metaFrom.getColumns(null, userFrom, code, null);
while (cols.next()) {
    cols.getShort("DATA_TYPE");
    cols.getString("TYPE_NAME");
    }

--
Toni Helenius / Syncron Tech Oy
Lappeenranta, Finland

Re: TEXT columns should indentify as java.sql.Types.CLOB

From

Thomas Kellerer

Date:

16 August 2010, 09:30:19

Toni Helenius wrote on 16.08.2010 11:01:
> I'm using Postgres 8.3.11 database and the latest JDBC driver 8.4
> Build 701 (JDBC 4) + Java 6. In our databases there are TEXT type
> columns. However if I make a query to identify these fields in Java,
> the field DATA TYPE is VARCHAR and the length is 2147483647. Type
> name is correct; "TEXT". But as we need database independent code, we
> are using DATA TYPE as I presume is correct. And I think these TEXT
> fields should return java.sql.Types.CLOB as DATA TYPE instead of
> VARCHAR.

I agree, this would make the driver more JDBC compatible and it would behave more like other drivers.

Regards
Thomas

Re: TEXT columns should indentify as java.sql.Types.CLOB

From

Oliver Jowett

Date:

16 August 2010, 11:18:45

Thomas Kellerer wrote:
> Toni Helenius wrote on 16.08.2010 11:01:
>> I'm using Postgres 8.3.11 database and the latest JDBC driver 8.4
>> Build 701 (JDBC 4) + Java 6. In our databases there are TEXT type
>> columns. However if I make a query to identify these fields in Java,
>> the field DATA TYPE is VARCHAR and the length is 2147483647. Type
>> name is correct; "TEXT". But as we need database independent code, we
>> are using DATA TYPE as I presume is correct. And I think these TEXT
>> fields should return java.sql.Types.CLOB as DATA TYPE instead of
>> VARCHAR.
>
> I agree, this would make the driver more JDBC compatible and it would
> behave more like other drivers.

Returning Types.CLOB would imply that the preferred way to deal with
those columns is via getClob() / setClob(), which surely isn't correct
for TEXT columns in most cases?

-O

Re: TEXT columns should indentify as java.sql.Types.CLOB

From

Thomas Kellerer

Date:

16 August 2010, 13:48:07

Oliver Jowett wrote on 16.08.2010 13:18:
> Thomas Kellerer wrote:
>> Toni Helenius wrote on 16.08.2010 11:01:
>>> I'm using Postgres 8.3.11 database and the latest JDBC driver 8.4
>>> Build 701 (JDBC 4) + Java 6. In our databases there are TEXT type
>>> columns. However if I make a query to identify these fields in Java,
>>> the field DATA TYPE is VARCHAR and the length is 2147483647. Type
>>> name is correct; "TEXT". But as we need database independent code, we
>>> are using DATA TYPE as I presume is correct. And I think these TEXT
>>> fields should return java.sql.Types.CLOB as DATA TYPE instead of
>>> VARCHAR.
>>
>> I agree, this would make the driver more JDBC compatible and it would
>> behave more like other drivers.
>
> Returning Types.CLOB would imply that the preferred way to deal with
> those columns is via getClob() / setClob(), which surely isn't correct
> for TEXT columns in most cases?
>

Not necessarily.

Almost all drivers I know (Oracle, jTDS, MySQL and DB2 I think) can handle getString() on a CLOB column without
problems.(Oracle only since it's 10.x drivers) 

Regards
Thomas

Re: TEXT columns should indentify as java.sql.Types.CLOB

From

"Kevin Grittner"

Date:

16 August 2010, 14:28:47

Thomas Kellerer <spam_eater@gmx.net> wrote:
> Oliver Jowett wrote on 16.08.2010 13:18:

>> Returning Types.CLOB would imply that the preferred way to deal
>> with those columns is via getClob() / setClob(), which surely
>> isn't correct for TEXT columns in most cases?
>>
>
> Not necessarily.
>
> Almost all drivers I know (Oracle, jTDS, MySQL and DB2 I think)
> can handle getString() on a CLOB column without problems. (Oracle
> only since it's 10.x drivers)

I'm with Oliver.  I've developed a lot of highly portable code, and
*my* assumption would be that a column categorized as Types.CLOB
would support getClob() / setClob().  That it might or might not
support getString() seems pretty irrelevant to the issue.

Your argument seems to be that if something behaves like character
varying without the length limitations of some other database
products on such types, it should be considered CLOB.  I think it
should be related to whether the CLOB access methods are supported.

-Kevin

Re: TEXT columns should indentify as java.sql.Types.CLOB

From

Thomas Kellerer

Date:

16 August 2010, 14:53:15

Kevin Grittner wrote on 16.08.2010 16:28:
> I'm with Oliver.  I've developed a lot of highly portable code, and
> *my* assumption would be that a column categorized as Types.CLOB
> would support getClob() / setClob().  That it might or might not
> support getString() seems pretty irrelevant to the issue.
>
> Your argument seems to be that if something behaves like character
> varying without the length limitations of some other database
> products on such types, it should be considered CLOB.  I think it
> should be related to whether the CLOB access methods are supported.

You have a point here.

So _if_ the driver were to return Types.CLOB it would need to implement getClob()/setClob() as well.
I still think it would be more consitent, to flag those columns as CLOB but I underst that it would require a lot more
workthan simply changing the datatype return value. 


Regards
Thomas

Re: TEXT columns should indentify as java.sql.Types.CLOB

From

"Donald Fraser"

Date:

16 August 2010, 15:13:14

> So _if_ the driver were to return Types.CLOB it would need to implement
> getClob()/setClob() as well.
> I still think it would be more consitent, to flag those columns as CLOB
> but I underst that it would require a lot more work than simply changing
> the datatype return value.

I cannot see an argument for "more consistant".

Please read the following documentation taken from:
http://download-llnw.oracle.com/javase/1.3/docs/guide/jdbc/getstart/mapping.html

Mapping SQL and Java Types
NOTE: The material in this chapter is based on JDBCtm API Tutorial and
Reference, Second Edition: Universal Data Access for the Javatm 2 Platform,
published by Addison Wesley as part of the Java series, ISBN 0-201-43328-1.

8.3.1 CHAR, VARCHAR, and LONGVARCHAR
The JDBC types CHAR, VARCHAR, and LONGVARCHAR are closely related. CHAR
represents a small, fixed-length character string, VARCHAR represents a
small, variable-length character string, and LONGVARCHAR represents a large,
variable-length character string.

8.4.2 CLOB
The JDBC type CLOB represents the SQL3 type CLOB (Character Large Object).

A JDBC CLOB value is mapped to an instance of the Clob interface in the Java
programming language. If a driver follows the standard implementation, a
Clob object logically points to the CLOB value on the server rather than
containing its character data, greatly improving efficiency. Two of the
methods on the Clob interface materialize the data of a CLOB object on the
client.

Re: TEXT columns should indentify as java.sql.Types.CLOB

From

dmp

Date:

16 August 2010, 15:44:39

>
>
>Hello,
>
>I'm using Postgres 8.3.11 database and the latest JDBC driver 8.4 Build 701 (JDBC 4) + Java 6. In our databases there
areTEXT type columns. However if I make a query to identify these fields in Java, the field DATA TYPE is VARCHAR and
thelength is 2147483647. Type name is correct; "TEXT". But as we need database independent code, we are using DATA TYPE
asI presume is correct. And I think these TEXT fields should return java.sql.Types.CLOB as DATA TYPE instead of
VARCHAR.
>
>Here is some code:
>
>Connection fromConn;
>DatabaseMetaData metaFrom;
>String userFrom;
>
>fromConn = from.getConnection();
>metaFrom = fromConn.getMetaData();
>userFrom = ((PooledConnection)from).getTableOwner();
>
>ResultSet cols = metaFrom.getColumns(null, userFrom, code, null);
>while (cols.next()) {
>    cols.getShort("DATA_TYPE");
>    cols.getString("TYPE_NAME");
>    }
>
I'm just not seeing it. Your code example seems to be collecting the
information
from the database connection not a particular table. If your application
needs
to identify column types regardless of different databases then the way
I do it
is through evaluation of the table columns. Attached file containing the
output
for the last three or so PostgreSQL JDBC drivers. As far as TEXT and CLOB
types I would prefer then to be identifed independently.

danap

String sqlStatementString = "SELECT * FROM " + schemaTableName + " LIMIT 1";
ResultSet db_resultSet = sqlStatement.executeQuery(sqlStatementString);
DatabaseMetaData dbMetaData = dbConnection.getMetaData();
ResultSetMetaData tableMetaData = db_resultSet.getMetaData();

for (int i = 1; i < tableMetaData.getColumnCount() + 1; i++)
{
// Collect Information on Column.

colNameString = tableMetaData.getColumnName(i);
comboBoxNameString = parseColumnNameField(colNameString);
columnClass = tableMetaData.getColumnClassName(i);
columnType = tableMetaData.getColumnTypeName(i);
columnSize = Integer.valueOf(tableMetaData.getColumnDisplaySize(i));

System.out.println(i + " " + colNameString + " " +
comboBoxNameString + " " +
columnClass + " " + columnType + " " +
columnSize);
}
-----------------------
PostgreSQL 8.4-701

1 data_type_id Data Type Id java.lang.Integer int4 11
2 smallint_type Smallint Type java.lang.Integer int2 6
3 int_type Int Type java.lang.Integer int4 11
4 bigint_type Bigint Type java.lang.Long int8 20
5 decimal_type Decimal Type java.math.BigDecimal numeric 18
6 numeric_type Numeric Type java.math.BigDecimal numeric 12
7 real_type Real Type java.lang.Float float4 14
8 doubleprecision_type Doubleprecision Type java.lang.Double float8 24
9 serial_type Serial Type java.lang.Integer int4 11
10 bigserial_type Bigserial Type java.lang.Long int8 20
11 varchar_type Varchar Type java.lang.String varchar 30
12 char_type Char Type java.lang.String bpchar 30
13 text_type Text Type java.lang.String text 2147483647
14 bytea_type Bytea Type [B bytea 2147483647
15 date_type Date Type java.sql.Date date 13
16 time_type Time Type java.sql.Time time 15
17 timetz_type Timetz Type java.sql.Time timetz 21
18 timestamp_type Timestamp Type java.sql.Timestamp timestamp 29
19 timestamptz_type Timestamptz Type java.sql.Timestamp timestamptz 35
20 interval_type Interval Type org.postgresql.util.PGInterval interval 49
21 boolean_type Boolean Type java.lang.Boolean bool 1
22 point_type Point Type org.postgresql.geometric.PGpoint point 2147483647
23 linesegment_type Linesegment Type org.postgresql.geometric.PGlseg lseg 2147483647
24 box_type Box Type org.postgresql.geometric.PGbox box 2147483647
25 path_type Path Type org.postgresql.geometric.PGpath path 2147483647
26 polygon_type Polygon Type org.postgresql.geometric.PGpolygon polygon 2147483647
27 circle_type Circle Type org.postgresql.geometric.PGcircle circle 2147483647
28 cidr_type Cidr Type java.lang.Object cidr 2147483647
29 inet_type Inet Type java.lang.Object inet 2147483647
30 macaddr_type Macaddr Type java.lang.Object macaddr 2147483647
31 bit2_type Bit2 Type java.lang.Boolean bit 2
32 bitvarying5_type Bitvarying5 Type java.lang.Object varbit 5

-----------------------
PostgreSQL 8.3-603

1 data_type_id Data Type Id java.lang.Integer int4 11
2 smallint_type Smallint Type java.lang.Integer int2 6
3 int_type Int Type java.lang.Integer int4 11
4 bigint_type Bigint Type java.lang.Long int8 20
5 decimal_type Decimal Type java.math.BigDecimal numeric 18
6 numeric_type Numeric Type java.math.BigDecimal numeric 12
7 real_type Real Type java.lang.Float float4 14
8 doubleprecision_type Doubleprecision Type java.lang.Double float8 24
9 serial_type Serial Type java.lang.Integer int4 11
10 bigserial_type Bigserial Type java.lang.Long int8 20
11 varchar_type Varchar Type java.lang.String varchar 30
12 char_type Char Type java.lang.String bpchar 30
13 text_type Text Type java.lang.String text 2147483647
14 bytea_type Bytea Type [B bytea 2147483647
15 date_type Date Type java.sql.Date date 13
16 time_type Time Type java.sql.Time time 15
17 timetz_type Timetz Type java.sql.Time timetz 21
18 timestamp_type Timestamp Type java.sql.Timestamp timestamp 29
19 timestamptz_type Timestamptz Type java.sql.Timestamp timestamptz 35
20 interval_type Interval Type org.postgresql.util.PGInterval interval 49
21 boolean_type Boolean Type java.lang.Boolean bool 1
22 point_type Point Type org.postgresql.geometric.PGpoint point 2147483647
23 linesegment_type Linesegment Type org.postgresql.geometric.PGlseg lseg 2147483647
24 box_type Box Type org.postgresql.geometric.PGbox box 2147483647
25 path_type Path Type org.postgresql.geometric.PGpath path 2147483647
26 polygon_type Polygon Type org.postgresql.geometric.PGpolygon polygon 2147483647
27 circle_type Circle Type org.postgresql.geometric.PGcircle circle 2147483647
28 cidr_type Cidr Type java.lang.Object cidr 2147483647
29 inet_type Inet Type java.lang.Object inet 2147483647
30 macaddr_type Macaddr Type java.lang.Object macaddr 2147483647
31 bit2_type Bit2 Type java.lang.Boolean bit 2
32 bitvarying5_type Bitvarying5 Type java.lang.Object varbit 5

-----------------------
PostgreSQL 8.2-506

1 data_type_id Data Type Id java.lang.Integer int4 11
2 smallint_type Smallint Type java.lang.Integer int2 6
3 int_type Int Type java.lang.Integer int4 11
4 bigint_type Bigint Type java.lang.Long int8 20
5 decimal_type Decimal Type java.math.BigDecimal numeric 18
6 numeric_type Numeric Type java.math.BigDecimal numeric 12
7 real_type Real Type java.lang.Float float4 14
8 doubleprecision_type Doubleprecision Type java.lang.Double float8 24
9 serial_type Serial Type java.lang.Integer int4 11
10 bigserial_type Bigserial Type java.lang.Long int8 20
11 varchar_type Varchar Type java.lang.String varchar 30
12 char_type Char Type java.lang.String bpchar 30
13 text_type Text Type java.lang.String text 2147483647
14 bytea_type Bytea Type [B bytea 2147483647
15 date_type Date Type java.sql.Date date 13
16 time_type Time Type java.sql.Time time 15
17 timetz_type Timetz Type java.sql.Time timetz 21
18 timestamp_type Timestamp Type java.sql.Timestamp timestamp 29
19 timestamptz_type Timestamptz Type java.sql.Timestamp timestamptz 35
20 interval_type Interval Type org.postgresql.util.PGInterval interval 49
21 boolean_type Boolean Type java.lang.Boolean bool 1
22 point_type Point Type org.postgresql.geometric.PGpoint point 2147483647
23 linesegment_type Linesegment Type org.postgresql.geometric.PGlseg lseg 2147483647
24 box_type Box Type org.postgresql.geometric.PGbox box 2147483647
25 path_type Path Type org.postgresql.geometric.PGpath path 2147483647
26 polygon_type Polygon Type org.postgresql.geometric.PGpolygon polygon 2147483647
27 circle_type Circle Type org.postgresql.geometric.PGcircle circle 2147483647
28 cidr_type Cidr Type java.lang.Object cidr 2147483647
29 inet_type Inet Type java.lang.Object inet 2147483647
30 macaddr_type Macaddr Type java.lang.Object macaddr 2147483647
31 bit2_type Bit2 Type java.lang.Boolean bit 2
32 bitvarying2_type Bitvarying2 Type java.lang.Object varbit 5

-----------------------
PostgreSQL 8.3-603 Arrays (Class Now All Array)

1 data_type_id Data Type Id java.lang.Integer int4 11
2 smallint_array Smallint Array java.sql.Array _int2 2147483647
3 int_array Int Array java.sql.Array _int4 2147483647
4 bigint_array Bigint Array java.sql.Array _int8 2147483647
5 decimal_array Decimal Array java.sql.Array _numeric 2147483647
6 numeric_array Numeric Array java.sql.Array _numeric 2147483647
7 real_array Real Array java.sql.Array _float4 2147483647
8 doubleprecision_array Doubleprecision Array java.sql.Array _float8 2147483647
9 varchar_array Varchar Array java.sql.Array _varchar 2147483647
10 char_array Char Array java.sql.Array _bpchar 2147483647
11 text_array Text Array java.sql.Array _text 2147483647
12 date_array Date Array java.sql.Array _date 2147483647
13 time_array Time Array java.sql.Array _time 2147483647
14 timetz_array Timetz Array java.sql.Array _timetz 2147483647
15 timestamp_array Timestamp Array java.sql.Array _timestamp 2147483647
16 timestamptz_array Timestamptz Array java.sql.Array _timestamptz 2147483647
17 interval_array Interval Array java.sql.Array _interval 2147483647
18 boolean_array Boolean Array java.sql.Array _bool 2147483647
19 point_array Point Array java.sql.Array _point 2147483647
20 linesegment_array Linesegment Array java.sql.Array _lseg 2147483647
21 box_array Box Array java.sql.Array _box 2147483647
22 path_array Path Array java.sql.Array _path 2147483647
23 polygon_array Polygon Array java.sql.Array _polygon 2147483647
24 circle_array Circle Array java.sql.Array _circle 2147483647
25 cidr_array Cidr Array java.sql.Array _cidr 2147483647
26 inet_array Inet Array java.sql.Array _inet 2147483647
27 macaddr_array Macaddr Array java.sql.Array _macaddr 2147483647
28 bit2_array Bit2 Array java.sql.Array _bit 2147483647
29 bitvarying5_array Bitvarying5 Array java.sql.Array _varbit 2147483647

-----------------------
PostgreSQL 8.2-506 Arrays (Class Object & Array)

1 data_type_id Data Type Id java.lang.Integer int4 11
2 smallint_array Smallint Array java.sql.Array _int2 2147483647
3 int_array Int Array java.sql.Array _int4 2147483647
4 bigint_array Bigint Array java.sql.Array _int8 2147483647
5 decimal_array Decimal Array java.sql.Array _numeric 2147483647
6 numeric_array Numeric Array java.sql.Array _numeric 2147483647
7 real_array Real Array java.sql.Array _float4 2147483647
8 doubleprecision_array Doubleprecision Array java.sql.Array _float8 2147483647
9 varchar_array Varchar Array java.sql.Array _varchar 2147483647
10 char_array Char Array java.sql.Array _bpchar 2147483647
11 text_array Text Array java.sql.Array _text 2147483647
12 date_array Date Array java.sql.Array _date 2147483647
13 time_array Time Array java.sql.Array _time 2147483647
14 timetz_array Timetz Array java.sql.Array _timetz 2147483647
15 timestamp_array Timestamp Array java.sql.Array _timestamp 2147483647
16 timestamptz_array Timestamptz Array java.sql.Array _timestamptz 2147483647
17 interval_array Interval Array java.lang.Object _interval 2147483647
18 point_array Point Array java.lang.Object _point 2147483647
19 linesegment_array Linesegment Array java.lang.Object _lseg 2147483647
20 box_array Box Array java.lang.Object _box 2147483647
21 path_array Path Array java.lang.Object _path 2147483647
22 polygon_array Polygon Array java.lang.Object _polygon 2147483647
23 circle_array Circle Array java.lang.Object _circle 2147483647
24 cidr_array Cidr Array java.lang.Object _cidr 2147483647
25 inet_array Inet Array java.lang.Object _inet 2147483647
26 macaddr_array Macaddr Array java.lang.Object _macaddr 2147483647
27 bit2_array Bit2 Array java.sql.Array _bit 2147483647
28 bitvarying5_array Bitvarying5 Array java.lang.Object _varbit 2147483647

Re: TEXT columns should indentify as java.sql.Types.CLOB

From

Toni Helenius

Date:

17 August 2010, 07:08:32

Hi,

Thanks for the reply. Your code essentially does the same, except it relies that the table has some data in it to
returna line. My code asks for columns in a certain table. It works as well. 

You get:
text_type Text Type java.lang.String text 2147483647

You're not actually mapping the classes in a way we need to do in this case. This tells me that I should read te TEXT
fieldas java.lang.String to Java, that is correct. But I need to create a CREATE SQL dynamically from these accross
differentdatabases, mapping the fields (also utilizing our fixed field mappings). Reading the field values isn't
necessaryhere. This is why I need the details on what kind of field in common SQL sense we are talking about. This
providespretty good database independency! 

cols.getShort("DATA_TYPE");
returns the java.sql.Types enumeration. TEXT field returns VARCHAR. Which in my oppinion should be CLOB.

ResultSet cols = metaFrom.getColumns(null, userFrom, code, null);
userFrom = schema string; code = table name;


-----Original Message-----
From: dmp [mailto:danap@ttc-cmc.net]
Sent: 16. elokuuta 2010 18:36
To: Toni Helenius; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] TEXT columns should indentify as java.sql.Types.CLOB

>
>
>Hello,
>
>I'm using Postgres 8.3.11 database and the latest JDBC driver 8.4 Build 701 (JDBC 4) + Java 6. In our databases there
areTEXT type columns. However if I make a query to identify these fields in Java, the field DATA TYPE is VARCHAR and
thelength is 2147483647. Type name is correct; "TEXT". But as we need database independent code, we are using DATA TYPE
asI presume is correct. And I think these TEXT fields should return java.sql.Types.CLOB as DATA TYPE instead of
VARCHAR.
>
>Here is some code:
>
>Connection fromConn;
>DatabaseMetaData metaFrom;
>String userFrom;
>
>fromConn = from.getConnection();
>metaFrom = fromConn.getMetaData();
>userFrom = ((PooledConnection)from).getTableOwner();
>
>ResultSet cols = metaFrom.getColumns(null, userFrom, code, null); while
>(cols.next()) {
>    cols.getShort("DATA_TYPE");
>    cols.getString("TYPE_NAME");
>    }
>
I'm just not seeing it. Your code example seems to be collecting the information from the database connection not a
particulartable. If your application needs to identify column types regardless of different databases then the way I do
itis through evaluation of the table columns. Attached file containing the output for the last three or so PostgreSQL
JDBCdrivers. As far as TEXT and CLOB types I would prefer then to be identifed independently. 

danap

String sqlStatementString = "SELECT * FROM " + schemaTableName + " LIMIT 1"; ResultSet db_resultSet =
sqlStatement.executeQuery(sqlStatementString);
DatabaseMetaData dbMetaData = dbConnection.getMetaData(); ResultSetMetaData tableMetaData = db_resultSet.getMetaData();

for (int i = 1; i < tableMetaData.getColumnCount() + 1; i++) { // Collect Information on Column.

colNameString = tableMetaData.getColumnName(i); comboBoxNameString = parseColumnNameField(colNameString);
columnClass = tableMetaData.getColumnClassName(i);
columnType = tableMetaData.getColumnTypeName(i);
columnSize = Integer.valueOf(tableMetaData.getColumnDisplaySize(i));

System.out.println(i + " " + colNameString + " " + comboBoxNameString + " " + columnClass + " " + columnType + " " +
columnSize);}

Re: TEXT columns should indentify as java.sql.Types.CLOB

From

Toni Helenius

Date:

18 August 2010, 07:04:33

Hi,

An definitely a bug: BOOLEAN columns get identified as java.sql.Types.VARCHAR. Not java.sql.Types.BOOLEAN as they
should.I end up getting SQL create statements where boolean fields are translated back as VARCHAR(1). 

-----Original Message-----
From: dmp [mailto:danap@ttc-cmc.net]
Sent: 16. elokuuta 2010 18:36
To: Toni Helenius; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] TEXT columns should indentify as java.sql.Types.CLOB

>
>
>Hello,
>
>I'm using Postgres 8.3.11 database and the latest JDBC driver 8.4 Build 701 (JDBC 4) + Java 6. In our databases there
areTEXT type columns. However if I make a query to identify these fields in Java, the field DATA TYPE is VARCHAR and
thelength is 2147483647. Type name is correct; "TEXT". But as we need database independent code, we are using DATA TYPE
asI presume is correct. And I think these TEXT fields should return java.sql.Types.CLOB as DATA TYPE instead of
VARCHAR.
>
>Here is some code:
>
>Connection fromConn;
>DatabaseMetaData metaFrom;
>String userFrom;
>
>fromConn = from.getConnection();
>metaFrom = fromConn.getMetaData();
>userFrom = ((PooledConnection)from).getTableOwner();
>
>ResultSet cols = metaFrom.getColumns(null, userFrom, code, null); while
>(cols.next()) {
>    cols.getShort("DATA_TYPE");
>    cols.getString("TYPE_NAME");
>    }
>
I'm just not seeing it. Your code example seems to be collecting the information from the database connection not a
particulartable. If your application needs to identify column types regardless of different databases then the way I do
itis through evaluation of the table columns. Attached file containing the output for the last three or so PostgreSQL
JDBCdrivers. As far as TEXT and CLOB types I would prefer then to be identifed independently. 

danap

String sqlStatementString = "SELECT * FROM " + schemaTableName + " LIMIT 1"; ResultSet db_resultSet =
sqlStatement.executeQuery(sqlStatementString);
DatabaseMetaData dbMetaData = dbConnection.getMetaData(); ResultSetMetaData tableMetaData = db_resultSet.getMetaData();

for (int i = 1; i < tableMetaData.getColumnCount() + 1; i++) { // Collect Information on Column.

colNameString = tableMetaData.getColumnName(i); comboBoxNameString = parseColumnNameField(colNameString);
columnClass = tableMetaData.getColumnClassName(i);
columnType = tableMetaData.getColumnTypeName(i);
columnSize = Integer.valueOf(tableMetaData.getColumnDisplaySize(i));

System.out.println(i + " " + colNameString + " " + comboBoxNameString + " " + columnClass + " " + columnType + " " +
columnSize);}

Re: TEXT columns should indentify as java.sql.Types.CLOB

From

Oliver Jowett

Date:

18 August 2010, 07:31:51

Toni Helenius wrote:
> Hi,
>
> An definitely a bug: BOOLEAN columns get identified as java.sql.Types.VARCHAR. Not java.sql.Types.BOOLEAN as they
should.I end up getting SQL create statements where boolean fields are translated back as VARCHAR(1). 

That does sound like a bug. Do you have a self-contained testcase
showing the problem?

-O

Re: TEXT columns should indentify as java.sql.Types.CLOB

From

Toni Helenius

Date:

18 August 2010, 08:15:42

Hi,

Here is the best I can do, I'm sure you'll manage to fill in the holes:

private static final String DRIVER = "org.postgresql.Driver";
private static final String URL = "jdbc:postgresql://localhost/testdb";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

Class.forName("DRIVER");
Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData  metaFrom = connection.getMetaData();
// Get the columns
ResultSet cols = metaFrom.getColumns(null, YOUR_SCHEMA_HERE, YOUR_TABLE_NAME_HERE, null);

// Get column info
while (cols.next()) {
    println("Column name: " + cols.getString("COLUMN_NAME"));
    short type = cols.getShort("DATA_TYPE"));

    if (java.sql.Types.BOOLEAN == type) {
        println("A boolean");
    }
    else {
        println("Not a boolean! (" + type + ")");
    }
    println("Postgre calls this: " + cols.getString("TYPE_NAME"));
    }


-----Original Message-----
From: Oliver Jowett [mailto:oliver@opencloud.com]
Sent: 18. elokuuta 2010 10:32
To: Toni Helenius
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] TEXT columns should indentify as java.sql.Types.CLOB

Toni Helenius wrote:
> Hi,
>
> An definitely a bug: BOOLEAN columns get identified as java.sql.Types.VARCHAR. Not java.sql.Types.BOOLEAN as they
should.I end up getting SQL create statements where boolean fields are translated back as VARCHAR(1). 

That does sound like a bug. Do you have a self-contained testcase
showing the problem?

-O

Re: TEXT columns should indentify as java.sql.Types.CLOB

From

Oliver Jowett

Date:

18 August 2010, 08:19:30

Toni Helenius wrote:
> Hi,
>
> Here is the best I can do, I'm sure you'll manage to fill in the holes:

Well, I was more interested in how you were ending up with VARCHAR.
It's normal for booleans to be mapped to Types.BIT (see previous list
discussion)

>     if (java.sql.Types.BOOLEAN == type) {
>         println("A boolean");
>     }

-O

Re: TEXT columns should indentify as java.sql.Types.CLOB

From

Toni Helenius

Date:

18 August 2010, 09:02:28

Hi,

Yes, good sir, you're right. It got mapped as a BIT. My hastiness and original if-else was a bit off on this. Sorry.

Still how do you feel about TEXT columns mapping as VARCHAR?

-----Original Message-----
From: Oliver Jowett [mailto:oliver@opencloud.com]
Sent: 18. elokuuta 2010 11:19
To: Toni Helenius
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] TEXT columns should indentify as java.sql.Types.CLOB

Toni Helenius wrote:
> Hi,
>
> Here is the best I can do, I'm sure you'll manage to fill in the holes:

Well, I was more interested in how you were ending up with VARCHAR.
It's normal for booleans to be mapped to Types.BIT (see previous list
discussion)

>     if (java.sql.Types.BOOLEAN == type) {
>         println("A boolean");
>     }

-O