Announcement

**Isomorphic** · 30 Jun 2020, 12:59

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?

**helmut** · 30 Jun 2020, 21:55

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

**Isomorphic** · 2 Jul 2020, 13:34

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.

**helmut** · 2 Jul 2020, 23:20

That's right, looking forward to the mail.

Announcement

Sql Server 2012+ and Sequences

Comment

Comment

Comment

Comment