Re: [JDBC] 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:



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