Hello. I have two questions: 1. *Does Exposed supo...

# exposed

[Das135]

Hello.
I have two questions:
1. Does Exposed suport sequence autoincrement insert for MSSQL?
I have column in Table defined like this:

val id: Column<Long> = long("id").autoIncrement("users_seq")

override val primaryKey: PrimaryKey = PrimaryKey(id)

And when inserting object (without defined inc column) I get this error:
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into column 'id', table ''; column does not allow nulls. INSERT fails.



2. How to store NULL value to varbinary(max) MSSQL column with Exposed? I have this column type:

val image = binary("image", Int.MAX_VALUE).nullable()

When trying to insert null value, I get error:
Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

[Das135]

Autoincrement:
I don't even see id column in insert statement:
INSERT INTO users (created_at, deleted_at, email) VALUES (?,?,?)

I figured that exposed probably doesn't support autoInc for MSSQL. Is it possible to use next val statement when inserting row?
Something like:
INSERT INTO users (id, created_at, deleted_at, email) VALUES (NEXT VALUE FOR seq_name,?,?,?)

When I try to define my own sequence val seq = Sequence("seq_name") and use its .nextLongVal(), I get an error Unexpected value of type Long: NEXT VALUE FOR seq_name of org.jetbrains.exposed.sql.NextVal.LongNextVal

[tapac]

Autoincrement is made on a database side so it shoud not be included into insert statement.

More about the sequences:
https://github.com/JetBrains/Exposed/wiki/DSL#sequence

[tapac]

2. How to store NULL value to varbinary(max) MSSQL column with Exposed?  I have this column type:

Can you share a more details?

[Das135]

Hi @tapac! Thank you for your response.
1. There are different solutions per DBMS how they use autoincrement, for example:
Oracle -> uses seq_name.NEXTVAL
MSSQL -> uses NEXT VALUE FOR seq_name
• why autoincrement is implemented is for Oracle database? See OracleDialect.kt there
• As I mentioned, another solution is to use exposed Sequence() and its .nextLongVal() in insert statement, but I got this type error: Unexpected value of type Long: NEXT VALUE FOR seq_name of org.jetbrains.exposed.sql.NextVal.LongNextVal
2.
I have table defined like this:

object MyTable : Table("table_name") {
    val id: Column<Long> = long("id").autoIncrement("seq_name")
    val binaryColumn: Column<ByteArray?> = binary("binary_column", Int.MAX_VALUE).nullable()
}

When I try to insert row without defining binaryColumn

MyTable.insert {
    it[id] = 1
}

// Or null value in binaryColumn
MyTable.insert {
        it[id] = 1
        it[binaryColumn] = null
}

SQL statement fails with this error: Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

[Das135]

Possible workaround for point 1 is to call custom query SELECT NEXT VALUE FOR seq_name 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 🙂

[Das135]

Point 2:
I found out that Exposed executes this command during insert:

INSERT INTO table_name (id, binary_column) VALUES (@P0, @P1) select SCOPE_IDENTITY() AS GENERATED_KEYS

And all arguments except bigint and datetime are defined as `nvarchar(4000)`:

@P0 bigint, @P1 nvarchar(4000)

This is probably because I am using ByteArray instead of ExposedBlob. Please, what is difference there?

[Das135]

Moved this conversation into issues on Github:
Sequences -> https://github.com/JetBrains/Exposed/issues/1164
Binary type -> https://github.com/JetBrains/Exposed/issues/1165