× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I don't save email from java400-l so can't look back in history.
Is there an archive somewhere?

It was a new post from friday, the archive is at www.midrange.com.

Neither one of the isolationsetings work!  I tried read_uncommitted,
read_committed and repeatable-read.  However, when I read first from the
webapp (user1) and then run from within VAJAVA (user2), the coding works as
it should be.
User1 gets the record for update. User2 can only read the data and it not
permitted to get the record for update!

I'll include the 'Locked records mail' with full detail which includes the
specific class:

quote "
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;
  }}

" unquote




As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.