Re: TEXT columns should indentify as java.sql.Types.CLOB - Mailing list pgsql-jdbc

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);}