Обсуждение: JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.
Hi, I think I found two bugs (and I hope I am not wasting everyone's time). One is minor: the base type of a boolean[] is java.sql.Types.BIT instead or java.sql.Types.BOOLEAN. At the very least shouldn't these be aliases for the same type? And secondly the returned type from a smallint[] is an Integer[] instead of a Short[]. So running this code: ------------------------------------------------------------------ public class NewClass { public static void main(String[] args) { try { Class.forName("org.postgresql.Driver"); java.sql.Connection conn = java.sql.DriverManager.getConnection( "jdbc:postgresql://localhost:5432/dev", "dev", "devmm" ); java.util.Enumeration<java.sql.Driver> drivers = java.sql.DriverManager.getDrivers(); while( drivers.hasMoreElements() ) { java.sql.Driver d = drivers.nextElement(); System.out.println( d.toString() + ": " + d.getMajorVersion() + "." + d.getMinorVersion() ); } java.sql.PreparedStatement ps_ver = conn.prepareStatement( "SELECT version()" ); java.sql.ResultSet rs = ps_ver.executeQuery(); while(rs.next()) System.out.println(rs.getString(1)); java.sql.PreparedStatement ps_create = conn.prepareStatement( "CREATE TABLE public.aab (" + "ia integer[], " + "sa smallint[], " + "ba boolean[] )" ); ps_create.executeUpdate(); java.sql.PreparedStatement ps_insert = conn.prepareStatement( "INSERT INTO public.aab (ia,sa,ba) VALUES (?,?,?)" ); ps_insert.setArray( 1, conn.createArrayOf( "int4", new Integer[] { 1, 2 } ) ); ps_insert.setArray( 2, conn.createArrayOf( "int2", new Short[] { 100, 200 } ) ); ps_insert.setArray( 3, conn.createArrayOf( "bool", new Boolean[] {false,true} ) ); ps_insert.executeUpdate(); java.sql.PreparedStatement ps_select = conn.prepareStatement( "SELECT ia,sa,ba FROM public.aab" ); rs = ps_select.executeQuery(); java.sql.Array jdbcArr; while(rs.next()) { System.out.println("Integer[]"); jdbcArr = rs.getArray("ia"); Integer[] javaIntArr = (Integer[]) jdbcArr.getArray(); System.out.println(javaIntArr[0] + " " + javaIntArr[1]); System.out.println(String.format( "%s: %d %d", jdbcArr.getBaseTypeName(), java.sql.Types.INTEGER, jdbcArr.getBaseType())); System.out.println("END Integer[]"); System.out.println("Boolean[]"); jdbcArr = rs.getArray("ba"); Boolean[] javaBooArr = (Boolean[]) jdbcArr.getArray(); System.out.println(javaBooArr[0] + " " + javaBooArr[1]); System.out.println(String.format( "%s: %d %d %d", jdbcArr.getBaseTypeName(), java.sql.Types.BOOLEAN, jdbcArr.getBaseType(), java.sql.Types.BIT)); System.out.println("END Boolean[]"); System.out.println("Short[]"); jdbcArr = rs.getArray("sa"); Short[] javaShoArr = (Short[]) jdbcArr.getArray(); System.out.println(javaShoArr[0] + " " + javaShoArr[1]); System.out.println(String.format( "%s: %d %d", jdbcArr.getBaseTypeName(), java.sql.Types.SMALLINT, jdbcArr.getBaseType())); System.out.println("END Short[]"); } catch( Exception e ) { ; } } // main } // class ------------------------------------------------------------------ Prints the following for me: ------------------------------------------------------------------ sun.jdbc.odbc.JdbcOdbcDriver@de6f34: 2.1 org.postgresql.Driver@47b480: 8.4 PostgreSQL 8.4.2 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42), 64-bit Integer[] 1 2 int4: 4 4 END Integer[] Boolean[] false true bool: 16 -7 -7 END Boolean[] Short[] java.lang.ClassCastException: [Ljava.lang.Integer; cannot be cast to [Ljava.lang.Short; at NewClass.main(NewClass.java:90) [Ljava.lang.Integer; cannot be cast to [Ljava.lang.Short; ------------------------------------------------------------------ Thanks for any help, -- Saneesh Apte 510-642-5478 http://www.calccit.org California Center for Innovative Transportation University of California, Berkeley, MC3830 2105 Bancroft Way, Suite 300 Berkeley, CA 94720-3830 Phone: 510-642-4522 Fax: 510-642-0910 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Saneesh Apte <san@calccit.org> wrote: > the base type of a boolean[] is java.sql.Types.BIT instead or > java.sql.Types.BOOLEAN. At the very least shouldn't these be > aliases for the same type? > > And secondly the returned type from a smallint[] is an Integer[] > instead of a Short[]. Should the objects in the array returned by getArray be typed according to the rules of an individual value returned by getObject? (I couldn't find anything explicit on that, but it seems reasonable.) If that *is* true, the controlling part of the spec is: http://java.sun.com/javase/6/docs/technotes/guides/jdbc/getstart/mapping.html#table3 That maps SQL types TINYINT, SMALLINT, AND INTEGER to Java Integer. It also maps SQL BIT to Java Boolean. The SQL type of BOOLEAN was a latecomer to the SQL spec, and it appears that JDBC hasn't yet added it to the mappings. Do you have a reference to something which indicates that getArray should use a different mapping? Maybe someone will see it differently, but I don't think I see a bug here. Compliance with the spec is not a bug, even if the spec is a bit odd.... ;-) -Kevin
On Mon, 28 Jun 2010, Saneesh Apte wrote: > One is minor: the base type of a boolean[] is java.sql.Types.BIT > instead or java.sql.Types.BOOLEAN. At the very least shouldn't these be > aliases for the same type? These are aliases for the same type. I believe we accept either BOOLEAN or BIT as equivalent in all cases. We default to BIT for historical reasons because it was defined first in the JDBC2 spec while BOOLEAN came around in the JDBC3 version. > And secondly the returned type from a smallint[] is an Integer[] > instead of a Short[]. > The JDBC spec says that the result of getObject on a Types.SMALLINT value should return Integer, so we have followed that for array types as well. The spec contains this historical note: The JDBC 1.0 specification defined the Java object mapping for the SMALLINT and TINYINT JDBC types to be Integer. The Java language did not include the Byte and Short data types when the JDBC 1.0 specification was finalized. The mapping of SMALLINT and TINYINT to Integer is maintained to preserve backwards compatibility For more information see table B-3 in the JDBC4.0 spec. Kris Jurka
> Hi, > > I think I found two bugs (and I hope I am not wasting everyone's > time). > One is minor: the base type of a boolean[] is java.sql.Types.BIT > instead or java.sql.Types.BOOLEAN. At the very least shouldn't these > be aliases for the same type? > > And secondly the returned type from a smallint[] is an Integer[] > instead of a Short[]. > > > > So running this code: The running of the supplied code also gives the same results with PostgreSQL 8.3.3 results: ======================================== sun.jdbc.odbc.JdbcOdbcDriver@fc9944: 2.1 org.postgresql.Driver@8b819f: 8.4 PostgreSQL 8.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 Integer[] 1 2 int4: 4 4 END Integer[] Boolean[] false true bool: 16 -7 -7 END Boolean[] Short[] java.lang.ClassCastException: [Ljava.lang.Integer; cannot be cast to [Ljava.lang.Short; ======================================== The 8.4 JDBC Driver though does gives a consistent result as past versions via the tableMetaData.getColumnClassName() & tableMetaData.getColumnTypeName() for the base types, integer, smallint, and boolean. System.out.println(i + " " + colNameString + " " + columnClass + " " + columnType + " " + columnSize); 3 int_type java.lang.Integer int4 11 2 smallint_type java.lang.Integer int2 6 21 boolean_type java.lang.Boolean bool 1 1 ia java.sql.Array _int4 11 2 sa java.sql.Array _int2 6 3 ba java.sql.Array _bool 1 Attached slight modification to NewClass to correctly compile and drop table if run more than once. danap. public class NewClass2 { public static void main(String[] args) { try { Class.forName("org.postgresql.Driver"); java.sql.Connection conn = java.sql.DriverManager.getConnection( "jdbc:postgresql://192.168.157.32:5432/dev", "dev", "devmm"); java.util.Enumeration<java.sql.Driver> drivers = java.sql.DriverManager.getDrivers(); while (drivers.hasMoreElements()) { java.sql.Driver d = drivers.nextElement(); System.out.println(d.toString() + ": " + d.getMajorVersion() + "." + d.getMinorVersion()); } java.sql.PreparedStatement ps_ver = conn.prepareStatement("SELECT version()"); java.sql.ResultSet rs = ps_ver.executeQuery(); while (rs.next()) System.out.println(rs.getString(1)); java.sql.Statement dbStatement = conn.createStatement(); dbStatement.executeUpdate("DROP TABLE IF EXISTS public.aab"); java.sql.PreparedStatement ps_create = conn.prepareStatement("CREATE TABLE public.aab (" + "ia integer[], " + "sa smallint[], " + "ba boolean[] )"); ps_create.executeUpdate(); java.sql.PreparedStatement ps_insert = conn .prepareStatement("INSERT INTO public.aab (ia,sa,ba) VALUES (?,?,?)"); ps_insert.setArray(1, conn.createArrayOf("int4", new Integer[] {1, 2})); ps_insert.setArray(2, conn.createArrayOf("int2", new Short[] {100, 200})); ps_insert.setArray(3, conn.createArrayOf("bool", new Boolean[] {false, true})); ps_insert.executeUpdate(); java.sql.PreparedStatement ps_select = conn.prepareStatement("SELECT ia,sa,ba FROM public.aab"); rs = ps_select.executeQuery(); java.sql.Array jdbcArr; while (rs.next()) { System.out.println("Integer[]"); jdbcArr = rs.getArray("ia"); Integer[] javaIntArr = (Integer[]) jdbcArr.getArray(); System.out.println(javaIntArr[0] + " " + javaIntArr[1]); System.out.println(String.format("%s: %d %d", jdbcArr.getBaseTypeName(), java.sql.Types.INTEGER, jdbcArr.getBaseType())); System.out.println("END Integer[]"); System.out.println("Boolean[]"); jdbcArr = rs.getArray("ba"); Boolean[] javaBooArr = (Boolean[]) jdbcArr.getArray(); System.out.println(javaBooArr[0] + " " + javaBooArr[1]); System.out.println(String.format("%s: %d %d %d", jdbcArr.getBaseTypeName(), java.sql.Types.BOOLEAN, jdbcArr.getBaseType(), java.sql.Types.BIT)); System.out.println("END Boolean[]"); System.out.println("Short[]"); jdbcArr = rs.getArray("sa"); Short[] javaShoArr = (Short[]) jdbcArr.getArray(); System.out.println(javaShoArr[0] + " " + javaShoArr[1]); System.out.println(String.format("%s: %d %d", jdbcArr.getBaseTypeName(), java.sql.Types.SMALLINT, jdbcArr.getBaseType())); System.out.println("END Short[]"); } rs.close(); dbStatement.close(); conn.close(); } catch (Exception e) { System.out.println(e.toString()); } } // main } // class
Saneesh Apte wrote: > I think I found two bugs (and I hope I am not wasting everyone's time). > One is minor: the base type of a boolean[] is java.sql.Types.BIT > instead or java.sql.Types.BOOLEAN. At the very least shouldn't these be > aliases for the same type? Types.BOOLEAN does not exist before JDBC3. Client code can use either, but we have to pick one or the other when returning a value, so we return BIT. > And secondly the returned type from a smallint[] is an Integer[] > instead of a Short[]. smallint is a Types.SMALLINT which is mapped to java.lang.Integer by the JDBC spec. See appendix B of the spec: > Note – The JDBC 1.0 specification defined the Java object mapping for the > SMALLINT and TINYINT JDBC types to be Integer. The Java language did not > include the Byte and Short data types when the JDBC 1.0 specification was > finalized. The mapping of SMALLINT and TINYINT to Integer is maintained to > preserve backwards compatibility. -O