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_idThis 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.
in hope that it helps.
Thanks Louis.
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.
Could you do something with the output clause and the inserted pseudo table?
Caveats - doing this completely from memory and not tried this with Xojo MSSQL plugin!!
Assuming the table has identity column
INSERT tablename (column1)
OUTPUT Inserted.idcolumn, Inserted.column1
VALUES ('value')
Thanks Pat.
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 @tablevarThat would solve my immediate problem so I’ll look into it. Thanks again.
@Kem Tekinay - Found this via StackOverflow (and about three other places):
Also, consensus in all posts I read same as Patrick’s that SCOPE_IDENTITY is the way to go.
HTH!
Anthony
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…
and same thing, the RecordSet comes back as nil.
I feel like there is something I’m not understanding about MSSQL.
Kem - Dont have a MS SQL DB to test against these days. Couple of other thoughts:
Are you explicitly creating a transaction and calling SCOPE_IDENTITY() within that transaction?
If that doesnt work:
Have you tried a different version of Xojo’s MSSQLServerPlugin" plugin?
Have you tried with Christians SQL plugin?
Just trying to think of the more obvious stuff.
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.
Then again this? http://www.bigresource.com/MS_SQL-IDENT_CURRENT-permissions-C9qHEkZy.html
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.
(Probably tomorrow though. 
)
Hope it does!
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.
Kem, the following does work with MBS.
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.
This has been our take on it for a couple of years.
I’ve never been able to get that working reliably. I just write an SQLDateTime stringvalue and it works fine.