I spent day and cannot get way how to insert increment id number to table...
Could you provide me with any suitable way?
I have table:
create table VIC.CHAT_MESSAGES_T (
CM_ID NUMBER(20) NOT NULL,
CM_TEXT NVARCHAR2(2000) INLINE,
primary key (CM_ID));
create sequence VIC.CHAT_MESSAGES_S
increment by 1
start with 1
I need to use inserts, like:
INSERT INTO "VIC"."CHAT_MESSAGES_T" (CM_TEXT) VALUES (' any chat message');
Field CM_ID should populate automatically with unique ID number from sequence VIC.CHAT_MESSAGES_S
1) TT doesnt support triggers, so I cannot do it via trigger
2) I cannot use CHAT_MESSAGES_S.nextval in insert becouse I don't use neative SQL and some part of aplication use hibernate, TT JDBC driver doesn't support hibernate.jdbc.use_get_generated_keys so it is not possible to use generator class="sequence-identity"
3) TT same as Oracle DB doesnt support table creation with default CHAT_MESSAGES_S.nextval construction
I am new in TT and may be I am stupid... but I don't see way how to solve it :( help me please with this simple question
Hi, thanks for answer
1)" INSERT INTO CHAT_MESSAGES_T VALUES ( CHAT_MESSAGES_S.nextval, 'some text');"
I cannot use this, jdbc driver doesn't support it. (Only native query possible)
2)Application retrieves sequence nextval (SELECT CHAT_MESSAGES_S.necxtval FROM DUAL) and then uses the value in the INSERT
Application can insert up to 2000 records/sec, may be more... so in this case I should make up to 4000 requests to server, I think it is not optimal way.
But, is it possible to get/reserve 2000 new IDs by one SELECT query to TT ?
3) You write a PL/SQL procedure that takes the message text as an input param,eter and inside the procedure you retrieve the sequence valeu and use it to do the insert.
I want to make EJB application, which can work with a lot different databases via hibernate mapping, this case need to write different DB procedures for each database server- not good too.
Thank you Vladimir,
Yes your idea can solve it. But in any case I should know how many maximal quantity of records should be saved and I should write special code specially for TT DB... not good too.
I hope next versions of jdbc driver of TT will have necessary functional, because current TT actually cannot work with Hibernate mapping in simple way.