LastInsertId is not implemented result

[GoogleCodeExporter]

What is the expected output? What do you see instead?
- I would like to get the id of the last inserted row instead of "not 
implemented" message after the insert command

What version of the product are you using? On what operating system?
- latest odbc ver on both Ubuntu and Windows

Please provide any additional information below.

//result.go:
func (r *Result) LastInsertId() (int64, error) {
    // TODO(brainman): implement (*Resilt).LastInsertId
    return 0, errors.New("not implemented")
}

Original issue reported on code.google.com by rzemy...@gmail.com on 26 Feb 2014 at 3:20

[GoogleCodeExporter]

As far as I am aware, there is no ODBC call that provides that information. If 
you know I am wrong about that, please, provide some references. Closing this 
as WontFix until we have some plan.

But, if you know what your database is, you should be able to use a particular 
approach to retrieve that information. For example, I use MS SQL Server, and 
this is how I would do that (you could apply this diff to hg id of 
79ae99114308):

diff --git a/mssql_test.go b/mssql_test.go
--- a/mssql_test.go
+++ b/mssql_test.go
@@ -1218,3 +1218,21 @@
        t.Fatal("comparison fails")
    }
 }
+
+func TestALEX(t *testing.T) {
+   db, sc, err := mssqlConnect()
+   if err != nil {
+       t.Fatal(err)
+   }
+   defer closeDB(t, db, sc, sc)
+
+   db.Exec("drop table dbo.temp")
+   exec(t, db, "create table dbo.temp(id int identity, v int)")
+   var id int64
+   err = db.QueryRow("insert into dbo.temp(v) values(123); select 
scope_identity()").Scan(&id)
+   if err != nil {
+       t.Fatal(err)
+   }
+   t.Logf("id=%v", id)
+   exec(t, db, "drop table dbo.temp")
+}

Alex

Original comment by alex.bra...@gmail.com on 27 Feb 2014 at 1:07

• Changed state: WontFix

[GoogleCodeExporter]

Thank you for your answer.
I am using MS SQL Server as well.

I followed your example, but I am still getting odbc error: "Stmt did not
create a result set"
err ==  "Stmt did not create a result set"
for:
err = db.QueryRow("insert into [mysampletable] values(123); select
scope_identity()").Scan(&id)

Do you have any ideas what went wrong?

Original comment by rzemy...@gmail.com on 27 Feb 2014 at 9:49

[GoogleCodeExporter]

Please, send complete program, so I can try and run it here. Thank you.

Alex

Original comment by alex.bra...@gmail.com on 27 Feb 2014 at 10:13

[GoogleCodeExporter]

Here are my codes:

package main

import (
_ "code.google.com/p/odbc"
"database/sql"
"fmt"
"runtime"
)

func defaultDriver() string {
if runtime.GOOS == "windows" {
return "sql server"
} else {
return "MSSQL"
}
}

func main() {

conn, err := sql.Open("odbc",
fmt.Sprintf("driver=%s;server=************,**;uid=******;pwd=********;database=*
****",
defaultDriver()))

if err != nil {

fmt.Println("Connecting Error", err)

return
}

defer conn.Close()

if err != nil {
fmt.Println("Query Error", err)
return
}

//!!!!!!!!!!!!!!!!!!
// uncomment one of these 4
// !!!!!!!!!!!!!!!!!

//row := conn.QueryRow("select top 1 id from dbo.temp") //works
//row := conn.QueryRow("insert into dbo.temp(v) values(1337);select
@@identity as id") //Stmt did not create a result set
//row := conn.QueryRow("insert into dbo.temp(v) values(1337);select
scope_identity() as id") //Stmt did not create a result set
row := conn.QueryRow("insert into dbo.temp(v) values(1337);select
scope_identity()") //Stmt did not create a result set

var id int

if err := row.Scan(&id); err == nil {
fmt.Println(id)
}

if err != nil {
fmt.Println("Query Error", err)
return
}
fmt.Printf("%s\n", "finish")
return
}

Thanks for your help

2014-02-27 23:13 GMT+01:00 <odbc@googlecode.com>:

Original comment by rzemy...@gmail.com on 28 Feb 2014 at 8:35

[GoogleCodeExporter]

Does it work in your environment? Thank you

Original comment by rzemy...@gmail.com on 3 Mar 2014 at 10:25

[GoogleCodeExporter]

I cannot run your program here - you are using dbo.temp table, that does not 
exist here. Have you tried running my program listed in 
https://code.google.com/p/odbc/issues/detail?id=35#c1 ? Does "go test -v 
-run=TestALEX ..." command PASSes? If not, what is the error message? Thank you.

Alex

Original comment by alex.bra...@gmail.com on 4 Mar 2014 at 1:10

[GoogleCodeExporter]

Thanks for your answer. In both cases I get the same error: Smt did not create 
a result set.

I was testing both ubuntu and windws, on FreeTDS and mssql driver, and both on 
SQL2008 and SQL2012. Unfortunatelly no luck on any of this configurations.

My codes and result of testing yours attached. What sql server are you using?

Original comment by rzemy...@gmail.com on 4 Mar 2014 at 3:13

Attachments:

• main.go
• error.jpg

[GoogleCodeExporter]

My fault. My example works on linux/freetds-0.91/unixODBC-2.3.1, but fails on 
windows.

I google some, and here 
http://discuss.joelonsoftware.com/default.asp?design.4.463520.12 they suggest 
to use "SET NOCOUNT ON". I don't know why, but it does the trick. Please, try 
this again:

diff --git a/mssql_test.go b/mssql_test.go
--- a/mssql_test.go
+++ b/mssql_test.go
@@ -1218,3 +1218,21 @@
        t.Fatal("comparison fails")
    }
 }
+
+func TestALEX(t *testing.T) {
+   db, sc, err := mssqlConnect()
+   if err != nil {
+       t.Fatal(err)
+   }
+   defer closeDB(t, db, sc, sc)
+
+   db.Exec("drop table dbo.temp")
+   exec(t, db, "create table dbo.temp(id int identity, v int)")
+   var id int64
+   err = db.QueryRow("set nocount on; insert into dbo.temp(v) values(123); 
select scope_identity()").Scan(&id)
+   if err != nil {
+       t.Fatal(err)
+   }
+   t.Logf("id=%v", id)
+   exec(t, db, "drop table dbo.temp")
+}

Perhaps there are better ways to do that. But ...

Alex

Original comment by alex.bra...@gmail.com on 5 Mar 2014 at 3:36

[GoogleCodeExporter]

Awesome. Appending the "nocount on" works both on windows and ubuntu.
Thank you for your assistance

2014-03-05 4:36 GMT+01:00 <odbc@googlecode.com>:

Original comment by rzemy...@gmail.com on 5 Mar 2014 at 8:21