Skip site navigation (1) Skip section navigation (2)

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

From: Toni Helenius <Toni(dot)Helenius(at)syncrontech(dot)com>
To: dmp <danap(at)ttc-cmc(dot)net>, "pgsql-jdbc(at)postgresql(dot)org"<pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: TEXT columns should indentify as java.sql.Types.CLOB
Date: 2010-08-17 07:08:12
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-jdbc

Thanks for the reply. Your code essentially does the same, except it relies that the table has some data in it to return a 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 field as java.lang.String to Java, that is correct. But I need to create a CREATE SQL dynamically from these accross different databases, mapping the fields (also utilizing our fixed field mappings). Reading the field values isn't necessary here. This is why I need the details on what kind of field in common SQL sense we are talking about. This provides pretty good database independency!

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(at)ttc-cmc(dot)net] 
Sent: 16. elokuuta 2010 18:36
To: Toni Helenius; pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] TEXT columns should indentify as java.sql.Types.CLOB

>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.
>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.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.


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

In response to

pgsql-jdbc by date

Next:From: Toni HeleniusDate: 2010-08-18 07:04:13
Subject: Re: TEXT columns should indentify as java.sql.Types.CLOB
Previous:From: dmpDate: 2010-08-16 15:36:13
Subject: Re: TEXT columns should indentify as java.sql.Types.CLOB

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group