Can someone with access to MSSQL check this? Here, if I issue the following SELECT statement through Management Studio after an insert, I get a result, but if I issue it through a MSSQLServerDatabase object, I get nil.
SELECT SCOPE_IDENTITY() AS last_insert_id
This should return the last inserted primary key value.
Are you issuing the scope_identity select as a separate statement from the insert? if so, it should return null. A separate statement will be a different scope than the insert.
Also, I remember reading somewhere that ODBC sends insert requests directly to the server, and scope_identity requests through RPC, effectively sending the select scope_identity request in a different scope, resulting in null being returned always. I am a bit fuzzy on that one, you may want to double check. This is as far as I can go with ODBC, since I use ADODB to connect to MSSQL.
A little more information: I got back a nil RecordSet whether I included this as part of the insert statement or issued it immediately afterwards. I got back the “correct” result in Management Studio whether I made it part of the insert statement or issued it after my Xojo app issued the insert. I didn’t really expect the latter as I was just testing, but it worked.
My research (very brief research) on MSSQL showed me the statement above and I didn’t see anything about OUTPUT. I’ll look into it and if that works, great, but I’d still like to know if the above statement should work so I can file Feedback.
From MSDN on SCOPE_IDENTITY() Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.
The Output clause has been around since SQL 2005 (IIRC). It would be how I’d approach this.
The output clause also supports an INTO to write the data into table variables, temp tables and actual tables if that helps - e.g.
OUTPUT Inserted.idcolumn, Inserted.column1 INTO @tablevar
Except SCOPE_IDENTITY() doesn’t seem to work in Xojo. I’ll file that as Feedback. Meanwhile, Output certainly work though Management Studio. Going to test in Xojo next…
Without giving away too much, I’m using a very recent MSSQL plugin through a VERY recent version of Xojo.
I’m building an open-source database adapter for MSSQL, the same as I’ve already done for Postgres, MySQL, and SQLite. In that respect, while a version based on an MBS plugin would be welcome and (probably) possible, it’s outside the scope of what I’m trying to do here.
If anyone wants to look at the project, it’s here:
Ive browsed the repo/been following along. Looks really promising!
I built something a few years back for internal-use only (customer-owned source) but went much more with a "convention over configuration POV where the way users named controls/classes directed how the SQL was generated/behaved.
I was simply trying to pin down the root of the problem:
Is it the plugin?
Is it some setting on the MS SQL server?
Is it a permission issue with the user thats somehow skirted when you use the Management Studio?
Is it a networking issue thats somehow skirted when you use the Management Studio?
Since youre using that VERY recent version of Xojo. Its possible it could be the plugin, but that seems unlikely to me since youre able to connect and execute other queries.
My gut feels like it could be a permissions thing", but thats totally speculation. I do know that MS SQL server can be very granular with permissions, though it restricting a user in such away that you experience what youre experiencing seems unrealistic.
That’s a good thought! I set up a special username/password but was executing my queries using the admin account. I’l try via the “unittests” account in Management Studio to see if there is a difference.
Ive danced a lot of strange dances with other people’s databases. Permissions(user/db user/file) and network restrictions have bitten me in a lot of ways over the year. I probably have database rabies.
One thing you could do is write the output clause to a temporary table and read from that in a new statement. That should get you past any scope problems.
temp tables are limited to the connection in hand so it’ll be available so long as the connection is open.
dim cmd as new SQLCommandMBS(con, "INSERT INTO "+iTable.text+" ("+iColumn.text+") VALUES ('"+d.SQLDateTime+"')" + ";SELECT SCOPE_IDENTITY() as last_id")
I’ve concat’d the 2 queries together. At least part of the problem is that Xojo’s plugin doesn’t return any values for this query, whereas Christian’s and SSMS both return correct values.
That’s the way it seems, and I’d call that a bug, but I’m going to test Anthony’s idea about permissions later just to make sure.
The other problem I’m seeing is that SQL that generates an error in Management Studio does not return an error through the plugin, making it more difficult to figure out what’s going on.
Finally, my test code silently fails to insert when I attempt to bind a Date.
I’m going to wrap all this up into Feedback when I have more information, but I’d say the MSSQL plugin needs at least a little work.