You see I'm not the only one having questions about the structure and what you do, beginning to end, not just part of the code.
How about starting over?
Say you have a table Sessions and want a new session, while purely using remote views instead of mixing data access methods.
Suppose the Session table has this structure:
Code:
SessionId (Integer Identity/PK)
SystemId (Integer, FK into Systems table)
UserId (Integer, FK into Users table)
SessionStartTime (datetime2 Default GetDate())
SessionEndTime (datetime2 Nullable)
Your application knows its SystemId and the UserId logged into it as properties of goApp, your application object. So those two column values come from your application and are in the updatable field list. SessionId is the PK and is generated by SQL Server.
Then you could define a view with the query
Code:
Select * From Sessions Where SystemId = ?m.goApp.nSystemId And UserId = ?m.goApp.nUserId And SessionEndTime Is Null
The updatable field list must be SystemId, UserId. The keyfieldlist is just SessionId.
I know you said only a combination of SessionId and SystemId is the PK, but then SessionId is Identity. You can stay with that and make it the PK, the SystemId doesn't need to be part of the PK at all, it may be mandatory because a session must be for a specific system, but that's a referential integrity constraint, not a PK constraint. When each SessionId is a number of an Identity sequence it's unique in itself already and sufficient for the PK, don't overcomplicate things.
To get all sessions of a specific system you can have a separate view without the UserId clause. And yes, it is okay to define many such views, you're not limited to one per table.
But that other view definition without UserId has nothing to do with starting a new session of a user, it may be of interest when there is a number of licenses usable concurrently, but not to check whether UserID X has a session or not.
Stop thinking the VFP way to USE a table and LOCATE the record of interest. Go straight to your goal with the query.
When you want to start a new session you set both SystemId and UserId to see whether there already is a running session (notice the view definition checks whether the SessionEndTime is NULL). You get the user's session record or an empty view cursor. If it's empty you can APPEND BLANK in the view and then TABLEUPDATE(0,.t.,'rv_sesssion'), then Requery() and you have your session record including its SessionId.
This you don't need @@IDENTITY nor SCOPE_IDENTITY(), as there can only be one active record for a user/system combination, with a NULL for the session end time and the UserId, SystemId combination.
So the session creation and/or reconnection to a running session could be done this way:
1. A User logs into your app, therefore you know goApp.nSystemID and goApp.nUserId
2. Looking for an already started session by the view with goApp.nUserId and goApp.nSystemId known.
3.1 Finding a record in the view cursor you are at your goal and have a session.
3.2 If - and only if - the view has no record, Insert a new record to it without setting SessionId at all, only set UserId, SystemId and even the Session StartTime will be Server time by its default value. You then TableUpdate(0,.t.,'rv_session') and Requery() to get your own SessionId.
Maybe set that in goApp.nSessionId in both cases 3.1 and 3.2
And then to end a session you can update the single record in your rv_sessions cursor with the end time and once more TABLEUPDATE(0,.T.,'rv_sessions') to set the SQL Server session record to its archived state by having its end time.
[bbox][/bbox]
You see you can solve things like getting the generated Id just defining tables in a way you have a single record of interest. With 1:n:m relations there always is one head, and even if you use a table like Sessions to also archive the past sessions you still have a way to filter only the active session by having an end session time, as you already have it.
So you thought about all the necessary aspects but just didn't think it down to the bottom line.
I know getting a new ID is still a topic of interest in the general case, it's simple in VFP to APPEND BLANK and have Sessions.SessionId in the current record, but SQL Server has no such concept.
So let's see how to get there, but I'll post that in your other thread, where @@IDENTITY and SCOPE_IDENTITY() is the more central topic.