This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
Hi, "DECLARE Id number(9);BEGIN INSERT INTO CPS1_MY_EWALLET ( EWALLET_ID) VALUES (CPS1_MY_EWALLET_1SQ.NEXTVAL) RETURNING EWALLET_ID INTO Id; END;"
Thats not standard SQL; you've mixed in a database-proprietary procedural langauage. The JDBC standard only guarantees support for plain SQL, one statement at a time, although a vendor's driver/DB may allow more... Also, the "RETURNING..." clause is not standard SQL either. I'm guessing that you're using Oracle; if so, the RETURNING clause is only valid in either a PL/SQL environment or when using Oracle's non-JDBC libraries for COBOL, PL/1, or C/C++.
Possibilities: 1. Encapsulate your PL/SQL code in a stored procedure and use a CallableStatement to invoke it. 2. Do it in 2 JDBC calls: a) Get the next value with SELECT CPS1_MY_EWALLET_1SQ.NEXTVAL FROM DUAL b) Insert it into CPS1_MY_EWALLET 3. Do an INSERT without the RETURNING clause and try using Statement.getGeneratedKeys() (if EWALLET_ID is a primary key). I've not used this with Oracle so I won't swear it works.
Joined: Sep 08, 2005
Hi, Thanks for the reply. I'll try the first option.