Gorm: MSSQL Primary Key Related

[iMaxopoly]

The primary key column is defined in the struct as:

Col_nUserNo int gorm:"column:nEMID;primary_key"

And the following is my save function:

func Save(firstname string, lastname string, username string, password string, email string, ip string, gender int, token string, time string) (err error) {
    user := Account{
        Col_sFirstName: firstname,
        Col_sLastName: lastname,
        Col_sUserID: username,
        Col_sUserPW: password,
        Col_sUserName: username,
        Col_sEmail: email,
        Col_sUserIP: ip,
        Col_sGender: gender,
        Col_sLastToken: token,
        Col_sLastSeen: time,
    }
    db.NewRecord(user)
    if query := db.Create(&user); query.Error != nil {
        return query.Error
    }
    return nil
}

The Column nEMID is defined as the following in MSSQL table:

[nEMID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,

And I get the following error:

mssql: Explicit value must be specified for identity column in table 'tAccounts' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

---

My observation has been that when the above 'save' function runs, this query is executed first when it is not needed since I'm not supplying the primary key explicitly:

SET IDENTITY_INSERT tAccounts ON

Kindly look into it.

[euforia]

I have a patch for this if you'd like to check it out- https://github.com/euforia/gorm/commit/b65a7a29ad9219e1be4fb23c16b9e2b0af5d83a1#diff-fb0ef55974bb35e6e1da71205071fd31R15

Basically adding this to the conditional in the line referenced above - && !scope.PrimaryKeyZero()

[dipbhi]

As a workaround I have added below statement in my init function

gorm.DefaultCallback.Create().Remove("mssql:set_identity_insert")

[chenwee]

@dipbhi thank you, helps me a lot

[ryanwalls]

@dipbhi What does your command/workaround do? Mind describing the effect?

[euforia]

It removes the set identity params from the insert sql statement. Its on by default in gorm

[ryanwalls]

@euforia @dipbhi Calling gorm.DefaultCallback.Create().Remove("mssql:set_identity_insert") in my init() function only works for the first insert. Subsequent inserts fail. Do I need to call that Remove function on every insert?

[dipbhi]

I think it's each time after you call Open. I don't have any issue with
multiple inserts afterwards.

On Mon, Nov 21, 2016 at 12:23 PM, ryanwalls [email protected]
wrote:

@euforia https://github.com/euforia @dipbhi https://github.com/dipbhi
Calling gorm.DefaultCallback.Create().Remove("mssql:set_identity_insert")
in my init() function only works for the first insert. Subsequent inserts
fail. Do I need to call that Remove function on every insert?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/jinzhu/gorm/issues/941#issuecomment-261858072, or mute
the thread
https://github.com/notifications/unsubscribe-auth/ALu0yHQpyap5vZh3hPxegiziJGMMPWIcks5rAT_tgaJpZM4H9G0V
.

[jinzhu]

We should fixed all issues for mssql, if you still have any problem, please create a new issue.

Thank you.