Support sequences in SQL Server

[FidasL]

I ran into an issue when inserting row into table with SQL Server.
My table definition:

object Person : Table("persons") {
    val id: Column<Long> = long("id").autoIncrement("persons_seq")
    override val primaryKey: PrimaryKey = PrimaryKey(id)

    val email: Column<String?> = varchar("email", 255).nullable()
}

Sequence persons_seq exists in my db schema.

When I try to insert row without specifying id column, I got an error:
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into column 'id', table 'persons'; column does not allow nulls. INSERT fails.

**Solution using sequence definition: **
I tried to create own sequence as defined there

val sequence = Sequence("persons_seq")

val nextVal = sequence.nextLongVal()
val id = Person.insert{
  it[id] = nextVal
  it[email] = "[email protected]"
}

During this insert, I get an error: Unexpected value of type Long: NEXT VALUE FOR seq_name of org.jetbrains.exposed.sql.NextVal.LongNextVal

Solution using NEXT VALUE FOR
Another solution is to use NEXT VALUE FOR persons_seq in insert statement - similar implementation like uses OracleDialect.kt there.

I think that Exposed should support both solutions, using Sequence() and also NEXT VALUE FOR

Workaround
Possible workaround is to call custom query SELECT NEXT VALUE FOR persons_seq AS 'seq_no' that increments sequence value and returns the old value (in MSSQL) and then assign this value in InsertStatement, but I think this is not a good solution.

fun getIntSeqNumber(seqName: String): Int {
    val seqNumber = arrayListOf<Int>()
    transaction {
        exec("SELECT NEXT VALUE FOR $seqName AS 'seq_no'") { rs ->
            while (rs.next()) {
                seqNumber.add(rs.getInt("seq_no"))
            }
        }
    }
    return seqNumber.first()
}

[dtbullock]

Not to say that Exposed shouldn't have its sights set on covering the supported DBMS's comprehensively, sequences and all, but to solve your problem, is there a reason why the standard identity column is not appropriate in your scenario?

CREATE TABLE Persons (
    ID int IDENTITY(1,1) PRIMARY KEY,  /* should work even if you don't insert a value; corresponds with autoIncrement() */
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

That's idiomatic for SQL Server. I'd only use a sequence if I needed uniqueness across >1 table.

Other qns:

• is your ID column really an SQL server BIGINT? If it's only INT in the DBMS, the corresponding Kotlin datatype will be Int too.

[FidasL]

Changing type from BIGINT to IDENTITY() would be a problem. because other services in our infrastructure are dependent on existing data model.
I understand, that this might not be a best-practice in SQL Server, but we use BIGINT with additional sequence.

As I mentioned, I tried to solve this problem by defining my own Sequence variable, but calling it's .nextLongVal() does not work in SQL Server statement - I think that this is not an expected behaviour.

Another fact is, that .autoincrement(seq_name) definition also works with Oracle database, not only with Postgres. Although I would like to say it is implemented in a little bit strange way, because table can have only one autoInc column see:

• https://github.com/JetBrains/Exposed/blob/master/exposed-core/src/main/kotlin/org/jetbrains/exposed/sql/vendors/OracleDialect.kt#L133
• https://github.com/JetBrains/Exposed/blob/master/exposed-core/src/main/kotlin/org/jetbrains/exposed/sql/Table.kt#L323

---

• is your ID column really an SQL server BIGINT? If it's only INT in the DBMS, the corresponding Kotlin datatype will be Int too.

I made a mistake in getIntSeqNumber() function - It should work with Long type

fun getLongSeqNumber(seqName: String): Long {
    val seqNumber = arrayListOf<Long>()
    transaction {
        exec("SELECT NEXT VALUE FOR $seqName AS 'seq_no'") { rs ->
            while (rs.next()) {
                seqNumber.add(rs.getLong("seq_no"))
            }
        }
    }
    return seqNumber.first()
}

[dtbullock]

I wonder if lines 95 & 96 of ColumnType.kt (Exposed 0.29.1) could be the culprit for the first-use case ColumnType<T>.autoIncrement(String) not working?

    val autoincSeq: String? = _autoincSeq
        get() = if (currentDialect.needsSequenceToAutoInc) field else null

Only the dialect for Oracle overrides the default value of needsSequenceToAutoInce = false . So sequences named in autoIncrement(String) are likely work only for Oracle.

[FidasL]

You are right. Sequences works only with Oracle, but implementation is limited only for one(first) sequence column in defined (see this row).

[Tapac]

I've made changes and now sequences should work find with SQL Server, but please note that SQLServer jdbc driver doesn't support returning generated values for sequences (check the issue) that means it wont be possible to use insertAndGetId and similar functions.