aspose file tools*
The moose likes JDBC and the fly likes problem with INSERT query with RETURNING clause Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "problem with INSERT query with RETURNING clause" Watch "problem with INSERT query with RETURNING clause" New topic
Author

problem with INSERT query with RETURNING clause

maya fur
Greenhorn

Joined: Sep 08, 2005
Posts: 11
Hi,
I'm try to run the following query using JDBC.
"DECLARE Id number(9);BEGIN INSERT INTO CPS1_MY_EWALLET ( EWALLET_ID) VALUES (CPS1_MY_EWALLET_1SQ.NEXTVAL) RETURNING EWALLET_ID INTO Id; END;"

1.How can I retrieve the Id that was filled?
2.Maybe this is an incorrect way, is there any idea how I can return a value from an INSERT query?

I use the following code:
PreparedStatement s = con.prepareStatement(query);
ResultSet rs=s.executeQuery();

but any method I try to activate on the ResultSet fails (getMetaData, getInt, getLong etc...)

Thanks
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333

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.
maya fur
Greenhorn

Joined: Sep 08, 2005
Posts: 11
Hi,
Thanks for the reply.
I'll try the first option.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1135

Maya,
According to the Oracle JDBC FAQ, this is supported in Oracle 10g. However, I usually wrap the DML in a [PL/SQL] stored function like so:

Then in your JDBC code, use a "CallableStatement":
[NOTE: Uncompiled and untested!]

I'm only going from memory, so check the documentation to make sure.

Good Luck,
Avi.
[ March 06, 2006: Message edited by: Avi Abrami ]
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by Avi Abrami:
Maya,
According to the Oracle JDBC FAQ, this is supported in Oracle 10g.


Wow. Learn something new every day, I do....

Looks like you have to use the 10gR2 JDBC driver to get this functionality, and also make use of some Oracle extensions to JDBC in your Java code.

Oracle's online documentation for this feature is here:
http://download-west.oracle.com/docs/cd/B19306_01/java.102/b14355/oraint.htm#BABJJDDA
(free registration required, I think)

Please let us know if you try it and it works...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: problem with INSERT query with RETURNING clause