cancel
Showing results for 
Search instead for 
Did you mean: 

how to get seq.nextval as a result of an insert statement?

Former Member
0 Kudos

Hi,

on Oracle using sequences we can use follow command to issue an insert command and get the nextval used on this insert:

CALL INSERT INTO "TEST2550_ENTITY" ("ID","NAME") VALUES ("TEST2550_ENTITY_SEQ".nextval,?) RETURNING "ID" INTO ?

there is a way to do same thing in MaxDB using JDBC?

thanks for any tip

Clóvis

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Hi,

In MaxDB I would split it up into two statements.

SELECT SEQUENCE.NEXTVAL from dual
INSERT INTO "TEST2550_ENTITY" ("ID","NAME") VALUES (ID,?)

But you could also write a dbprocedure that encapsulate these two statements and returns the sequence number.

Regards,

Marco

Former Member
0 Kudos

In my opinion all of these solutions would cause a massive development overhead, which is completely unnecessary for such a simple basic feature. Why should a developer write a DB Procedure for a functionality, which is usually provided by a simple method of the java jdbc API. Despite of the fact, that not everyone is able to write DB Procedures.

This issue should really be addressed by the MaxDB team for the next release, so that Java programmers can use the methods they are used to.

Kind Regards,

Carl Heckmann

lbreddemann
Active Contributor
0 Kudos

> This issue should really be addressed by the MaxDB team for the next release, so that Java programmers can use the methods they are used to.

There are many things that "should really be addressed by the MaxDB team for the next release"...

But despite common believe development capacities seem to be limited in the real world - so I guess this feature-request requires some strong sponsoring to get the priority to allow a quick implementation.

Anyhow, wishing for/hoping for is never forbidden and sometimes lucky circumstances allow the implementation of nice ideas quite pronto

regards,

Lars

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

Hi Clovis,

yes there is such an option - but not that easy ...

You may want to check the getLastSerial Method (SapDB_Result Class) in the documentation.

[http://maxdb.sap.com/doc/7_6/a6/1ecb347f09a148a76a9a93b6e886fe/content.htm]

With it you can get the last serial value used in your update/insert statement.

Best regards,

Lars

Former Member
0 Kudos

Hi, Lars,

i check that but this method exists only for Python, i cant find that method in JDBC classes, nor find any JDBC javadoc to search for something like that.

you know if such method exists in JDBC driver?

best regards

Clóvis

lbreddemann
Active Contributor
0 Kudos

Hi Clovis,

sorry my fault. (I really should stop answering questions in the late evening...).

Well, unfortunately it looks like that this actually not possible vie JDBC.

I tried to use the getGeneratedKeys() Method that usually would give you automatically created IDs, but I only got this message:


Exception in thread "main" com.sap.dbtech.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: Retrieving auto generated keys is not supported.
	at com.sap.dbtech.jdbc.ConnectionItem.throwNotSupported(ConnectionItem.java:101)
	at com.sap.dbtech.jdbc.StatementSapDB.getGeneratedKeys(StatementSapDB.java:1108)
	at com.sap.dbtech.jdbc.trace.Statement.getGeneratedKeys(Statement.java:254)

Maybe one fo the JDBC developers is reading this and knows a better way...

best regards,

Lars

Former Member
0 Kudos

Hi Lars,

what you explain its only for Python i cant find anything about that in Java, i tried to check ResultSetSapDB and StatementSapDB classes, but dont find anything about that, maybe have a javadoc that points for that? or that exists only for Python?

best regards

Clóvis

DISCARD MY BROWSER DONT SHOW THE ABOVE MESSAGES

Edited by: Clovis Wichoski on May 19, 2009 10:26 PM