Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Insert a new row and get the auto generated id in the same call #487

Open
ndjordjevic opened this issue Jul 3, 2019 · 13 comments
Open

Insert a new row and get the auto generated id in the same call #487

ndjordjevic opened this issue Jul 3, 2019 · 13 comments

Comments

@ndjordjevic
Copy link

Hi,

Can someone post an example how to do this?

@chris-rossi
Copy link
Contributor

I can work on this.

@ndjordjevic
Copy link
Author

I switched to minus5/gofreetds driver, installed freetds and LastInsertId() was returning the correct last generated id... Then I switched back to denisenkom/go-mssqldb and magically LastInsertId() started to work... Now I'm totally confused ... :) Before I haven't had a freetds installed.. Ok uninstalled freetds and LastInsertId() is still working :) No idea what's going on :)

@chris-rossi
Copy link
Contributor

I have no idea what your issue was, but I'm glad to hear it's working. I'll still work on creating an example for this scenario.

@kardianos
Copy link
Collaborator

Please be aware that there is no 100% correct way with SQL server to return a LastInsertID without modifying the SQL and returning it in the select statement.

If you are using SQL Server DO NOT USE OR RELY on LastInsertID.

You should use something like:

insert into mytable (mytextcol)
ouput inserted.ID
values (N'myval');

@ndjordjevic
Copy link
Author

ndjordjevic commented Jul 3, 2019

Alright I'll explain what i'm doing

  1. First I used sql.DB.Exec() to insert the row and tried to extract the generated id with a result. LastInsertId(). That doesn't work as stated at https://github.com/denisenkom/go-mssqldb#important-notes
  2. Then I tried what is suggested there by adding SELECT ID = convert(bigint, SCOPE_IDENTITY()); along with the INSERT in Exec(). It can't be done like that...
  3. Then I switched to DB.QueryRow() and passed both INSERT and SELECT ID =... to it and then Scan()... It works but if there is an error while Insertion that error will be hidden ...
  4. Then I switched to sql.Tx.Exec() and magically LastInsertId() started to return an id

@kardianos
Copy link
Collaborator

Not magical, each connection is a unique session. calling db.Conn(), then calling exec then scope_identity on the same connection is what you want to do. Making a Tx is another way to do that.

@ndjordjevic
Copy link
Author

Please be aware that there is no 100% correct way with SQL server to return a LastInsertID without modifying the SQL and returning it in the select statement.

If you are using SQL Server DO NOT USE OR RELY on LastInsertID.

You should use something like:

insert into mytable (mytextcol)
ouput inserted.ID
values (N'myval');

I see but how to execute this? With a db.Exec or with something else? db.Exec doesn't work.

@kardianos
Copy link
Collaborator

There is no such thing as "exec" in SQL Server. Exec for the DB interface just means disregard all rows.
Don't use Exec. Use Query. Scan the first row to get output.

@scbizu
Copy link

scbizu commented Oct 10, 2019

The error will also be hidden if I replace the SCOPE_IDENTITY() solution with OUTPUT clause. However, when I try to insert some duplicate rows to a table:

  • SCOPE_IDENITY() will return the driver type conversion error between null and int64
  • OUTPUT clause do not return any errors

They both do not actually insert the duplicate rows into the table .

Any suggestion about how to capture the raw database error when insert duplicated rows ?

@kardianos

@yukiwongky
Copy link
Contributor

