I need details out of the VST (_connect) for some conditional logic. For example, the field _Connect._Connect-ClientType is of interest to me (will tell me if my session is running code with a client-server connection or shared-memory).
What I've been doing thus far is to first get the _MyConn-Pid from _MyConnection. Then I use the PID to access the related _Connect record.
It is similar to what is happening in the following KB. Notice the sample code.
https://knowledgebase.progress.com/articles/Article/P19414
DEF VAR servernum AS INTEGER NO-UNDO. FIND FIRST _Myconnection. FIND FIRST _connect WHERE _connect._connect-pid = _Myconnection._myconn-pid. ASSIGN servernum=_connect._connect-server. FIND FIRST _connect WHERE _connect._connect-usr = servernum. DISPLAY _connect._connect-pid.
The example results in very buggy behavior. The PID is NOT NECESSARILY unique. Especially now that we have remote ABL code that is connecting from PASOE instances. ... It is not unusual for there to be multiple ABL client sessions or PASOE sessions that have an identical PID (because they run on different machines).
I suppose I will need to use BOTH the pid and the user number to find the related _connect details, right? IE. it might look something like so:
FOR EACH _Connect
WHERE _Connect._connect-pid = p_ConnectionPid
AND _Connect._connect-usr = p_ConnectionUserId
It is odd that the KB would have such a buggy example. I think I've seen more than one example from progress where the _connect record is found using only the PID.
The bigger underlying question is what are the unique constraints on the _Connect data in the vst? Is it _Connect-Usr? Is it the combination of _Connect-Usr and _Connect-Pid?
stuff that might be worth remembering:
0) user numbers (aka connection numbers) are relevant only to one
database. a 4GL user may be connected to many databases with
different connection numbers in each.
1) the process id is valid only for the machine the connection is
coming from. it is unique on that machine (which might be a
virtual machine).
2) _connect-ipaddress tells you from where the connection orginates
3) the database primary broker always has the first _connect table
entry. its ip address would be the ip address of the machine
hosting the database and its servers.
4) looks like the ip address field is not filled in for local connections.
i have not verfied this but the 11.7 doc implies so.
DEF VAR servernum AS INTEGER NO-UNDO. FIND FIRST _MyConnection. FIND FIRST _Connect WHERE _Connect._Connect-Id EQ _MyConnection._MyConn-UserId + 1. ASSIGN servernum=_connect._connect-server. FIND FIRST _Connect WHERE _Connect._Connect-Id EQ servernum + 1. DISPLAY _connect._connect-pid.
The *-id field is the unique key for most VSTs. _myConnection is an exception since there is only ever just the one record.
In almost all cases when you are working with VSTs you want to use the "id" field as the unique key. (This is a good reason to keep the distinction between "vst" and "meta schema" clear because this is NOT the case with meta schema tables.)
It is also useful to note that: _connect-id = _connect-usr + 1
It is also useful to note that _connect-usr = ? means that there is no session active for that _connection.
The *efficient* way to find the _connect record corresponding to a given usr# is:
find _myconn no-lock.
display _myconn.
find _connect no-lock where _connect-id = _myconn-userid + 1.
display
_connect-id
_connect-usr
_connect-name
_connect-pid
.
The feature of the *-id indexes:
FOR EACH _Connect WHERE _Connect-Id EQ 0: DISPLAY _Connect-Id. END.
is equivalent of:
FOR EACH _Connect: DISPLAY _Connect-Id. END.
stuff that might be worth remembering:
0) user numbers (aka connection numbers) are relevant only to one
database. a 4GL user may be connected to many databases with
different connection numbers in each.
1) the process id is valid only for the machine the connection is
coming from. it is unique on that machine (which might be a
virtual machine).
2) _connect-ipaddress tells you from where the connection orginates
3) the database primary broker always has the first _connect table
entry. its ip address would be the ip address of the machine
hosting the database and its servers.
4) looks like the ip address field is not filled in for local connections.
i have not verfied this but the 11.7 doc implies so.
Thanks for all the helpful responses.
From my perspective, I would not want to rely on a surrogate id (ROWID or identity column or whatever) in a way that assumes that it has meaningful information.
Given that a user number (aka connection number) is found in _MyConnection._MyConn-UserId, then I would use that to access the _connect table, by joining to a corresponding field which also contains a user number. ("_Connect._connect-usr").
The problem with making assumptions about surrogate ID's is that can introduce breaking changes as software evolves over time. A surrogate ID might be a guid, integer counter, or some other type of random number. It seems to me that the following code introduces some assumptions that might not always be true in future versions of Progress:
FIND FIRST _Connect WHERE _Connect._Connect-Id EQ _MyConnection._MyConn-UserId + 1
Maybe I've been bitten too many when trying to derive meaningful information out of a surrogate ID (rather than just using it as a record ID). I suppose if the "assumed" relationship in the code above is well-documented then Progress won't quickly change it in a future version. But It seems unlikely to me that Progress wants us to write code that way - or why would the _connect table also have the explicit field for "_Connect._connect-usr".
As far as my original question goes, I think I will now have to start including the user number (aka connection number) when I find the related _connect. The PID alone is clearly not enough. Too bad we have a lot of legacy code (and Progress has a lot of legacy KB artices) that use _MyConnection._MyConn-Pid to find a _Connect record. Setting PASOE aside, that probably wouldn't have worked for other types of remote client-server code either!
> It seems to me that the following code introduces some assumptions that might not always be true in future versions of Progress
Anything possible in the future versions. For example, _Startup is a well documented VST but it will be removed in V12.
> But It seems unlikely to me that Progress wants us to write code that way
End-user wants us to write code that works fast.
WHERE _Connect-Id EQ ... is fast.
WHERE _Connect-Usr EQ ... is not.
If you want it to be fast and efficient use _connect-id.
If performance doesn't matter to you or your users then go ahead and use _connect-usr.
The problem with making assumptions about surrogate ID's is that can introduce breaking changes as software evolves over time.
Of course, this is exactly the motivation for surrogate IDs, i.e., to provide an arbitrary identifier which uniquely identifies the record without dependence on any of the meaningful information in the record.