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
