This discussion is archived
5 Replies Latest reply: Jan 24, 2013 7:06 AM by 986937 RSS

Using sequences in timesten

981524 Newbie
Currently Being Moderated
Hi

I spent day and cannot get way how to insert increment id number to table...
Could you provide me with any suitable way?

For example:
I have table:
create table VIC.CHAT_MESSAGES_T (
CM_ID NUMBER(20) NOT NULL,
CM_TEXT NVARCHAR2(2000) INLINE,
primary key (CM_ID));

and sequence:
create sequence VIC.CHAT_MESSAGES_S
increment by 1
minvalue 1
maxvalue 9223372036854775807
start with 1
cache 20;

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
  • 1. Re: Using sequences in timesten
    ChrisJenkins Guru
    Currently Being Moderated
    There are really only 3 ways to dop this with TimesTen:

    1. INSERT INTO CHAT_MESSAGES_T VALUES ( CHAT_MESSAGES_S.nextval, 'some text');

    2. Application retrieves sequence nextval (SELECT CHAT_MESSAGES_S.necxtval FROM DUAL) and then uses the value in the INSERT.

    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 think those are really your only choices...

    Chris

    Edited by: ChrisJenkins on Dec 25, 2012 10:09 AM
  • 2. Re: Using sequences in timesten
    981524 Newbie
    Currently Being Moderated
    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.
  • 3. Re: Using sequences in timesten
    598001 Newbie
    Currently Being Moderated
    But, is it possible to get/reserve 2000 new IDs by one SELECT query to TT ?
    You can get one sequence value and use it to generate 1000 key values;

    key=get_next_seq_value()
    for (int i=0;i<1000;i++) {
    insert_new_row(key*1000+i,"some_text");
    }

    Edited by: Vladimir Romanov on 27.12.2012 14:23
  • 4. Re: Using sequences in timesten
    981524 Newbie
    Currently Being Moderated
    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.
  • 5. Re: Using sequences in timesten
    986937 Newbie
    Currently Being Moderated
    Hello Chris!

    Maybe you can help me with another problem with sequences in TT.

    You wrote:
    2. Application retrieves sequence nextval (SELECT CHAT_MESSAGES_S.necxtval FROM DUAL) and then uses the value in the INSERT.

    That's the way I'm trying to use sequences.
    C++ program. Using TTClasses. Command executes normally (SQL_SUCCESS), but column that supposed to contain new sequence value is NULL ...

    UPDATE:
    Oh gosh! My bad ...
    Due to mistype, FetchNext method didn't get executed ...
    Problem solved.

    Edited by: 983934 on Jan 24, 2013 7:05 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points