Thread: JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.
JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.
From
Saneesh Apte
Date:
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
Re: JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.
From
"Kevin Grittner"
Date:
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
Re: [JDBC] JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.
From
Kris Jurka
Date:
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
Re: [JDBC] JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.
From
dmp
Date:
> 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
Re: [JDBC] JDBC: 2 bugs: Getting a smallint array actually gets an integer array and return type of a boolean array is bit.
From
Oliver Jowett
Date:
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