5 Replies Latest reply: Jan 24, 2013 9:06 AM by 986937 RSS

    Using sequences in timesten

    981524
      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
          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
            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
              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
                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
                  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