Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC DB/2 statement delimiter problem

 
Julio Lopez
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Any help on this matter would be greatly appreciated. I am trying to write a shopping cart application. I have a DB/2 SQL statement that will check to see if a certain item is in the Shopping Cart table, if it is it increments the quantity by one, if it is not it adds the item and sets the quantity to 1. I know this SQL works properly because I have run it in the command line.
BEGIN ATOMIC
FOR ROW AS
SELECT COUNT(*) AS NUMBEROFROWS FROM THELIB.SCL0P WHERE SCLENTTYP = ? AND SCLENTID = ? AND SCLENTBUSTYP = ? AND SCLUSRID = ? AND SCLITMCDE = ?
DO IF (NUMBEROFROWS = 0) THEN INSERT INTO THELIB.SCL0P (SCLENTTYP, SCLENTID, SCLENTBUSTYP, SCLUSRID, SCLITMCDE, SCLITMQTY) VALUES (?, ?, ?, ?, ?, 1) ;
ELSE UPDATE THELIB.SCL0P SET SCLITMQTY = SCLITMQTY + 1 WHERE SCLENTTYP = ? AND SCLENTID = ? AND SCLENTBUSTYP = ? AND SCLUSRID = ? AND SCLITMCDE = ? ;
END IF ;
END FOR ;
END
The issue I am running into is JDBC in nature. It seems that the ; within the statement are not being passed to the DB/2 server or the ; is not being recognized as a statement delimiter. If the former is the problem is there any way to escape the ; so it does not get parsed out? If it is the latter is there a way to tell DB/2 within the SQL statement that the ; is a statement delimiter? Here is the pertinent Java code.
// The statement must reference the connection to be used
prepStatement = connection.prepareStatement(getSQLString());
// Create placeholders for the parameters
prepStatement.setString(1, entityType);
prepStatement.setString(2, entityId);
prepStatement.setString(3, entityBusinessType);
prepStatement.setString(4, userId);
prepStatement.setString(5, itemNumber);
prepStatement.setString(6, entityType);
prepStatement.setString(7, entityId);
prepStatement.setString(8, entityBusinessType);
prepStatement.setString(9, userId);
prepStatement.setString(10, itemNumber);
prepStatement.setString(11, entityType);
prepStatement.setString(12, entityId);
prepStatement.setString(13, entityBusinessType);
prepStatement.setString(14, userId);
prepStatement.setString(15, itemNumber);
// Try to perform the update
try {
// Execute the SQL statement
prepStatement.execute();
}
catch (SQLException e) {
e.printStackTrace();
throw e;
}
And here is the error I am getting back.
[01.06.04 23:06:03:247 CDT] 818b8b35 WebGroup A SRVE0091I: [Servlet LOG]: "eMDS.CartAddItem: [eMDS.CartAddItem-error][IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "END-OF-STATEMENT" was found following "S (?, ?, ?, ?, ?, 1)". Expected tokens may include: "<delim_semicolon>". LINE NUMBER=1. SQLSTATE=42601
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "END-OF-STATEMENT" was found following "S (?, ?, ?, ?, ?, 1)". Expected tokens may include: "<delim_semicolon>". LINE NUMBER=1. SQLSTATE=42601
If anybody has run into something like this or knows what I am doing wrong I would greatly appreciate the help.

Thank you,
Julio Lopez
M-Group Systems
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic