• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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
 
I think I'll just lie down here for a second. And ponder this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic