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: E6A9CAA76548CB4EB02D2E3B174DD3B155381F0E4C@ink.sad.syncrontech.com (view raw or flat)
Thread:
Lists: 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 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!

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

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

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-2014 The PostgreSQL Global Development Group