MBS: How to retrieve scope_identity() value?

Add-Ons
1

I’m trying, but failing, to get the last inserted id of a table using SCOPE_IDENTITY(). I.e:

dim cmd as new SQLCommandMBS(con, "INSERT INTO "+iTable.text+" ("+iColumn.text+") VALUES ('"+d.SQLDateTime+"')" + ";SELECT SCOPE_IDENTITY() as last_id")

In the debugger, the SQLCommandMBS does show a returned resultset, but the value of last_id is Nil.

I’m new to the MBS SQL plugin, so it’s likely something I’m doing wrong. (The docs are a bit daunting…)

Notes: Most recent MBS SQL plugin in Mac Xojo 2014r2.4 via ODBC to MSSQL 2005.

2

maybe run a second SQLCommandMBS for the select command?
And you did call FetchNext to get first line of data?

3

[quote=219872:@Christian Schmitz]maybe run a second SQLCommandMBS for the select command?
And you did call FetchNext to get first line of data?[/quote]

Thanks for the pointer. I’ll try it out this weekend.

4

OK, it took me a while to figure out why I wasn’t getting resultSets back. For my future reference, do not forget to to add:

con.SetFileOption con.kOptionLibraryODBC, libtdsodbc

for the SQLConnectionMBS object. Now on to getting results from SCOPE_IDENTITY()…

5

[quote=219872:@Christian Schmitz]maybe run a second SQLCommandMBS for the select command?
And you did call FetchNext to get first line of data?[/quote]

No need to run a second instance of SQLCommandMBS. The query as written does return the correct SCOPE_IDENTITY() value once I switched to FetchNext in a while loop. Thank you.

6

excellent.