RE: JDBC record locking -- help?

You can work the column number issue by using the UPDATE in a prepared statement. Just keep the placeholder numbers the same as the column numbers. Here's one from the Oracle Java tutorial. It assumes the connection is already set. http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

public void updateCoffeeSales(HashMap<String, Integer> salesForWeek)
throws SQLException {

PreparedStatement updateSales = null;
PreparedStatement updateTotal = null;

String updateString =
"update " + dbName + ".COFFEES " +
"set SALES = ? where COF_NAME = ?";

String updateStatement =
"update " + dbName + ".COFFEES " +
"set TOTAL = TOTAL + ? " +
"where COF_NAME = ?";

try {
con.setAutoCommit(false);
updateSales = con.prepareStatement(updateString);
updateTotal = con.prepareStatement(updateStatement);

for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) {
updateSales.setInt(1, e.getValue().intValue());
updateSales.setString(2, e.getKey());
updateSales.executeUpdate();
updateTotal.setInt(1, e.getValue().intValue());
updateTotal.setString(2, e.getKey());
updateTotal.executeUpdate();
con.commit();
}
} catch (SQLException e ) {
JDBCTutorialUtilities.printSQLException(e);
if (con != null) {
try {
System.err.print("Transaction is being rolled back");
con.rollback();
} catch(SQLException excep) {
JDBCTutorialUtilities.printSQLException(excep);
}
}
} finally {
if (updateSales != null) {
updateSales.close();
}
if (updateTotal != null) {
updateTotal.close();
}
con.setAutoCommit(true);
}
}

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of James H. H. Lampert
Sent: Monday, October 28, 2013 12:15 PM
To: Midrange Systems Technical Discussion
Subject: Re: JDBC record locking -- help?

On 10/28/13 9:51 AM, Dan Kimmel wrote:

How about reading the table with a CONCUR_READ_ONLY and then

updating

it with an UPDATE statement with a where clause rather than an UPDATE
CURRENT of your record set?

The difficulty with that is that I know of no way to do an UPDATE
statement that does all the columns by result set column number (the way
updateString() and so forth, followed by an updateRow(), does), and
building a statement to update all the columns by name (when, at compile
time, we don't even know how may columns we have, let alone what their
names are) is even less convenient than briefly opening an updateable
result set on the same query as an existing read-only result set.

And naturally, WHERE CURRENT OF <cursorName> (which I hadn't even
heard
of until this morning) seems to only work for UPDATE and DELETE
statements, not SELECT.

--
JHHL
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.