|
midrange.com
I know it's very difficult to discuss such issues using mail.
Nevertheless, it's the only place I can go for... I never give up untill
someone tells me it's impossible!
Right now I suspect the AS400/DB2 for reacting differently when
SQL-statements are executing:
My initial question was to lock a record. However this lock should exists
for update purposes only. I mean, other users may 'select' this record,
either as part of a 'subset' list (using select * ...), or as a single
'select' to view details. When this record is retrieved to update I can
use the sqlexception to send a message that this record is locked.
At the moment it's getting only worser...
web-app user1
1) create connection
2) setAutoCommit(false)
3) execute 'select * ...' (subset of records)
4) select record for update
5) execute 'update...'
no commit() executed, record on screen.
web-app user2 (This doesn't work)
1) create connection
2) setAutoCommit(false)
3) execute 'select * ...' (subset of records)
a selection which includes the record fails. Other selections work
normally.
vajava user3 (This works)
1) create connection
2) setAutoCommit(false)
3) execute 'select * ...' (subset of records)
4) select same record for update
This update failes correctly because of lock. It should work like this.
Why is the record locked when retrieved from the webapp and not when
retrieved from VAJAVA directly where same classes/methods are executed?
Only difference is in vajava using main() to call methods where in
web-app these methods are called from within a controlling servlet.
Here's the class involved:
package com.clipper.objects;
import com.clipper.externals.*;
import com.clipper.tools.*;
import com.ibm.as400.access.*;
import com.clipper.model.*;
import java.sql.*;
import java.util.*;
import java.math.*;
/**
* Insert the type's description here.
* Creation date: (07/03/00 14:02:58)
* @author: Administrator
*/
public class FreightQuotations
{
private static CommunicateClipperJDBC clipper = new
CommunicateClipperJDBC();
/**
* Ports constructor comment.
*/
public FreightQuotations() {
super();
}
/**
* This method was created in VisualAge.
* @return OL24.model.Part
* @param rs java.sql.ResultSet
*/
private static FreightQuotation convertRecord(ResultSet rs) throws
SQLException
{
FreightQuotation aFreightQuotation = new FreightQuotation();
aFreightQuotation.setKeyInCC(rs.getShort("BRDICC"));
aFreightQuotation.setKeyInYY(rs.getShort("BRDIJJ"));
aFreightQuotation.setKeyInMM(rs.getShort("BRDIMM"));
aFreightQuotation.setKeyInDD(rs.getShort("BRDIDD"));
aFreightQuotation.setKeySequence(rs.getShort("BRRENR"));
aFreightQuotation.setItemNr(rs.getShort("BRITEM"));
aFreightQuotation.setRecordNr(rs.getShort("BRRECN"));
aFreightQuotation.setNewAnnMod(rs.getString("BRNAMC"));
aFreightQuotation.setStatus(rs.getString("BRSTAT"));
aFreightQuotation.setFileLine(rs.getShort("BRLIJN"));
aFreightQuotation.setFileYear(rs.getShort("BRJAAR"));
aFreightQuotation.setFileSequence(rs.getShort("BRVOLG"));
aFreightQuotation.setPortOrigin(rs.getString("BRHOOR"));
aFreightQuotation.setPortLoading(rs.getString("BRHVER"));
aFreightQuotation.setPortDestination(rs.getString("BRHBES"));
aFreightQuotation.setPortDischarge(rs.getString("BRHLOS"));
aFreightQuotation.setBookingNr(rs.getString("BRBOKN"));
aFreightQuotation.setTerms(rs.getString("BRTERM"));
aFreightQuotation.setAllInYN(rs.getString("BRALIN"));
aFreightQuotation.setAgentCode(rs.getString("BRAGEN"));
aFreightQuotation.setShipperCode(rs.getString("BRVERL"));
aFreightQuotation.setForwarderCode(rs.getString("BREXPE"));
aFreightQuotation.setCargoType(rs.getString("BRBFLC"));
aFreightQuotation.setColli(rs.getInt("BRCOLL"));
aFreightQuotation.setPackageCode(rs.getString("BRVERP"));
aFreightQuotation.setGoodsDescription(rs.getString("BROMGO"));
aFreightQuotation.setWeight(rs.getInt("BRGEWI"));
aFreightQuotation.setCubage(rs.getInt("BRCUBE"));
aFreightQuotation.setCalculationBase(rs.getInt("BRBAS1"));
aFreightQuotation.setBtnCode(rs.getString("BRBTNK"));
aFreightQuotation.setImcoCode(rs.getString("BRIMCO"));
aFreightQuotation.setImcoPage(rs.getString("BRPAGE"));
aFreightQuotation.setImcoUnno(rs.getString("BRUNNO"));
aFreightQuotation.setImcoFlashPoint(rs.getString("BRFLAP"));
aFreightQuotation.setContainerSize(rs.getString("BRSIZE"));
aFreightQuotation.setContainerType(rs.getString("BRTYPE"));
aFreightQuotation.setContainerShippersOwnedYN(rs.getString("BRSOCC"));
aFreightQuotation.setContainerFullEmpty(rs.getString("BRFUEM"));
aFreightQuotation.setContainerTarra(rs.getInt("BRTARE"));
aFreightQuotation.setContainerServiceLoading(rs.getString("BRSER1"));
aFreightQuotation.setContainerServiceDischarge(rs.getString("BRSER2"));
aFreightQuotation.setBtnTariff(rs.getString("BRBTNT"));
aFreightQuotation.setBtnTariffSequence(rs.getShort("BRBTVT"));
aFreightQuotation.setQuotationTrade(rs.getString("BRTRAD"));
aFreightQuotation.setQuotationNr(rs.getString("BRQUOT"));
aFreightQuotation.setRejectionCode(rs.getString("BRREJC"));
aFreightQuotation.setRejectionDescription(rs.getInt("BRSEGM"));
aFreightQuotation.setMessageCode(rs.getString("BRMSCO"));
aFreightQuotation.setValidFromCC(rs.getShort("BRFRCC"));
aFreightQuotation.setValidFromYY(rs.getShort("BRFRYY"));
aFreightQuotation.setValidFromMM(rs.getShort("BRFRMM"));
aFreightQuotation.setValidToCC(rs.getShort("BRTOCC"));
aFreightQuotation.setValidToYY(rs.getShort("BRTOYY"));
aFreightQuotation.setValidToMM(rs.getShort("BRTOMM"));
aFreightQuotation.setReceiver(rs.getString("BRMSCO"));
aFreightQuotation.setFirmCode(rs.getString("BRMSCO"));
aFreightQuotation.setIataPortLoading(rs.getString("BRNVER"));
aFreightQuotation.setIataPortOrigin(rs.getString("BRNOOR"));
aFreightQuotation.setIataPortDestination(rs.getString("BRNBES"));
aFreightQuotation.setIataPortDischarge(rs.getString("BRNLOS"));
aFreightQuotation.setPrecarrying(rs.getString("BRPREC"));
aFreightQuotation.setOncarrying(rs.getString("BRONCR"));
aFreightQuotation.setTransportMode(rs.getString("BRDRCT"));
return aFreightQuotation;
}
/**
* disconnectFromDB method comment.
*/
public static void disconnectFromDB()
{
try
{
psPerAgent.close();
psPerKeyInput.close();
psUpdateRecord.close();
clipper.disconnect();
}
catch (java.sql.SQLException e)
{
e.printStackTrace();
}
}
/**
* Insert the method's description here.
* Creation date: (2/03/2001 20:33:24)
* @param args java.lang.String[]
*/
public static void main(String[] args)
{
FreightQuotations vFreightQuotations = new FreightQuotations();
Vector rv = new Vector();
// Get Connection
try
{
conClipper = connectToDB("system","user", "password");
try
{
conClipper.setAutoCommit(false);
}
catch (SQLException e)
{
e.printStackTrace();
}
}
catch (Exception e)
{
e.printStackTrace();
}
// Get Quotation per Agent
try
{
String sAgentCode = "JOSHIP%";
String sStatusCode = "%";
String sLoadingPort = "%";
String sDischargePort = "%";
rv = vFreightQuotations.getPerAgent(conClipper, sAgentCode, sStatusCode,
sLoadingPort, sDischargePort);
}
catch (Exception e)
{
e.printStackTrace();
}
for (int i = 0; i < rv.size(); i++)
{
FreightQuotation as = new FreightQuotation();
as = (FreightQuotation)rv.get(i);
System.out.println(i + "." + as.getFileLine() +
" " + as.getAgentCode() + " " + as.getShipperCode());
}
// Get Quotation per Inputkey
FreightQuotation aFreightQuotation = new FreightQuotation();
try
{
short sKeyInCC = 19;
short sKeyInYY = 98;
short sKeyInMM = 11;
short sKeyInDD = 19;
short sKeySequence = 17;
aFreightQuotation = vFreightQuotations.getPerKeyInput(conClipper,
sKeyInCC, sKeyInYY, sKeyInMM, sKeyInDD, sKeySequence);
}
catch (Exception e)
{
e.printStackTrace();
}
System.out.println(aFreightQuotation.getFileLine() +
" " + aFreightQuotation.getAgentCode() + " " +
aFreightQuotation.getShipperCode());
// Rollback any transactions
try
{
conClipper.rollback();
conClipper.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
private static Connection conClipper; private static PreparedStatement
psPerAgent; private static PreparedStatement psPerKeyInput; private
static PreparedStatement psUpdateRecord;/**
* connectToDB method comment.
*/
public static Connection connectToDB(String systemName, String userid,
String password) throws Exception
{
Connection connectJDBC = clipper.connect(systemName, userid, password);
return connectJDBC;
}/**
* getFile method comment.
*/
public static java.util.Vector getPerAgent(Connection clipper, String
agentCode, String statusCode, String loadingPort, String dischargePort)
throws Exception
{
ResultSet rs = null;
java.util.Vector vector = new java.util.Vector(40, 10);
// create the statements
psPerAgent = clipper.prepareStatement("SELECT * FROM LSJAVA.UPBOKINTST
WHERE BRAGEN LIKE ? AND BRSTAT LIKE ? AND BRNVER LIKE ? AND BRNLOS LIKE ?
FOR READ ONLY");
psPerAgent.setString(1,(agentCode));
psPerAgent.setString(2,(statusCode));
psPerAgent.setString(3,(loadingPort));
psPerAgent.setString(4,(dischargePort));
System.out.println("Retrieving records with: " + agentCode + statusCode +
loadingPort + dischargePort);
try
{
rs = psPerAgent.executeQuery();
System.out.println("Retrieving retrieved");
while (rs.next())
{
FreightQuotation aFreightQuotation = null;
aFreightQuotation = convertRecord(rs);
vector.addElement(aFreightQuotation);
}
}
catch (SQLException e)
{
System.out.println("No records retrieve because SQL-error:" + agentCode +
statusCode + loadingPort + dischargePort);
System.out.println(" SQLCODE =" + e.getErrorCode());
System.out.println(" SQL message =" + e.getMessage());
System.out.println(" SQL LocalMsg =" + e.getLocalizedMessage());
e.printStackTrace();
}
return vector;
}/**
* getFile method comment.
*/
public static FreightQuotation getPerKeyInput(Connection clipper, short
keyInCC, short keyInYY, short keyInMM, short keyInDD, int keySequence)
throws Exception
{
ResultSet rs = null;
FreightQuotation aFreightQuotation = new FreightQuotation();
try
{
// Create statement and Read record
System.out.println("Retrieving: "+keyInCC+" "+keyInYY+" "+keyInMM+" "
+keyInDD+" "+keySequence);
psPerKeyInput = clipper.prepareStatement("SELECT * FROM LSJAVA.UPBOKINTST
WHERE BRDICC=? AND BRDIJJ=? AND BRDIMM = ? AND BRDIDD = ? AND BRRENR = ?
FOR READ ONLY");
psPerKeyInput.setShort(1,(keyInCC));
psPerKeyInput.setShort(2,(keyInYY));
psPerKeyInput.setShort(3,(keyInMM));
psPerKeyInput.setShort(4,(keyInDD));
psPerKeyInput.setInt(5,(keySequence));
rs = psPerKeyInput.executeQuery();
System.out.println("Record Retrieved: "+keyInCC+" "+keyInYY+" "+keyInMM+"
"+keyInDD+" "+keySequence+" successfully");
// Update record to see if it's free
if (setKeyInput(clipper, keyInCC, keyInYY, keyInMM, keyInDD,
keySequence))
{
if (rs.next())
{
aFreightQuotation = convertRecord(rs);
}
else
{
aFreightQuotation = null;
System.out.println("No records found for this selection...");
}
}
else
{
aFreightQuotation = null;
System.out.println("problem with record retrieval because of update
error...");
}
}
catch (SQLException e)
{
aFreightQuotation = null;
System.out.println("Record not retrieved because SQL-error:"+keyInCC+" "
+keyInYY+" "+keyInMM+" "+keyInDD+" "+keySequence+" successfully");
System.out.println(" SQLCODE =" + e.getErrorCode());
System.out.println(" SQL message =" + e.getMessage());
System.out.println(" SQL LocalMsg =" + e.getLocalizedMessage());
e.printStackTrace();
}
return aFreightQuotation;
}/**
* getFile method comment.
*/
public static boolean setKeyInput(Connection clipper, short keyInCC,
short keyInYY, short keyInMM, short keyInDD, int keySequence) throws
Exception
{
ResultSet rs = null;
FreightQuotation aFreightQuotation = new FreightQuotation();
boolean dbAccessOk = false;
// Update record
try
{
System.out.println("Autocommit=" + clipper.getAutoCommit() +",
TransActionIsolationLevel=" + clipper.getTransactionIsolation());
System.out.println("Updating : "+keyInCC+" "+keyInYY+" "+keyInMM+" "
+keyInDD+" "+keySequence);
psUpdateRecord = clipper.prepareStatement("UPDATE LSJAVA.UPBOKINTST SET
BRSTAT=? WHERE BRDICC=? AND BRDIJJ=? AND BRDIMM = ? AND BRDIDD = ? AND
BRRENR = ?");
psUpdateRecord.setString(1,("TR"));
psUpdateRecord.setShort(2,(keyInCC));
psUpdateRecord.setShort(3,(keyInYY));
psUpdateRecord.setShort(4,(keyInMM));
psUpdateRecord.setShort(5,(keyInDD));
psUpdateRecord.setInt(6,(keySequence));
psUpdateRecord.executeUpdate();
System.out.println("Record Updated : "+keyInCC+" "+keyInYY+" "+keyInMM+"
"+keyInDD+" "+keySequence+" successfully");
dbAccessOk = true;
}
catch (SQLException e)
{
System.out.println("Record not available for update:");
System.out.println(" SQLCODE =" + e.getErrorCode());
System.out.println(" SQL message =" + e.getMessage());
System.out.println(" SQL LocalMsg =" + e.getLocalizedMessage());
e.printStackTrace();
}
return dbAccessOk;
}}
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
