Consultor Eletrônico

Status: Verified

FACT(s) (Environment):

Progress 9.x

SYMPTOM(s):

SELECT statement with FOR UPDATE has different locking behavior.

SQL92

ODBC Client connection

JDBC Client connection

CAUSE:

ODBC and JDBC SQL-92 clients using the SELECT statement with the FOR UPDATE clause will only apply SHARE locks to records in the result set generated by the SELECT statement. This occurs because the ODBC and JDBC drivers strip the FOR UPDATE clause off of the statement.

FIX:

There is a workaround that will provide ODBC and JDBC clients with the expected locking behavior. Use a Stored Procedure that returns a result set to execute the SELECT... FOR UPDATE statement.

The following code provides an EXAMPLE of a Stored Procedure that executes a SELECT... FOR UPDATE and returns a result set:

** Tested against the Progress demo database **

// Stored Procedure: get_empNameCity()
// Purpose: Execute query for retrieving customer.name, customer.city
// using SELECT…FOR UPDATE to apply exclusive locks to rows
// in result set
CREATE PROCEDURE get_empNameCity()
RESULT (rs_name CHAR(20),rs_city CHAR(20))
BEGIN
// define variables for processing statement result set
String empname = new String();
String empcity = new String();

// Declare and Open cursor
SQLCursor emp_cursor = new SQLCursor("SELECT name, city " + "FROM pub.customer FOR UPDATE OF name");
emp_cursor.open();

// fetch records and transfer data from statement result set
// to procedure result set
do
{
emp_cursor.fetch();

if (emp_cursor.found())
{
empname = (String)emp_cursor.getValue(1, CHAR);
empcity = (String)emp_cursor.getValue(2, CHAR);

// set result set row
SQLResultSet.set(1, empname);
SQLResultSet.set(2, empcity);
SQLResultSet.insert();
}
} while (emp_cursor.found());

emp_cursor.close();
END
COMMIT WORK;


The following Java program demonstrates creating the result set using a stored procedure and then subsequently updating records:

import java.sql.*;

public class ChangeCustCity
{
public static void main (String args [])
throws SQLException, ClassNotFoundException
{

String driver = null;
String url = null;
String userid = null;
String passwd = null;

//Following using the Progress Demo Database
driver = "com.progress.sql.jdbc.JdbcProgressDriver";
url = "jdbc:jdbcprogress:T:localhost:mysvc:mydemo";
userid = "myuserid";
passwd = "mypasswd";

// Load the JDBC driver
Class.forName(driver);

// Register the driver
DriverManager.registerDriver(DriverManager.getDriver(url));

// Create the connection
Connection con;

con = DriverManager.getConnection(url, userid, passwd);

// Set transaction traits
con.setAutoCommit(false);
con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

// Create call to Stored Procedure, Result Set, and Prepared Update Statement CallableStatement getEmpNameCity = con.prepareCall("{call get_empNameCity()}");

ResultSet customerSet = getEmpNameCity.executeQuery();

PreparedStatement updateCity = con.prepareStatement("UPDATE pub.customer " + "SET city = ? WHERE name = ?");

String customerName = "Hearts Darts";
String newCustomerCity = "Franconia";

// loop through result set looking for row(s) to update
while (customerSet.next())
{
// check the first column (name) of result set for customer
if (customerSet.getString(1).trim().equals(customerName))
{
//Found row for update
updateCity.setString(1, newCustomerCity);
updateCity.setString(2, customerName);
updateCity.executeUpdate();
}
}

// Commit changes to database
con.commit();
} // end of main
} // end of class