Oracle JDBC Driver - getColumns retrieve incorrect number datatype

From: Vivian Fonger <vfonger_at_socketware.com>
Date: 8 Oct 2002 08:30:39 -0700
Message-ID: <6c4b2c3e.0210080730.3efc14fd@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 - 10:30:39 CDT