@scbizu What is your table schema? What is the query you're running? Why can't duplicate rows be insert into the table (I'm guessing there is a unique column in your table)?

I tried both using SCOPE_IDENTITY() and OUTPUT I was able to get the correct error (mssql: Violation of UNIQUE KEY constraint 'UQ__foo__DE90ECF660F04C30'. Cannot insert duplicate key in object 'dbo.foo'. The duplicate key value is (1).).

The following is the queries I tried to run:

rows, err := db.Query("insert into foo (baz) values (1); select ID = convert(bigint, SCOPE_IDENTITY())")

and

err = db.QueryRow("insert into foo (baz) output inserted.bar values (1)").Scan(&lastInsertId2)

where baz is a unique column.

@scbizu
Copy link

scbizu commented Oct 11, 2019

@v-kaywon Are you sure the first case error is the duplicated error ? I got the conversion error even if I use the db.Query() (or db.QueryRow()), it says sql: Scan error on column index 0, name \"ID\": converting driver.Value type <nil> (\"<nil>\") to a int64: invalid syntax. The code looks likes below:

rows, err := db.Query("MY INSERT QUERY; select ID = convert(bigint, SCOPE_IDENTITY())")
if err != nil {
  return err
}
defer rows.Close()

if !rows.Next() {
  if err:= rows.Err();err!=nil{
    return err
  }
  return INSERTID_NOT_FOUND_ERROR
}

var id int64

err := rows.Scan(&id); err!=nil {
   return err
}

As for output clause, I fixed up my scan bug , And now it works as expected.

Anyway, Thanks for your help.

@yukiwongky
Copy link
Contributor

I doubled checked and tried again with the query "insert into foo (baz) values (1); select ID = convert(bigint, SCOPE_IDENTITY())" and I do get the violation of unique key constraint error. I'm not sure why it's not working for you.. Anyway I'm glad the output query works for you.

@Fs02
Copy link

Fs02 commented Mar 5, 2021

Hi, I'm also having this problem, and able to reproduce using this code:

code modified from: #23 (comment)

func TestDriver(t *testing.T) {
	db, err := sql.Open("mssql", dsn())
	fmt.Println(db, err)
	fmt.Println(db.Ping())
	defer db.Close()
	schema := "CREATE TABLE [dbo].[Foo3]([ID] [int] IDENTITY(1,1) NOT NULL,[Name] [varchar](100) NOT NULL,CONSTRAINT [PK_Foo3] PRIMARY KEY CLUSTERED ([ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],CONSTRAINT [IX__Name] UNIQUE NONCLUSTERED ([Name] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]"
	_, err = db.Exec(schema)
	fmt.Println(err)

	// first record, return nil is expected
	_, err = db.Query("INSERT INTO Foo3([Name]) "+
		"VALUES (?)", "foo")
	fmt.Println("first record: ", err) //nil

	// duplicate record, error returned
	_, err = db.Query("INSERT INTO Foo3([Name]) "+
		"VALUES (?)", "foo")
	fmt.Println("duplicate record no id returned: ", err) // mssql: Violation of UNIQUE KEY constraint

	// duplicate record with output inserted, no error returned???
	_, err = db.Query("INSERT INTO Foo3([Name]) OUTPUT INSERTED.ID "+
		"VALUES (?)", "foo")
	fmt.Println("duplicate with inserted: ", err) // nil??

	// duplicate record with output inserted, no error returned???
	_, err = db.Query("INSERT INTO Foo3([Name]) "+
		"VALUES (?); select ID = convert(bigint, SCOPE_IDENTITY())", "foo")
	fmt.Println("duplicate with select id: ", err) // nil??

	// works: error returned
	_, err = db.Query("INSERT INTO Foo3([Name]) OUTPUT INSERTED.ID " +
		"VALUES ('foo')")
	fmt.Println("duplicate with inserted, literal only: ", err) //Real error!

	// duplicate record with output inserted, no error returned???
	_, err = db.Exec("INSERT INTO Foo3([Name]) OUTPUT INSERTED.ID "+
		"VALUES (?)", "foo")
	fmt.Println("duplicate with inserted using exec: ", err) // nil??

	// duplicate record with output inserted, no error returned???
	_, err = db.Exec("INSERT INTO Foo3([Name]) "+
		"VALUES (?); select ID = convert(bigint, SCOPE_IDENTITY())", "foo")
	fmt.Println("duplicate with select id using exec: ", err) // nil??

}

output:

=== RUN   TestDriver
&{0 {sqlserver://sa:REL2021-mssql@localhost:1433?database=master&log=32 0x17ff040} 0 {0 0} [] map[] 0 0 0xc00008e480 0xc00015cf00 false map[] map[] 0 0 0 <nil> 0 0 0 0x10dcfa0} <nil>
<nil>
<nil>
first record:  <nil>
duplicate record no id returned:  mssql: Violation of UNIQUE KEY constraint 'IX__Name'. Cannot insert duplicate key in object 'dbo.Foo3'. The duplicate key value is (foo).
duplicate with inserted:  <nil>
duplicate with select id:  <nil>
duplicate with inserted, literal only:  <nil>
duplicate with inserted using exec:  mssql: Violation of UNIQUE KEY constraint 'IX__Name'. Cannot insert duplicate key in object 'dbo.Foo3'. The duplicate key value is (foo).
duplicate with select id using exec:  mssql: Violation of UNIQUE KEY constraint 'IX__Name'. Cannot insert duplicate key in object 'dbo.Foo3'. The duplicate key value is (foo).
--- PASS: TestDriver (0.19s)

it seems db.Query can't detect if constraint error is thrown?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

6 participants