Re: case problem with moveToInsertRow()

Roger,

Try the latest build for 7.3 from jdbc.postgresql.org.  It seems this
issue is already fixed.

thanks,
--Barry


Roger Hand wrote:
> All my tablenames are lowercased in my Postgres db, but for legacy
> reasons the SQL code typically uses mixed case.
>
> Select statements work fine with the mixed case, but moveToInsertRow()
> fails with a 'No Primary Keys' exception unless I use the lowercase name
> in the query.
>
> For select, both these statements work fine for the table 'phone':
> SELECT * FROM Phone WHERE Phone = 244
> SELECT * FROM phone WHERE phone = 244
>
> However, the code below bombs if the mixed case table name is specified:
>
> String SQL = "SELECT * FROM Phone WHERE 1 = 0"; // 'No Primary Keys'
> exception
> //String SQL = "SELECT * FROM phone WHERE 1 = 0"; // works
> System.out.println(SQL);
> Statement sqlStatement = null;
> ResultSet RS = null;
> try {
>   sqlStatement = Conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
> ResultSet.CONCUR_UPDATABLE);
>   RS = sqlStatement.executeQuery(SQL);
>   boolean newRow = false;
>   if (RS != null) {
>     if (!RS.next()) {
>       newRow = true;
>       RS.moveToInsertRow(); // exception thrown here if mixed case
> tablename given
>     }
>     RS.updateInt("areacode", 444);
>
>     if (newRow)
>       RS.insertRow();
>     else
>       RS.updateRow();
>
>     RS.close();
>     Conn.commit();
>   } // if (RS != null)
> } catch (SQLException e) {System.out.println("SQLException " +
> e.getMessage());
> ==========
> table definition
> ==========
> -- Table: public.phone
> CREATE TABLE public.phone (
>   phone int8 DEFAULT nextval('"phone_phone_key"'::text) NOT NULL,
>   areacode varchar(10),
>   phonenumber varchar(30)
>   CONSTRAINT phone_pkey PRIMARY KEY (phone)
> ) WITH OIDS;
>
> I've traced it thru a little bit in the JDBC code, and it appears that
> getPrimaryKeys() expects the table name param to be the same case as the
> table.
>
> I am using Postgres 7.3.1, with JDBC drivers 2+ (build 108) with Java
> 1.3.1, and also the JDBC 3 driver with Java 1.4.1.
>
> -Roger