Thread
#exposed
    d

    Das135

    1 year ago
    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.
    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

    tapac

    1 year ago
    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
    2. How to store NULL value to 
    varbinary(max)
     MSSQL column with Exposed?  I have this column type:
    Can you share a more details?
    d

    Das135

    1 year ago
    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.
    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 🙂
    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?
    Moved this conversation into issues on Github:Sequences -> https://github.com/JetBrains/Exposed/issues/1164 Binary type -> https://github.com/JetBrains/Exposed/issues/1165