Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    Sql Server 2012+ and Sequences

    Hello Smartwt team,

    currently our application is supporting mysql and oracle, now a new customer also need sqlserver 2012+ support. Currently we use a hybrid solution (hibernate some backend services) / (smartgwt frontend services) within our application. In our first tests hibernate uses sequences for primary keys for sqlserver version 2012+ but for me it seems that smartgwt uses the older IDENTITY variant for sequence datasource fields.

    Now here comes my question: For oracle sequence support is active out of the box, so is it possible also to activate this for mssql? And when yes, how? I no support is currently available, is is possible to enhance smartgwt in this direction?

    Thank you very much!

    #2
    If SmartGWT needs an enhancement here, it can be done via the Feature Sponsorship program at little cost, however, we're not following what the issue is here:

    1. what version are you testing with?

    2. have you tested with the latest version and found the same issue?

    3. are you using our server framework?

    4. are you using our built-in Hibernate connector or using your own code with Hibernate?

    5. how does our approach for sequences become relevant, if you're using Hibernate? Are you saying you're trying to write to a Hibernate-generated table using our SQLDataSource?

    6. what specifically goes wrong? Can you share logs?

    Comment


      #3
      Hi Isomorphic,

      thank you for your response, let me put the record straight first and give you a short overview.
      Our very large application is logically divided into two parts, firstly a smartgwt based data handling part where a user can create, edit and delete database entries. This records are used for controlling a purely hibernate based engine which generates a large amount of data in other tables over time.
      Normally backend tables are managed and filled with data by hibernate and data in frontend tables only are created by smartgwt framework, but for some tables we have the situation that both systems can insert data records.

      For ORACLE smartgwt uses Database SEQUENCES for autoincrement primary keys, so the oracle tables are created as follows

      Code:
      CREATE TABLE GROUPING_SCHEME (
          ID NUMBER(20) NOT NULL,
          DESCRIPTION VARCHAR2(50 CHAR) NOT NULL
      );
      CREATE SEQUENCE GROUPING_SCHEME_ID
        INCREMENT BY 1
        START WITH 1
        MAXVALUE 999999999999999999999999
        MINVALUE 1
        NOCYCLE
        CACHE 100
        ORDER;
      For sqlserver since version 2012 two approaches for autoincement primary keys are available, the SEQUENCE variant and the IDENTITY codeword.

      In our tests we have seen, that smartgwt needs the IDENTITY variant for autoincrement primary keys, here comes an example:
      Code:
      CREATE TABLE GROUPING_SCHEME (
          id          BIGINT       NOT NULL IDENTITY,
          description NVARCHAR(50) NOT NULL,
          CONSTRAINT PK_GROUPING_SCHEME_ID PRIMARY KEY (id)
      );


      My question was, is it possible to configure smartgwt to use SEQUENCES in mssqlserver (like oracle) insteed IDENTITY, because SEQUENCES have some major performance advandages when you make mass inserts,
      This allows Hibernate to use JDBC batching and other optimization strategies that require the delayed execution of SQL INSERT statements, which is not possible with IDENTITY tables.
      Currently we have to migrate all tables to the IDENTITY approach and have to force hibernate to use the worse IDENTITY approach.

      In summary we would like to create the tables as follows:
      Code:
      CREATE TABLE GROUPING_SCHEME
      (
          id          BIGINT       NOT NULL,
          description NVARCHAR(50) NOT NULL,
          CONSTRAINT PK_GROUPING_SCHEME_ID PRIMARY KEY (id)
      );
      CREATE SEQUENCE GROUPING_SCHEME_ID INCREMENT BY 1 START WITH 1 NO MAXVALUE MINVALUE 1 NO CYCLE CACHE 100;

      Here are our answers to the applicable questions
      1) currently we are using 12.1p-20200614
      2) not a bug
      3) yes (mixed with hibernate)
      4) we are not using any hibernate connector
      5) see above
      6) When using the SEQUENCE variant, the smartgwt server framework doesn't emit the necessary NEXT VALUE FOR statement and tries to insert a null value for the primary key

      I hope that can explain the reason for our request a little more clearly

      Comment


        #4
        OK, so to sum up, you have Hibernate-generated tables and you are trying to access them via our SQLDataSource.

        We can definitely add this support for you via our Feature Sponsorship program. We'll contact you with an estimate shortly.

        Comment


          #5
          That's right, looking forward to the mail.

          Comment

          Working...
          X