FOR UPDATE is not permitted in this type of statement

[Thomas <Th...@t-online.de>]

Hi,

I am trying to execute a select for update statement and receive the error
message shown on the subject line although I think I had respected all
requirements for such a statement.

Here is the Java code of the stored procedure:
==============================================
public static void SP_GetNextID(int iNextVal[], String vcIDName)
   throws SQLException {
   Connection conn = getDefaultConnection();
   int column = 1;

   PreparedStatement ps = Utils.prepare(conn, 
   + "SELECT \"LastValue\" \n"
   + "FROM rte.\"IDs\" \n"
   + "WHERE \"IDName\" = ? \n"
   + "FOR UPDATE OF \"LastValue\" \n");

   ps.setString(1, vcIDName);

   ResultSet rs = ps.executeQuery();

   rs.next();

   iNextVal[0] = rs.getInt(column++) + 1;

   ps = Utils.prepare(conn, 
   + "UPDATE rte.\"IDs\" \n"
   + "SET \"LastValue\" = ? \n" 
   + "WHERE \"IDName\" = ? \n");

   ps.setInt(1, iNextVal[0]);
   ps.setString(2, vcIDName);

   ps.executeUpdate();

   Utils.close(ps);

   return;

This is the SQL code used to define the procedure:
==================================================
CREATE PROCEDURE 
rte."SP_GetNextID"(OUT "iNextID" integer, IN "vcIDName" varchar(64))
 LANGUAGE JAVA 
 PARAMETER STYLE JAVA 
 MODIFIES SQL DATA 
 EXTERNAL NAME 'allDatabasesPk.Functions.SP_GetNextID';


and here is the code snippet from the main program calling the procedure:
=========================================================================
Connection conn = DriverManager.getConnection(connectionURL);

String vcIDName = "Transaction";
CallableStatement cstmt = 
conn.prepareCall("{ call rte.\"SP_GetNextID\"(?, ?) }");
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.setString(2, vcIDName);
cstmt.executeUpdate();
String cTrxID = Integer.toString(cstmt.getInt(1));
cstmt.close();
System.out.println(cTrxID);

The error is thrown on line 'cstmt.executeUpdate();

Any help would be very much appreciated.

SOLVED: FOR UPDATE is not permitted in this type of statement

[Thomas <Th...@t-online.de>]

mistake was accessing rte."IDs" which is a view instead of "TBL_IDs" which is
the base table. Also rewrote procedure to use updatable resultset rather
separate select for update and update statement.