Re: Oracle JDBC Driver - getColumns retrieve incorrect number datatype

Check that the client and server's NLS_LANG match... Often this is overlooked.

Jim

"Vivian Fonger" <vfonger_at_socketware.com> wrote in message news:6c4b2c3e.0210080730.3efc14fd_at_posting.google.com...
> Database server: Oracle version 8.1.5
> JDBC Driver: Oracle JDBC thin driver version 8.1.6.
>
> I am having problem of retrieving the correct number datatype through
> Oracle JDBC driver. I am using the java DatabaseMetaData interface
> with getColumns method. The problem is the following:
>
> If we have a table with the following configuration:
>
> TABLE A (
> C1 NUMBER (18, 0),
> C2 NUMBER,
> C3 NUMBER (3,2)
> )
>
> Java pseduo code:
>
>
> import java.sql.*;
>
> public static String[][] getColumns(String[] params, String table)
> throws Exception {
>
> Connection conn = getConnection(params);
> DatabaseMetaData dbMeta = conn.getMetaData();
> ResultSet rs = dbMeta.getColumns
> (params[4], params[5], table, null);
>
> String[][] cols = new String[2][];
> ArrayList arrName = new ArrayList();
> ArrayList arrType = new ArrayList();
>
> while(rs.next()) {
> arrName.add(rs.getString(4));
> arrType.add(convertSQLType(rs.getShort(5)));
> }
>
> cols[0] = (String[]) arrName.toArray(new String[0]);
> cols[1] = (String[]) arrType.toArray(new String[0]);
> return cols;
> } // end getColumns
>
>
> private static String convertSQLType(short sqltype) {
> String ret = null;
>
> switch(sqltype) {
>
> case Types.ARRAY:
> ret = DataSourceConstants.ARRAY_TYPE;
> break;
> case Types.BIT:
> ret = DataSourceConstants.BOOLEAN_TYPE;
> break;
> case Types.TINYINT:
> case Types.SMALLINT:
> case Types.INTEGER:
> case Types.NUMERIC:
> ret = DataSourceConstants.INTEGER_TYPE;
> break;
> case Types.BIGINT:
> ret = DataSourceConstants.LONG_TYPE;
> break;
> case Types.FLOAT:
> case Types.DOUBLE:
> case Types.REAL:
> case Types.DECIMAL:
> ret = DataSourceConstants.DOUBLE_TYPE;
> break;
> case Types.DATE:
> case Types.TIME:
> case Types.TIMESTAMP:
> ret = DataSourceConstants.DATE_TYPE;
> break;
> case Types.CHAR:
> case Types.VARCHAR:
> case Types.LONGVARCHAR:
> ret = DataSourceConstants.STRING_TYPE;
> break;
> }
> return ret;
> } // end convertSQLType
>
> By using the getColumns method, I got all three of them as Double SQL
> type from java.sql.Types instead of C1 and C2 as either integer or
> long SQL type from java.sql.Types and C3 as double SQL type from
> java.sql.Types . I would like to know if anyone has heard of any bug
> regarding this issue or if I have done anything more with my code.
>
> Thanks
>
> Vivian Fonger
Received on Tue Oct 08 2002 - 18:40:09 CDT