Welcome, Guest
Username: Password: Remember me
Visual Objects

Please use this forum to post questions about Visual Objects and Vulcan.NET
  • Page:
  • 1

TOPIC:

ADS Remote DBServer Monitoring (X# 2.7) 01 Dec 2020 10:41 #16816

  • rjpajaron
  • rjpajaron's Avatar
  • Topic Author


  • Posts: 359
  • Hello,

    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!
    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 backup\mempco\2020\crm_data\" //CRMFOS\C00001"
    	
    	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
    --

    Rene Pajaron

    Please Log in or Create an account to join the conversation.

    • Page:
    • 1