See below is a working code on ADS Remote DBServer Monitor.
The code is already in our production with some modification to fit our use cases.
In case where the ADS data folder location were hidden, with actual path alias describe at adsserver.ini at the physical server boot drive, I implemented this by having a data config table that stores the actual folder location of our production database. This way "folderPath" is relative as the code monitors the running ADS which probably is only exposed only by IP or UNC path and actual path are hidden.
RequiredTables works like a treat when you only target particular table or tables. I did this when the app is about to do some maintenance that requires EXCLUSIVE access to a group of tables. Sysadmin love the this tools because they can see which computers are opening tables and also detect running apps on sleeping computers.
Well, it works in our end in VO and on X# 2.7 when Robert decided to include ADS ACE Management API. Thanks Roberts.
Cool!
Code: Select all
USING Xsharp.ADS
DEFINE AE_SUCCESS := 0
FUNCTION Start() AS VOID STRICT
? "Hello World! Today is ",Today()
LOCAL oRDSM AS ADSRemoteDbServerMonitor
LOCAL tableArrayList AS ARRAY
LOCAL userArrayList AS ARRAY
LOCAL requiredTables AS ARRAY
LOCAL tableCount, userCount AS DWORD
LOCAL folderPath AS STRING
BEGIN SEQUENCE
folderPath := "d:client coopbase data backupmempco2020crm_data" //CRMFOSC00001"
tableArrayList := {}
requiredTables := {}
userArrayList := {}
oRDSM := ADSRemoteDbServerMonitor{}
oRDSM:SetupData(folderPath)
oRDSM:SetupRequiredTables(requiredTables)
oRDSM:GetOpenTablesList()
tableArrayList := AClone(oRDSM:GetTableArrayList)
userArrayList := AClone(oRDSM:GetUserArrayList)
tableCount := oRDSM:GetTableCount
userCount := oRDSM:GetUserCount
oRDSM:Destroy()
END SEQUENCE
oRDSM := NULL_OBJECT
?
? "ADS Remote Table Monitoring"
?
? "Tables list:"
ShowArray(tableArrayList)
?
?
? "User list:"
ShowArray(userArrayList)
?
?
? "Number of tables: "
?? tableCount
? "Number of users: "
?? userCount
?
?
WAIT
RETURN
CLASS ADSRemoteDbServerMonitor
PROTECT tableArrayList AS ARRAY
PROTECT userArrayList AS ARRAY
PROTECT requiredTables AS ARRAY
PROTECT tableCount, userCount AS DWORD
PROTECT folderPath AS STRING
DECLARE METHOD SetupData
DECLARE METHOD SetupRequiredTables
DECLARE METHOD GetOpenTablesList
DECLARE ACCESS GetTableArrayList
DECLARE ACCESS GetUserArrayList
DECLARE ACCESS GetTableCount
DECLARE ACCESS GetUserCount
METHOD Destroy()
SELF:requiredTables := NULL_ARRAY
SELF:tableArrayList := NULL_ARRAY
SELF:userArrayList := NULL_ARRAY
RETURN NIL
METHOD GetOpenTablesList() AS VOID PASCAL
LOCAL hMgmtHandle AS System.IntPtr
LOCAL ulRetVal AS WORD
LOCAL usStructSizeTable, usStructSizeUsers AS WORD
LOCAL usArrayLenTable, usArrayLenUsers AS WORD
LOCAL pbufferTable AS ADS_MGMT_TABLE_INFO
LOCAL pbufferUsers AS ADS_MGMT_USER_INFO
LOCAL tableName AS STRING
LOCAL i, j AS DWORD
LOCAL pos AS DWORD
LOCAL userName AS STRING
LOCAL lRequiredTables AS LOGIC
BEGIN SEQUENCE
tableArrayList := {}
userArrayList := {}
tableCount := 0
userCount := 0
ulRetVal := AdsMgConnect( SELF:folderPath, "", "", @hMgmtHandle )
// If there was an error then show it and exit
IF ulRetVal == AE_SUCCESS
ELSE
? "Could not connect to server." +CRLF +CRLF +"Server connection error!!!"
BREAK
ENDIF
usArrayLenTable := 250
usStructSizeTable := _SIZEOF( ADS_MGMT_TABLE_INFO )
pbufferTable := MemAlloc( usArrayLenTable * usStructSizeTable )
ulRetVal := AdsMgGetOpenTables( hMgmtHandle, NULL, 0, pbufferTable, REF usArrayLenTable, REF usStructSizeTable)
IF ulRetVal == AE_SUCCESS
ELSE
? "Could not determined number of tables currently open at the server located at: " +SELF:folderPath +CRLF +CRLF +"Server access error!!!"
BREAK
END
IF requiredTables != NULL_ARRAY .AND. alen(requiredTables) > 0
lRequiredTables := TRUE
ENDIF
FOR j:= 1 UPTO usArrayLenTable
? tableName := Psz2String(@pbufferTable.aucTableName)
IF lRequiredTables
pos := AScan(requiredTables,{|x| Upper(x) == Upper(FileBase(tableName)) } ) //monitor only those table that matters!!!
ELSE //no specific tables are monitored, always defualt to 1
pos := 1
ENDIF
IF pos > 0
BEGIN SEQUENCE
usArrayLenUsers := 250
usStructSizeUsers := _SIZEOF(ADS_MGMT_USER_INFO)
pbufferUsers := MemAlloc(usArrayLenUsers * usStructSizeUsers)
ulRetVal := AdsMgGetUserNames( hMgmtHandle, tableName, pbufferUsers, REF usArrayLenUsers, REF usStructSizeUsers)
IF ulRetVal <> AE_SUCCESS
? "Could not determined number of users on table: " +tableName +CRLF +CRLF +"Table access error!!!"
BREAK
ENDIF
FOR i := 1 UPTO usArrayLenUsers
userName := Psz2String(@pbufferUsers.aucUserName)
IF AScan(userArrayList,userName) == 0
AAdd(userArrayList,userName)
userCount += 1
ENDIF
AAdd(tableArrayList,{FileBase(tableName),; //table filename without extension
FilePath(tableName),; //full path
userName,; //user name
pbufferUsers.usConnNumber,;
Psz2String(@pbufferUsers.aucAuthUserName),; //auth user name
Psz2String(@pbufferUsers.aucAddress)} ) //address
tableCount += 1
pbufferUsers++
NEXT
END SEQUENCE
IF pbufferUsers != NULL_PTR
MemFree(pbufferUsers)
ENDIF
ENDIF
pbufferTable++
NEXT
END SEQUENCE
IF pbufferTable != NULL_PTR
MemFree(pbufferTable)
ENDIF
IF pbufferUsers != NULL_PTR
MemFree(pbufferUsers)
ENDIF
AdsMgDisconnect( hMgmtHandle )
RETURN
ACCESS GetTableArrayList AS ARRAY PASCAL
RETURN SELF:tableArrayList
ACCESS GetTableCount AS DWORD PASCAL
RETURN SELF:tableCount
ACCESS GetUserArrayList AS ARRAY PASCAL
RETURN SELF:userArrayList
ACCESS GetUserCount AS DWORD PASCAL
RETURN SELF:userCount
METHOD SetupData(_folderPath AS STRING) AS VOID PASCAL
SELF:folderPath := _folderPath
RETURN
METHOD SetupRequiredTables(_requiredTables AS ARRAY) AS VOID PASCAL
SELF:requiredTables := {}
IF _requiredTables != NULL_ARRAY .AND. ( ALen(_requiredTables) > 0 )
SELF:requiredTables := AClone(_requiredTables)
ENDIF
RETURN
END CLASS
FUNCTION FileBase( cFile AS STRING ) AS STRING PASCAL
LOCAL nPos AS DWORD // Marks the position of the last "", if any
LOCAL cFileBase AS STRING // Return value containing the filename
DO CASE
CASE ( nPos := RAt( "", cFile )) != 0
// Strip out full path name leaving only the filename (with
// extension)
cFileBase := SubStr2( cFile, nPos + 1 )
CASE ( nPos := At( ":", cFile )) != 0
// Strip drive letter if cFile contains only drive letter
// no subdirectories
cFileBase := SubStr2( cFile, nPos + 1 )
OTHERWISE
// Assume it's already taken care of
cFileBase := cFile
ENDCASE
// Strip out the file extension, if any
IF ( nPos := At( ".", cFileBase )) != 0
cFileBase := SubStr3( cFileBase, 1, nPos - 1 )
ENDIF
RETURN ( cFileBase )
FUNCTION FilePath( cFile AS STRING ) AS STRING PASCAL
LOCAL nPos AS DWORD// Marks the position of the last "" in cFile, if any
LOCAL cFilePath AS STRING // The extracted path for cFile, exluding the filename
IF ( nPos := RAt( "", cFile )) != 0
cFilePath := SubStr( cFile, 1, nPos )
ELSE
cFilePath := ""
ENDIF
RETURN cFilePath