Обсуждение: case problem with moveToInsertRow()
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()) {
//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);
RS.moveToInsertRow(); // exception thrown here if mixed case tablename given
}
RS.updateInt("areacode", 444);
if (newRow)
RS.insertRow();
else
RS.updateRow();
RS.insertRow();
else
RS.updateRow();
RS.close();
Conn.commit();
} // if (RS != null)
} catch (SQLException e) {System.out.println("SQLException " + e.getMessage());
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;
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
I was using the "Latest Stable Drivers" (build 108), but when I tried the "Development Drivers" (build 201) the problem didindeed go away! Thanks for the tip! Any idea when this fix will make it into drivers judged to be "Latest Stable"? -Roger -----Original Message----- From: Barry Lind [mailto:blind@xythos.com] Sent: Wednesday, February 12, 2003 6:07 PM To: Roger Hand Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] 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