getGeneratedKeys( ) Problem in JSF

[Mongoose]

Hi,

I am trying to retrieve the primary key from a newly inserted row in
an Oracle Database from a JSF application. However, when I execute
this line:

Integer x = rs1.getInt("PurchaseID");

in the code below I get an SQLException "Unsupported Feature". I'm
using Oracle Database 10g and ojdbc14.jar.
Could be a jdbc driver problem but I'm not sure. Could someone help
me out here??

Thanks so much,

Andy



Statement stmt2 = conn.createStatement();

SQLStatement1 = new StringBuffer("INSERT INTO Purchase (PurchaseID,
CarModelID) VALUES (");
SQLStatement1.append("PurchaseID_Seq.nextval, ");
SQLStatement1.append(ModID + ")");

stmt2.executeUpdate(SQLStatement1.toString(),
Statement.RETURN_GENERATED_KEYS);

ResultSet rs1 = stmt2.getGeneratedKeys();

if (rs1.next())
{
Integer x = rs1.getInt("PurchaseID");
}

 

[Roedy Green]

SQLStatement1 = new StringBuffer("INSERT INTO Purchase (PurchaseID,

This is not your problem, but normally one uses StringBuilder rather
than the old, slower StringBuffer.

 

[Roedy Green]

in the code below I get an SQLException "Unsupported Feature".

Whenever asking for help, tell people which line generated the
exception.

I am guessing it was
stmt2.executeUpdate(SQLStatement1.toString(),
Statement.RETURN_GENERATED_KEYS);

JDBC is a wimpy "standard" where support for almost every feature is
optional. All it does it provide standard access to a feature if it is
available. You still have to port your code between SQL engines.
What is supported depends heavily on the version of the both the SQL
and JDBC drivers.

To debug, try dumping sql.Statement1.toString() (note the lower case
variable name) to make sure you are sending the command you think you
are. and get rid of the quirky RETURN_GENERATED_KEYS.

If that works, chances are the problem is simply Oracle does not
properly support RETURN_GENERATED_KEYS. IIRC there was something in
Oracle I was quite surprised it did not support, perhaps reverse
traversal of ResultSets, so don't assume, just because they are big
they support everything.

These URLs might shed some light:

http://forums.oracle.com/forums/thread.jspa?messageID=3407263

http://java-x.blogspot.com/2006/09/oracle-jdbc-automatic-key-generation.html

http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/jdbcvers.htm

 

[Lew]

I am trying to retrieve the primary key from a newly inserted row in
an Oracle Database from a JSF application.  However, when I execute
this line:

Integer x = rs1.getInt("PurchaseID");

in the code below I get an SQLException "Unsupported Feature".  I'm
using Oracle Database 10g and ojdbc14.jar.
Could be a jdbc driver problem but I'm not sure.  Could someone help
me out here??
...
Statement stmt2 = conn.createStatement();

SQLStatement1 = new StringBuffer("INSERT INTO Purchase (PurchaseID,
CarModelID) VALUES (");
SQLStatement1.append("PurchaseID_Seq.nextval, ");
SQLStatement1.append(ModID + ")");

"SQL injection attack" - learn from 7-11 and others.
<http://xkcd.com/327/>

Use 'PreparedStatement' instead of dynamic SQL.

stmt2.executeUpdate(SQLStatement1.toString(),
Statement.RETURN_GENERATED_KEYS);

ResultSet rs1 = stmt2.getGeneratedKeys();

if (rs1.next())
{
      Integer x = rs1.getInt("PurchaseID");

}

Does "UnsupportedFeature" apply to 'RETURN_GENERATED_KEYS'? Perhaps
the Oracle Java 5 or Java 6 JDBC drivers have that feature. Try them
instead of the Java 1.4 version.

Normally you don't need to explicitly insert a 'nextval' into a
generated ID. Perhaps you should use the "INSERT ... RETURNING
PurchaseID" form of insert.

 

[Arne Vajhøj]

Whenever asking for help, tell people which line generated the
exception.

I am guessing it was
stmt2.executeUpdate(SQLStatement1.toString(),
Statement.RETURN_GENERATED_KEYS);

Given that the original post just above what you quoted contains:

# However, when I execute
# this line:
#
# Integer x = rs1.getInt("PurchaseID");

Then there is no reason to guess.

Arne

 

[Arne Vajhøj]

This is not your problem, but normally one uses StringBuilder rather
than the old, slower StringBuffer.

If the fact that he is using the Oracle JDBC driver for Java 1.4
indicates that he is using Java 1.4, then there are very good reasons
for not using StringBuilder.

Arne