|
midrange.com
We have an application running which which uses SQL to retrieve and store
data on our AS400. Application uses JSP/Servlets. Today I noticed that
the application didn't work anymore. It can retrieve data but an
UPDATE_statement doesn't work anymore. (We have some logging_statements in
our javaclasses which helped me to find the problem is with an
update_statement). It leads to a problem with parameter (field) mapping to
the DB-file but when I do same in VAJAVA or from VAJAVA_TE it works normal.
(grrrrrr)
I already did following:
1) Restore full application as follows
- End WAS353SE
- Export of all classes from VAJAVA to WAS353SE
- Dummy change of JSP to force recompile
- restart WAS353SE
- call JSP's from within webbrowser (selection-, list- and detailpanels)
===> problem still occur
2) Export full application to VAJAVA_TE as follows
- Copy all classes/html/jsp to VAJAVA_TE
- start TE
- call JSP's from within webbrowser (selection-, list- and detailpanels)
===> this works normal ...
Why is it working in VAJAVA_TE and not in WAS353SE ????
This is an extract of the coding executed when update failes
'updatePerKeyInput' is called from 'getPerKeyInput' after succesfull read
to check if it's not in use by someone else.
/**
* updatePerKeyInput method comment.
*/
public static boolean updatePerKeyInput(Connection clipper,
FreightQuotation aFreightQuotation) throws Exception
{
ResultSet rs = null;
boolean dbAccessOk = false;
// Create the statements
PreparedStatement psUpdateRecord = clipper.prepareStatement("UPDATE
CDEXPORT.UPBOKIN " +
"SET BRDICC=?, BRDIJJ=?, BRDIMM=?, BRDIDD=?, BRRENR=?,
BRITEM=?, BRRECN=?, " +
"BRNAMC=?, BRSTAT=?, BRLIJN=?, BRJAAR=?, BRVOLG=?,
BRHOOR=?, BRHVER=?, " +
"BRHBES=?, BRHLOS=?, BRBOKN=?, BRTERM=?, BRALIN=?,
BRAGEN=?, BRVERL=?, " +
"BREXPE=?, BRBFLC=?, BRCOLL=?, BRVERP=?, BROMGO=?,
BRGEWI=?, BRCUBE=?, " +
"BRBAS1=?, BRBTNK=?, BRIMCO=?, BRPAGE=?, BRUNNO=?,
BRFLAP=?, BRSIZE=?, " +
"BRTYPE=?, BRSOCC=?, BRFUEM=?, BRTARE=?, BRSER1=?,
BRSER2=?, BRBTNT=?, " +
"BRBTVT=?, BRTRAD=?, BRQUOT=?, BRREJC=?, BRSEGM=?,
BRMSCO=?, BRFRCC=?, " +
"BRFRYY=?, BRFRMM=?, BRTOCC=?, BRTOYY=?, BRTOMM=?,
BRRECV=?, BRFIRM=?, " +
"BRNVER=?, BRNOOR=?, BRNBES=?, BRNLOS=?, BRPREC=?,
BRONCR=?, BRDRCT=? " +
"WHERE BRDICC=? AND BRDIJJ=? AND BRDIMM = ? AND BRDIDD
= ? AND BRRENR = ?");
// SET
psUpdateRecord.setShort(1,(aFreightQuotation.getKeyInCC()));
psUpdateRecord.setShort(2,(aFreightQuotation.getKeyInYY()));
psUpdateRecord.setShort(3,(aFreightQuotation.getKeyInMM()));
psUpdateRecord.setShort(4,(aFreightQuotation.getKeyInDD()));
psUpdateRecord.setInt(5,(aFreightQuotation.getKeySequence()));
psUpdateRecord.setShort(6,(aFreightQuotation.getItemNr()));
psUpdateRecord.setShort(7,(aFreightQuotation.getRecordNr()));
psUpdateRecord.setString(8,(aFreightQuotation.getNewAnnMod()));
psUpdateRecord.setString(9,(aFreightQuotation.getStatus()));
psUpdateRecord.setShort(10,(aFreightQuotation.getFileLine()));
psUpdateRecord.setShort(11,(aFreightQuotation.getFileYear()));
psUpdateRecord.setShort(12,(aFreightQuotation.getFileSequence()));
psUpdateRecord.setString(13,(aFreightQuotation.getPortOrigin()));
psUpdateRecord.setString(14,(aFreightQuotation.getPortLoading()));
psUpdateRecord.setString(15,(aFreightQuotation.getPortDestination
()));
psUpdateRecord.setString(16,(aFreightQuotation.getPortDischarge
()));
psUpdateRecord.setString(17,(aFreightQuotation.getBookingNr()));
psUpdateRecord.setString(18,(aFreightQuotation.getTerms()));
psUpdateRecord.setString(19,(aFreightQuotation.getAllInYN()));
psUpdateRecord.setString(20,(aFreightQuotation.getAgentCode()));
psUpdateRecord.setString(21,(aFreightQuotation.getShipperCode()));
psUpdateRecord.setString(22,(aFreightQuotation.getForwarderCode
()));
psUpdateRecord.setString(23,(aFreightQuotation.getCargoType()));
psUpdateRecord.setInt(24,(aFreightQuotation.getColli()));
psUpdateRecord.setString(25,(aFreightQuotation.getPackageCode()));
psUpdateRecord.setString(26,(aFreightQuotation.getGoodsDescription
()));
psUpdateRecord.setInt(27,(aFreightQuotation.getWeight()));
psUpdateRecord.setInt(28,(aFreightQuotation.getCubage()));
psUpdateRecord.setInt(29,(aFreightQuotation.getCalculationBase()));
psUpdateRecord.setString(30,(aFreightQuotation.getBtnCode()));
psUpdateRecord.setString(31,(aFreightQuotation.getImcoCode()));
psUpdateRecord.setString(32,(aFreightQuotation.getImcoPage()));
psUpdateRecord.setString(33,(aFreightQuotation.getImcoUnno()));
psUpdateRecord.setString(34,(aFreightQuotation.getImcoFlashPoint
()));
psUpdateRecord.setString(35,(aFreightQuotation.getContainerSize
()));
psUpdateRecord.setString(36,(aFreightQuotation.getContainerType
()));
psUpdateRecord.setString(37,(aFreightQuotation.getContainerShippersOwnedYN
()));
psUpdateRecord.setString(38,(aFreightQuotation.getContainerFullEmpty()));
psUpdateRecord.setInt(39,(aFreightQuotation.getContainerTarra()));
psUpdateRecord.setString(40,(aFreightQuotation.getContainerServiceLoading
()));
psUpdateRecord.setString(41,(aFreightQuotation.getContainerServiceDischarge
()));
psUpdateRecord.setString(42,(aFreightQuotation.getBtnTariff()));
psUpdateRecord.setShort(43,(aFreightQuotation.getBtnTariffSequence
()));
psUpdateRecord.setString(44,(aFreightQuotation.getQuotationTrade
()));
psUpdateRecord.setString(45,(aFreightQuotation.getQuotationNr()));
psUpdateRecord.setString(46,(aFreightQuotation.getRejectionCode
()));
psUpdateRecord.setInt(47,(aFreightQuotation.getRejectionDescription
()));
psUpdateRecord.setString(48,(aFreightQuotation.getMessageCode()));
psUpdateRecord.setShort(49,(aFreightQuotation.getValidFromCC()));
psUpdateRecord.setShort(50,(aFreightQuotation.getValidFromYY()));
psUpdateRecord.setShort(51,(aFreightQuotation.getValidFromMM()));
psUpdateRecord.setShort(52,(aFreightQuotation.getValidToCC()));
psUpdateRecord.setShort(53,(aFreightQuotation.getValidToYY()));
psUpdateRecord.setShort(54,(aFreightQuotation.getValidToMM()));
psUpdateRecord.setString(55,(aFreightQuotation.getReceiver()));
psUpdateRecord.setString(56,(aFreightQuotation.getFirmCode()));
psUpdateRecord.setString(57,(aFreightQuotation.getIataPortLoading
()));
psUpdateRecord.setString(58,(aFreightQuotation.getIataPortOrigin
()));
psUpdateRecord.setString(59,(aFreightQuotation.getIataPortDestination()));
psUpdateRecord.setString(60,(aFreightQuotation.getIataPortDischarge
()));
psUpdateRecord.setString(61,(aFreightQuotation.getPrecarrying()));
psUpdateRecord.setString(62,(aFreightQuotation.getOncarrying()));
psUpdateRecord.setString(63,(aFreightQuotation.getTransportMode
()));
// WHERE
psUpdateRecord.setShort(64,(aFreightQuotation.getKeyInCC()));
psUpdateRecord.setShort(65,(aFreightQuotation.getKeyInYY()));
psUpdateRecord.setShort(66,(aFreightQuotation.getKeyInMM()));
psUpdateRecord.setShort(67,(aFreightQuotation.getKeyInDD()));
psUpdateRecord.setInt(68,(aFreightQuotation.getKeySequence()));
// Update record in DB
System.out.println("Updating record with : "
+aFreightQuotation.getKeyInCC()+" "+aFreightQuotation.getKeyInYY()+" "
+aFreightQuotation.getKeyInMM()+" "+aFreightQuotation.getKeyInDD()+" "
+aFreightQuotation.getKeySequence());
System.out.println(" autocommit=" + clipper.getAutoCommit() +",
TransActionIsolationLevel=" + clipper.getTransactionIsolation());
try
{
psUpdateRecord.executeUpdate();
System.out.println("Record Updated : "
+aFreightQuotation.getKeyInCC()+" "+aFreightQuotation.getKeyInYY()+" "
+aFreightQuotation.getKeyInMM()+" "+aFreightQuotation.getKeyInDD()+" "
+aFreightQuotation.getKeySequence()+" successfully");
dbAccessOk = true;
}
catch (SQLException e)
{
System.out.println("Record not available for update: "
+aFreightQuotation.getKeyInCC()+" "+aFreightQuotation.getKeyInYY()+" "
+aFreightQuotation.getKeyInMM()+" "+aFreightQuotation.getKeyInDD()+" "
+aFreightQuotation.getKeySequence());
System.out.println(" SQLCODE =" +
e.getErrorCode());
System.out.println(" SQL message =" +
e.getMessage());
System.out.println(" SQL LocalMsg =" +
e.getLocalizedMessage());
System.out.println(" SQL State =" +
e.getSQLState());
System.out.println(" autoCommit =" +
clipper.getAutoCommit());
System.out.println(" transactionIsolation=" +
clipper.getTransactionIsolation());
e.printStackTrace();
}
psUpdateRecord.close();
return dbAccessOk;
}
/**
* getPerKeyInput 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();
// Create statement
PreparedStatement psPerKeyInput = clipper.prepareStatement("SELECT
* FROM CDEXPORT.UPBOKIN WHERE BRDICC=? AND BRDIJJ=? AND BRDIMM = ? AND
BRDIDD = ? AND BRRENR = ? FOR UPDATE");
psPerKeyInput.setShort(1,(keyInCC));
psPerKeyInput.setShort(2,(keyInYY));
psPerKeyInput.setShort(3,(keyInMM));
psPerKeyInput.setShort(4,(keyInDD));
psPerKeyInput.setInt(5,(keySequence));
// Get records from DB
System.out.println("Retrieving: "+keyInCC+" "+keyInYY+" "+keyInMM+"
"+keyInDD+" "+keySequence);
System.out.println(" autoCommit =" + clipper.getAutoCommit() +
", transactionIsolation=" + clipper.getTransactionIsolation());
try
{
rs = psPerKeyInput.executeQuery();
if (rs.next())
{
aFreightQuotation = convertRecord(rs);
System.out.println("Record Retrieved: "+keyInCC+" "
+keyInYY+" "+keyInMM+" "+keyInDD+" "+keySequence+" successfully");
// Update record to see if it's free
if (updatePerKeyInput(clipper, aFreightQuotation))
{
System.out.println("Update record completed
normally ");
}
else
{
aFreightQuotation = null;
System.out.println("Record in use ?");
}
}
else
{
aFreightQuotation = null;
System.out.println("No records found for this
selection...");
}
}
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());
System.out.println(" SQL State =" +
e.getSQLState());
System.out.println(" autoCommit =" +
clipper.getAutoCommit());
System.out.println(" transactionIsolation=" +
clipper.getTransactionIsolation());
e.printStackTrace();
}
psPerKeyInput.close();
return aFreightQuotation;
}
This is are the logging statements received in 'default_server_stdout.log'
CodeTablesBean FQCBean_getQuotation(connection, String, int):
Action=Select, selectionidx=2
CodeTablesBean FQCBean_getQuotation(connection, String, int):
Keyfieldvalues : Parm1=20 Parm2=1 Parm3=12 Parm4=7 Parm5=66
CodeTablesBean FQCBean_getQuotation(connection, String, int): Getting AS400
Data
Retrieving: 20 1 12 7 66
autoCommit =false, transactionIsolation=1
Record Retrieved: 20 1 12 7 66 successfully
Updating record with : 20 1 12 7 66
autocommit=false, TransActionIsolationLevel=1
Record not available for update: 20 1 12 7 66
SQLCODE =-601
SQL message =[PWS0043] Unable to use the parameter marker values
provided. Cause . . . . . : Either the parameter marker values or the
parameter marker description was not provided or was not valid. Recovery .
. . : Verify that the following are correct and run the function again.
-- Parameter marker data and an existing descriptor handle are provided. --
The descriptor provided matches the parameter marker data.
SQL LocalMsg =[PWS0043] Unable to use the parameter marker values
provided. Cause . . . . . : Either the parameter marker values or the
parameter marker description was not provided or was not valid. Recovery .
. . : Verify that the following are correct and run the function again.
-- Parameter marker data and an existing descriptor handle are provided. --
The descriptor provided matches the parameter marker data.
SQL State =HY001
autoCommit =false
transactionIsolation=1
Record in use ?
This is the message received in 'default_server_stderr.log'
java.sql.SQLException: [PWS0043] Unable to use the parameter marker values
provided. Cause . . . . . : Either the parameter marker values or the
parameter marker description was not provided or was not valid. Recovery .
. . : Verify that the following are correct and run the function again.
-- Parameter marker data and an existing descriptor handle are provided. --
The descriptor provided matches the parameter marker data.
java/lang/Throwable.<init>(Ljava/lang/String;)V+4 (Throwable.java:94)
java/sql/SQLException.<init>(Ljava/lang/String;Ljava/lang/String;I)V+1
(SQLException.java:43)
com/ibm/as400/access/JDError.throwSQLException(Lcom/ibm/as400/access/AS400JDBCConnection;III)V+0
(JDError.java:359)
com/ibm/as400/access/AS400JDBCStatement.commonExecute(Lcom/ibm/as400/access/JDSQLStatement;Lcom/ibm/as400/access/JDServerRow;)V+0
(AS400JDBCStatement.java:467)
com/ibm/as400/access/AS400JDBCPreparedStatement.executeUpdate()I+0
(AS400JDBCPreparedStatement.java:897)
com/clipper/objects/FreightQuotations.updatePerKeyInput(Ljava/sql/Connection;Lcom/clipper/model/FreightQuotation;)Z+0
(FreightQuotations.java:493)
com/clipper/objects/FreightQuotations.getPerKeyInput(Ljava/sql/Connection;SSSSI)Lcom/clipper/model/FreightQuotation;
0 (FreightQuotations.java:219)
com/clipper/beans/FQCBean.getQuotation(Ljava/sql/Connection;Ljava/lang/String;I)V+0
(FQCBean.java:55)
com/clipper/servlets/FQController.selectDetail(Ljava/lang/String;I)Z+0
(FQController.java:641)
com/clipper/servlets/FQController.doGet(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V+0
(FQController.java:335)
com/clipper/servlets/FQController.doPost(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V+0
(FQController.java:575)
javax/servlet/http/HttpServlet.service(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V+136
(HttpServlet.java:760)
javax/servlet/http/HttpServlet.service(Ljavax/servlet/ServletRequest;Ljavax/servlet/ServletResponse;)V+25
(HttpServlet.java:853)
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.
