• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

JDBC DB/2 statement delimiter problem

 
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Politics is a circus designed to distract you from what is really going on. So is this tiny ad:
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic