SQL syntax error when running same servlet concurrently

[Former Member]

Hi All,

We are having a strange problem. We have a method in a servlet having a few database select calls. This method is called directly from "service" method of the servlet. All these database calls are Statements(dbconnection.create

statement()) and resultsets.

When we execute this servlet from one system it has no problems and returns all the results successfully. However, when I invoke the same servlet from 2 systems to check for concurrent processing it comes back with strange database sql syntax errors like

<<

com.sap.sql.sqlparser.CommonSQLParserException: - 1:15 - SQL syntax error: the token "," was not expected here

>>

<<

com.sap.sql.sqlparser.CommonSQLParserException: - 1:1 - SQL syntax error: unexpected end of statement

>>

It definetely looks like something to do with making concurrent connections. Has anyone had this sort of problem before.

Thanks in advance for your help.

Cheers,

Sundeep

[Former Member · Accepted solution]

Hello

could you please post your servle coding, otherwise it´s hard to narrow down the problem

regards franz

[Former Member · Accepted solution]

Hi Franz,

Please see the code below.

package com.proj.uk.op.servlets;

import java.io.IOException;

import java.io.PrintWriter;

import java.io.StringWriter;

import java.sql.CallableStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.Types;

import javax.servlet.ServletConfig;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.servlet.http.HttpSession;

import com.proj.uk.op.accesscontrol.AccessControl;

import com.proj.uk.op.db.DbConnection;

public class CartonReversal_CN extends HttpServlet {

// data members

DbConnection dbConnection;

public void init(ServletConfig config) throws ServletException {

super.init(config);

}

public void service(

HttpServletRequest request,

HttpServletResponse response)

throws ServletException, IOException {

AccessControl ac = new AccessControl(request, response, "CRTREVERSE");

HttpSession session = request.getSession(true);

String userId = (String) session.getAttribute("loginUser");

String errmsg = "";

int chk7 = 0;

String userOption = request.getParameter("Select");

try {

dbConnection = new DbConnection();

dbConnection.open();

if (userOption.trim().equals("Select")) {

String cartonNo = request.getParameter("cartonNo");

cartonNo = cartonNo.trim();

processCtnRvrsl(cartonNo,userId,request,response);

} else if (userOption.equals("Confirm reversal")) {

String cartonNo = request.getParameter("cartonNo");

chk7 = callProcedure(cartonNo, userId, request, response);

} else {

errmsg = "Unknown user option: " + userOption;

errorMsg(errmsg, request, response);

}

dbConnection.close();

} catch (Exception e) {

e.printStackTrace();

errmsg = "An exception occured during the selection of carton details";

errorMsg(errmsg, request, response);

}

}

public void processCtnRvrsl(String cartonNo,String userId, HttpServletRequest request,

HttpServletResponse response) {

int chk1 = 0;

int chk2 = 0;

int chk3 = 0;

int chk4 = 0;

int chk5 = 0;

int chk6 = 0;

int chk7 = 0;

int chk8 = 0;

int chk9 = 0;

long RMQTY = 0;

String param1_alpltno = "";

String param1_ordnum = "";

String param1_plthusr = "";

String param1_plthmltflg = "";

int param1_plthtctn = 0;

long param1_plthqty = 0;

String param1_catnum = "";

String param1_paltno = "";

String param2_ordnum = "";

String param2_alnpltno = "";

long param2_alnrqty = 0;

long param2_alnbqty = 0;

String param2_alnsts = "";

String param2_alnwrkc = "";

long param2_alnsl = 0;

String param2_alndept = "";

String param2_alnshift = "";

long param2_alnop = 0;

long param2_alnfloat = 0;

String param2_catnum = "";

String param2_paltno = "";

String param3_ctnprdlot = "";

String param3_ctnnum = "";

String param3_alnpltno = "";

long param3_ctnqty = 0;

String param3_ctnsflg = "";

String param3_ctnlotdt = "";

String param3_ctnupdt = "";

String param3_catnum = "";

String param3_paltno = "";

String param3_bomline = "";

String param3t_ctnprdlot = "";

String param3t_ctnnum = "";

String param3t_alnpltno = "";

long param3t_ctnqty = 0;

String param3t_ctnsflg = "";

String param3t_ctnlotdt = "";

String param3t_ctnupdt = "";

String param3t_catnum = "";

String param3t_paltno = "";

String param3t_bomline = "";

String param4_ordnum = "";

String param4_ordplnt = "";

String param4_ordwrkcn = "";

String param4_ordtyp = "";

String param4_ordpart = "";

String param4_orddsc = "";

long param4_ordtqty = 0;

long param4_ordscqty = 0;

String param4_ordgpart = "";

long param4_ordbqty = 0;

String param4_ordsts = "";

String param4_orderrflg = "";

String param4_ordupqty = "";

long param4_ordtgqty = 0;

String param5_ordnum = "";

String param5_alnpltno = "";

long param5_alnrqty = 0;

long param5_alnbqty = 0;

String param5_alnsts = "";

String param5_alnwrkc = "";

String param5_alnsl = "";

String param5_alndept = "";

String param5_alnshift = "";

int param5_alnop = 0;

int param5_alnfloat = 0;

String param5_catnum = "";

String param5_paltno = "";

String param6_alnpltno = "";

String param6_ctnnum = "";

String param6_ordnum = "";

String param6_opart = "";

String param6_ogpart = "";

String param6_owcn = "";

String param6_odsc = "";

long param6_otqty = 0;

long param6_obqty = 0;

long param6_orqty = 0;

long param6_ormqty = 0;

String param6_ctnprdlot = "";

long param6_ctnqty = 0;

long param6_alnrqty = 0;

long param6_ochgqty = 0;

String param6_newprdlot = "";

String errflg = "";

String errmsg = "";

String bomExists = "0";

String htmlPage = " ";

String htmlHead1 = " ";

String htmlHead2 = " ";

String htmlForm = " ";

String htmlBottom = " ";

//CHECK PLTHEAD

try {

String query =

"select ALNPLTNO,CATNUM,PALTNO from PROJ_OP_ORDALN where CATNUM="

+ "'"

+ cartonNo

+ "'";

Statement stmt = dbConnection.createStatement();

ResultSet dataResultSet = stmt.executeQuery(query);

if (dataResultSet.next()) {

param1_alpltno = dataResultSet.getString(1);

param1_catnum = dataResultSet.getString(2);

param1_paltno = dataResultSet.getString(3);

if (param1_paltno != null && !"".equals(param1_paltno)) {

chk1 = 0;

errmsg =

"Carton Number "

+ cartonNo

+ " has already been confirmed.";

} else {

chk1 = 1;

}

} else {

errmsg = "Carton number ";

errmsg += cartonNo;

errmsg += " does not exist";

chk1 = 0;

}

dataResultSet.close();

stmt.close();

} catch (Exception e) {

cleanUp();

chk1 = 0;

e.printStackTrace();

errmsg = "An exception occured during the selection of carton details";

errorMsg(errmsg, request, response);

}

if (chk1 == 1) {

// CHECK ORDALN

try {

String query =

"select ORDNUM,ALNPLTNO,ALNRQTY,ALNBQTY,ALNWRKC,ALNSL,ALNDEPT,ALNSHIFT,ALNOP,ALNFLOAT,CATNUM,PALTNO from PROJ_OP_ORDALN where CATNUM="

+ "'"

+ cartonNo

+ "'"

+ " and "

+ "ALNSTS="

+ "'O'";

Statement stmt = dbConnection.createStatement();

ResultSet dataResultSet = stmt.executeQuery(query);

if (dataResultSet.next()) {

param2_ordnum = dataResultSet.getString(1);

param2_alnpltno = dataResultSet.getString(2);

param2_alnrqty = dataResultSet.getLong(3);

param2_alnbqty = dataResultSet.getLong(4);

param2_alnwrkc = dataResultSet.getString(5);

param2_alnsl = dataResultSet.getLong(6);

param2_alndept = dataResultSet.getString(7);

param2_alnshift = dataResultSet.getString(8);

param2_alnop = dataResultSet.getLong(9);

param2_alnfloat = dataResultSet.getLong(10);

param2_catnum = dataResultSet.getString(11);

param2_paltno = dataResultSet.getString(12);

chk2 = 1;

} else {

errmsg = "Carton Number ";

errmsg += cartonNo;

errmsg += " is not allocated";

chk2 = 0;

}

dataResultSet.close();

stmt.close();

} catch (Exception e) {

cleanUp();

chk2 = 0;

e.printStackTrace();

errmsg = "An exception occured during the selection of carton details";

errorMsg(errmsg, request, response);

}

if (chk2 == 1) {

// DISPLAY CARTON

try {

htmlHead1 = "<html><head>";

htmlHead1 += "<title>Carton Reversal</TITLE>";

htmlHead2 = "</HEAD>";

htmlHead2 += "<table width=100% border=0>";

htmlHead2 += "<tr>";

htmlHead2

+= "<th bgcolor=blue><font color=white>Reverse Carton confirmation</font></th>";

htmlHead2 += "</tr>";

htmlHead2 += "</table>";

htmlHead2 += "<div>";

htmlForm = "<FORM name='form_list' method=POST action='/EOP/CartonReversal'>";

htmlForm

+= "<table width=100% border=1 align=center bgcolor=lightblue>";

htmlForm += "<tr>";

htmlForm += "<td>";

htmlForm += "<B>Carton Number&nbsp;</B>";

htmlForm += "</td>";

htmlForm += "<td>";

htmlForm += "<p>";

htmlForm += "<INPUT type=\""

+ "text\""

+ " name=\""

+ "CATNUM\""

+ " size=\""

+ "10\""

+ " value=\""

+ cartonNo

+ "\""

+ " disabled=\""

+ "true\""

+ " style=\""

+ "font: bolder 10pt Verdana; color: #23238e; border: Black 1 Solid; background-color: #FFFFCC;\""

+ ">";

htmlForm += "</td>";

htmlForm += "<td>";

htmlForm += "<B>Order Number&nbsp;</B>";

htmlForm += "</td>";

htmlForm += "<td>";

htmlForm += "<p>";

htmlForm += "<INPUT type=\""

+ "text\""

+ " name=\""

+ "ONUM\""

+ " size=\""

+ "12\""

+ " value=\""

+ param4_ordnum

+ "\""

+ " disabled=\""

+ "true\""

+ " style=\""

+ "font: bolder 10pt Verdana; color: #23238e; border: Black 1 Solid; background-color: #FFFFCC;\""

+ ">";

htmlForm += "</td>";

htmlForm += "</TR>";

htmlForm += "<tr>";

htmlForm += "<TD>";

htmlForm += "<B>Product&nbsp;</B>";

htmlForm += "</td>";

htmlForm += "<TD><p>";

htmlForm += "<INPUT type=\""

+ "text\""

+ " name=\""

+ "OPART\""

+ " size=\""

+ "18\""

+ " value=\""

+ param4_ordpart

+ "\""

+ " disabled=\""

+ "true\""

+ " style=\""

+ "font: bolder 10pt Verdana; color: #23238e; border: Black 1 Solid; background-color: #FFFFCC;\""

+ ">";

htmlForm += "</td>";

htmlForm += "<TD><B>User&nbsp;</B>";

htmlForm += "</TD>";

htmlForm += "<TD><p>";

htmlForm += "<INPUT type=\""

+ "text\""

+ " name=\""

+ "USER\""

+ " size=\""

+ "8\""

+ " value="

+ userId

+ " disabled=\""

+ "true\""

+ " style=\""

+ "font: bolder 10pt Verdana; color: Navy; border: Black 1 Solid; background-color: #FFFFCC;\""

+ ">";

htmlForm += "</TD>";

htmlForm += "</TR>";

htmlForm += "<TR>";

htmlForm += "<TD>";

htmlForm += "&nbsp;";

htmlForm += "</td>";

htmlForm += "<TD>";

htmlForm += "&nbsp;";

htmlForm += "</td>";

htmlForm += "<TD>";

htmlForm += "<B>Description&nbsp;</B>";

htmlForm += "</td>";

htmlForm += "<TD><p>";

htmlForm += "<INPUT type=\""

+ "text\""

+ " name=\""

+ "ODSC\""

+ " size=\""

+ "40\""

+ " value=\""

+ param4_orddsc

+ "\""

+ " disabled=\""

+ "true\""

+ " style=\""

+ "font: bolder 10pt Verdana; color: #23238e; border: Black 1 Solid; background-color: #FFFFCC;\""

+ ">";

htmlForm += "</td>";

htmlForm += "</TR>";

htmlForm += "<TR>";

htmlForm += "<TD><B>Target Quantity&nbsp;</B>";

htmlForm += "</TD>";

htmlForm += "<TD><p>";

htmlForm += "<INPUT type=\""

+ "text\""

+ " name=\""

+ "OTQTY\""

+ " size=\""

+ "13\""

+ " value="

+ param4_ordtgqty

+ " disabled=\""

+ "true\""

+ " style=\""

+ "font: bolder 10pt Verdana; color: #23238e; border: Black 1 Solid; background-color: #FFFFCC;\""

+ ">";

htmlForm += "</TD>";

htmlForm += "<TD><B>Build Quantity&nbsp;</B>";

htmlForm += "</TD>";

htmlForm += "<TD><p>";

htmlForm += "<INPUT type=\""

+ "text\""

+ " name=\""

+ "OBQTY\""

+ " size=\""

+ "13\""

+ " value="

+ param4_ordbqty

+ " disabled=\""

+ "true\""

+ " style=\""

+ "font: bolder 10pt Verdana; color: #23238e; border: Black 1 Solid; background-color: #FFFFCC;\""

+ ">";

htmlForm += "</TD>";

htmlForm += "</TR>";

htmlForm += "<TR>";

htmlForm += "<TD><B>Reserved Quantity&nbsp;</B>";

htmlForm += "</TD>";

htmlForm += "<TD><p>";

htmlForm += "<INPUT type=\""

+ "text\""

+ " name=\""

+ "ORQTY\""

+ " size=\""

+ "13\""

+ " value="

+ param5_alnrqty

+ " disabled=\""

+ "true\""

+ " style=\""

+ "font: bolder 10pt Verdana; color: #23238e; border: Black 1 Solid; background-color: #FFFFCC;\""

+ ">";

htmlForm += "</TD>";

htmlForm += "<TD><B>Remaining Quantity&nbsp;</B>";

htmlForm += "</TD>";

htmlForm += "<TD><p>";

htmlForm += "<INPUT type=\""

+ "text\""

+ " name=\""

+ "ORMQTY\""

+ " size=\""

+ "13\""

+ " value="

+ RMQTY

+ " disabled=\""

+ "true\""

+ " style=\""

+ "font: bolder 10pt Verdana; color: #23238e; border: Black 1 Solid; background-color: #FFFFCC;\""

+ ">";

htmlForm += "</TD>";

htmlForm += "</TR>";

htmlForm += "<TR>";

htmlForm += "<TD><B>Carton Quantity&nbsp;</B>";

htmlForm += "</TD>";

htmlForm += "<TD><p>";

htmlForm += "<INPUT type=\""

+ "text\""

+ " name=\""

+ "ORQTY\""

+ " size=\""

+ "13\""

+ " value="

+ param2_alnrqty

+ " disabled=\""

+ "true\""

+ " style=\""

+ "font: bolder 10pt Verdana; color: #23238e; border: Black 1 Solid; background-color: #FFFFCC;\""

+ ">";

htmlForm += "</TD>";

htmlForm += "</TR>";

htmlForm += "</table>";

htmlForm += "<INPUT type=\""

+ "hidden\""

+ " name=\""

+ "cartonNo\""

+ " size=\""

+ "10\""

+ " value=\""

+ cartonNo

+ "\""

+ " style=\""

+ "font: bolder 10pt Verdana; color: #23238e; border: Black 1 Solid; background-color: #FFFFCC;\""

+ ">";

htmlForm += "<hr>";

htmlForm += "<table width=100% border=1 align=\""

+ "center\""

+ " bgcolor=\""

+ "#CCCCFF\""

+ ">";

htmlForm += "<TR>";

htmlForm += "<TD><B>SNO&nbsp;</B>";

htmlForm += "</TD>";

htmlForm += "<TD><B>Production LotCode&nbsp;</B>";

htmlForm += "</TD>";

htmlForm += "<TD><B>Lot Quantity&nbsp;</B>";

htmlForm += "</TD>";

htmlForm += "</TR>";

htmlForm += "</table>";

htmlForm += "<hr>";

htmlForm += "&nbsp;&nbsp;";

htmlForm += "<CENTER>";

htmlForm

+= "<INPUT type=submit value ='Confirm reversal' name=Select>";

htmlForm += "<INPUT type=reset value=Clear name=Reset>";

htmlForm += "</CENTER>";

htmlForm += "</FORM>";

htmlForm += "<HR>";

htmlBottom = "</BODY>";

htmlBottom += "</HTML>";

htmlPage = htmlHead1 + htmlHead2 + htmlForm + htmlBottom;

PrintWriter outputToBrowser =

new PrintWriter(response.getOutputStream());

response.setContentType("text/html");

outputToBrowser.println(htmlPage);

outputToBrowser.close();

} catch (Exception e) {

e.printStackTrace();

errmsg = "An exception occured during the display of carton details";

errorMsg(errmsg, request, response);

}

} else if (chk2 == 0) {

errorMsg(errmsg, request, response);

}

} else if (chk1 == 0) {

errorMsg(errmsg, request, response);

}

}

//----

---

// -

---

public int callProcedure(

String cartonNo,

String userId,

HttpServletRequest request,

HttpServletResponse response) {

int chk7 = 0;

String errflg = "";

String errmsg = "";

try {

CallableStatement cs1;

cs1 =

(CallableStatement) dbConnection.prepareNativeCall(

"begin PROJ_OP_CPSREVCALL(?,?,?,?); end;");

cs1.setString(1, cartonNo);

cs1.setString(2, userId);

cs1.registerOutParameter(3, Types.VARCHAR);

cs1.setString(3, errflg);

cs1.registerOutParameter(4, Types.VARCHAR);

cs1.setString(4, errmsg);

cs1.execute();

errflg = cs1.getString(3);

errmsg = cs1.getString(4);

if (errflg.equals("1")) {

errorMsg(errmsg, request, response);

}

if (errflg.equals("0")) {

chk7 = 1;

confirmationPage(cartonNo, request, response);

}

errflg = "0";

errmsg = " ";

cs1.close();

dbConnection.close();

} catch (Exception e1) {

e1.printStackTrace();

errmsg = "An exception occured in calling the stored procedure";

errorMsg(errmsg, request, response);

}

return chk7;

}

// -

---

public void confirmationPage(

String cartonNo,

HttpServletRequest request,

HttpServletResponse response)

throws SQLException, IOException {

try {

String htmlPage = "<html><head><title>Confirmation Page</title>";

htmlPage += "</HEAD>";

htmlPage += "<body bgcolor=#FFFFCC>";

htmlPage += "<table width=100% border=0>";

htmlPage += "<tr>";

htmlPage += "<th bgcolor=blue><font color=white>Reverse Carton confirmation</font></th>";

htmlPage += "</tr>";

htmlPage += "</table>";

htmlPage += "<center><h3>Success</h3></center><hr>";

htmlPage += "<b>";

htmlPage += "Carton number ";

htmlPage += cartonNo;

htmlPage += " was Reversed successfully.";

htmlPage += "</b>";

htmlPage += "<hr>";

htmlPage

+= "<center><a href=/EOP/SelectCarton.jsp?opMode=CARTONREVERSE>Return to Selection Screen</a> ";

htmlPage += "</center></body></html>";

PrintWriter outputToBrowser =

new PrintWriter(response.getOutputStream());

response.setContentType("text/html");

outputToBrowser.println(htmlPage);

outputToBrowser.close();

} catch (Exception e) {

cleanUp();

e.printStackTrace();

}

}

// -

---

public void errorMsg(String errmsg,

HttpServletRequest request,

HttpServletResponse response) {

String lp, gp, ds;

try {

String htmlPage = "<html><head><title>Error Page</title>";

htmlPage += "</HEAD>";

htmlPage += "<body bgcolor=#FFFFCC>";

htmlPage += "<table width=100% border=0>";

htmlPage += "<tr>";

htmlPage += "<th bgcolor=blue><font color=white>Reverse Carton confirmation</font></th>";

htmlPage += "</tr>";

htmlPage += "</table>";

htmlPage += "<center><h3>ERROR</h3></center><hr>";

htmlPage += "<b>";

htmlPage += "Carton was not Reversed since:<br><br> ";

htmlPage += errmsg;

htmlPage += "</b>";

htmlPage += "<hr>";

htmlPage

+= "<center><a href=/EOP/SelectCarton.jsp?opMode=CARTONREVERSE>Return to Selection Screen</a> ";

htmlPage += "</center></body></html>";

PrintWriter outputToBrowser =

new PrintWriter(response.getOutputStream());

response.setContentType("text/html");

outputToBrowser.println(htmlPage);

outputToBrowser.close();

} catch (Exception e) {

cleanUp();

e.printStackTrace();

}

}

// -

---

public void cleanUp() {

dbConnection.close();

}

// -

---

public void destroy() {

cleanUp();

}

}

Thanks for your help

Sundeep

[Former Member · Accepted solution]

Hello

first of all you should not override the service method - see [http://java.sun.com/j2ee/1.4/docs/api/javax/servlet/http/HttpServlet.html#service(javax.servlet.http.HttpServletRequest,%20javax.servlet.http.HttpServletResponse)]

either use doGet(...) or doPost(...)

and your problem is, your are always opening a connection when the service method is called, but your connection is not a local variable, but an instance variable

the service method of your servlet can be called from different threads - so what happens -

Thread a starts service -> opens connection and sets the connection variable (instance, so the same for all threads)

Thread b interrupts thread a -> also opens a connection and replaces your connection variable

then thread a has a different connection var -> big big problem

to solve the problem you have to make your service method reentrand (thread save)

you can do this by moving the connection variable inside your service method (so every thread executing your service method has it`s own connection var)

regards franz

[Former Member · Accepted solution]

Many Thanks Franz,

I was re-checking the code, thought that might be the reason and was updating when your reply confirmed it to me. Thanks for the suggestion.

Cheers,

Sundeep

[Former Member]

To add to the information we use WAS 7.0, Oracle as the database. We are using NWDS for development