12775220 DatabaseMetaData.getColumns() returns unexpected values in COLUMN_SIZE and DECIMAL_DIGITS

Rod Allen

Hi All,

I posted a while ago asking about Bug 12775220

I just wanted to provide an update on this as I found a potential solution to the underlying problem that I facing (with NUMBER columns which lacked precision and scale). If you add the JVM argument -Doracle.jdbc.J2EE13Compliant=true it appears to resolve this problem.

Regards

Rod

Simon Cheng-Oracle

This fix is in 11.2.0.4

Rod Allen

Hi,

This is what I thought originally as it was 'reported' as fixed in Oracle Support but I tested it and the problem I was facing remains. This either means the the problem I am seeing is not exactly the same (and I am happy to be corrected if this is the case) or the core problem has not been fixed. To illustrate try compiling this and testing with JDBC 11.2.0.4 against an 11.2.0.4 database (ensuring the latest ojdbc6.jar is first in your CLASSPATH and adapt for your environment)

import java.sql.*;
import java.sql.SQLException;
import oracle.jdbc.pool.OracleDataSource;

public class OracleDriverTest {

    /**
    * @param args the command line arguments
    * @throws java.sql.SQLException
    */
    public static void main(String[] args) throws SQLException {
        Connection connection = getConnection();
            Statement stmt;
            stmt = connection.createStatement();
            DatabaseMetaData dmd = connection.getMetaData();

            ResultSet rs = dmd.getColumns("", "SCOTT", "BONUS", "%"))

                while (rs.next()) {
                    System.out.println("Column: " + rs.getString("COLUMN_NAME"));
                    System.out.println("Data type: " + rs.getInt("DATA_TYPE"));
                    System.out.println("Size: " + rs.getInt("COLUMN_SIZE"));
                    System.out.println("Decimal digits: " + rs.getInt("DECIMAL_DIGITS"));
                }
    }

    public static Connection getConnection() throws SQLException {
        String username = "SCOTT";
        String password = "password";
        String thinConn = "jdbc:oracle:thin:@//localhost/ORCL";
        OracleDataSource ods;
        ods = new OracleDataSource();
        ods.setUser(username);
        ods.setPassword(password);
        ods.setURL(thinConn);
        Connection conn = ods.getConnection();
        DatabaseMetaData dbmd = conn.getMetaData();
        System.out.println("Database : " + dbmd.getDatabaseProductVersion());
        System.out.println("JDBC Driver : " +dbmd.getDriverVersion());
        conn.setAutoCommit(false);
        return conn;
    }

}

When I run this against my database I see this

$ /opt/jdk1.6.0_38/bin/java OracleDriverTest

Database : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

JDBC Driver : 11.2.0.4.0

Column: ENAME

Data type: 12

Size: 10

Decimal digits: 0

Column: JOB

Data type: 12

Size: 9

Decimal digits: 0

Column: SAL

Data type: 3

Size: 0

Decimal digits: -127

Column: COMM

Data type: 3

Size: 0

Decimal digits: -127

now compare the results when I use the oracle.jdbc.J2EE13Compliant=true argument

$ /opt/jdk1.6.0_38/bin/java -Doracle.jdbc.J2EE13Compliant=true OracleDriverTest

Database : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

JDBC Driver : 11.2.0.4.0

Column: ENAME

Data type: 12

Size: 10

Decimal digits: 0

Column: JOB

Data type: 12

Size: 9

Decimal digits: 0

Column: SAL

Data type: 3

Size: 38

Decimal digits: 0

Column: COMM

Data type: 3

Size: 38

Decimal digits: 0

This illustrates both the problem I am facing and a potential workaround/solution. The question is - is this the same bug or something different?

Regards

Rod