How to use AX_GetActivityInfo() of ADS V.9

[dutch]

Dear ADS expert,

I try to check the current number of connection to ADS (remote) and got the help from Sybase with this function AX_GetActivityInfo(). It show the error while compiling the program.

I use xHarbour (FWH) + FWH10.1 + BCC55. It shows unresolved function but I try with EXTERNAL and REQUEST statement. It doesn't work.

Any help and idea, most appreciated.

Regards,
Dutch

[lucasdebeltran]

Dutch,

This function is for Clipper, not for Harbour.

You have to look for an ADS_xxx equivalent.

[reinaldocrespo]

Dutch;

Execute stored procedure sp_mgGetConnectedUsers() to get a cursor with the list of connected users. Like this:

Code: Select all  Expand view

   LOCAL cSql := "EXECUTE PROCEDURE sp_mgGetConnectedUsers()"
 

Using AdsCreateSqlStatement() and AdsExeucteSqlDirect() you can execute this sql.


Reinaldo.

[Massimo Linossi]

Hi Reinaldo.
Where can I find a list of all possible stored procedures ?
Thanks a lot.
Massimo.

[reinaldocrespo]

Massimo;

That is a really good question, especially because there is a stored procedure for just about anything, like disconnecting a user, getting a table structure, getting a list of current locks and their owner, reindexing, packing, zapping, renaming or deleting a table, getting the current server type, creating users and groups, encrypting and decrypting tables, creating referential integrity... the list is endless.

To see a complete list of available stored procedures, open the help file on the Index tab and type sp_. Move the bar to each stored procedure and on the right you get their description, and how to use it. Stored procedures are executed by the server saving a lot network traffic.

There are also many sql statements you can use to manage the database such as:

SELECT curdate() FROM system.iota //returns the current date
SELECT now() FROM SYSTEM.iota //returns current timestamp
SELECT * FROM system.columns WHERE parent = 'customers' //returns table customers structure (fields, size, type)
SELECT user() FROM system.iota /returns current logged in user
SELECT * FROM system.tables //returns all tables in dd along with a lot of info on each table
SELECT * FROM system.indexes WHERE parent = 'customers' //returns all indexes on table customers
SELECT applicationId() FROM system.iota //returns information on current connection such as computer name, ip, windows name etc...

You can test any of these using arc32 while connected to a DD.

Hope this information is useful for you.

Reinaldo.

[Massimo Linossi]

Thanks a lot.
These are really precious informations.
Have a nice day (or evening).
Massimo.

[dutch]

Dear Reinaldo,

I try your example but it return .T. How does it show the number of current connection in ADS (Remote)?

Regards,
Dutch

Dutch;

Execute stored procedure sp_mgGetConnectedUsers() to get a cursor with the list of connected users. Like this:

Code: Select all  Expand view

   LOCAL cSql := "EXECUTE PROCEDURE sp_mgGetConnectedUsers()"
 

Using AdsCreateSqlStatement() and AdsExeucteSqlDirect() you can execute this sql.


Reinaldo.

[reinaldocrespo]

Hi Dutch;

Code: Select all  Expand view

cAlias := "cursor"
cSql := "EXECUTE PROCEDURE sp_mgGetConnectedUsers()"
ADSCreateSQLStatement( cAlias )
AdsExecuteSQLDirect( cSql )

While !(cAlias)->( eof() )
    ? (cAlias)->Username, (cAlias)->Address, (cAlias)->DictionaryUser
    (cAlias)->( dbSkip() )
EndWhile
 

That code will print the computer username, the ip address of the pc, and the DD username of all users connected to the DD.

Try it. I hope that helps.

Best regards,


Reinaldo.

[Massimo Linossi]

Hi Reinaldo.
I've tried your function but all the fields contains nil values.
Is there something wrong ?
Thanks a lot.
Massimo.

[reinaldocrespo]

What is your server version? Are you connected to a remote server (not the local server)? Are you connected as the ADSSYS user?

Try executing the stored procedure from arc32, do you get results?


Reinaldo.

[Massimo Linossi]

The server version is the 10,and I'm connected to a remote as ADSSYS.
Making the SQL command inside the ARC32 works perfectly.
It is really strange.
If I change the while..endwhile procedure with a browse() I see only one empty record.

[reinaldocrespo]

When strange things like this happen, it is always a good idea to look at the errorlog. Try opening AdsErr.adt table from arc32. That should help us know what's happening.

Furthermore, try adding an error trap after each ace api call, like this:

Code: Select all  Expand view

AdsCreateSqlStatemtn( cArea )
CheckForErrors()
AdsExecuteSqlDirect( cSql )
CheckForErrors()


//------------------------------------
Function CheckForErrors()
local nErr, cErr
   If (nErr := AdsGetLastError( @cError ) ) != 0
      msgError( cError, nErr )
   Endif
return nil
 

At some point I will clean up some my TAdsQuery class and will post it here. It should help for executing ads sql scripts. But for now the code above should help to find what is going wrong.


Reinaldo.

[reinaldocrespo]

Here is a screenshot from a window where I'm using sp_mgGetConnectedUsers() to show the system administrator what users are connected and the open tables they each have. It also allows the administrator to disconnect a user and to stop new connections from happening. Useful when you wish to install an update or update the data dictionary:




This is the code that produces the screenshot above:

Code: Select all  Expand view

*------------------------------------------------------------------------------------------------------
METHOD ShowConnectedUsers() CLASS MpAdmin
   LOCAL cSql := "EXECUTE PROCEDURE sp_mgGetConnectedUsers()"
   LOCAL cSeek , cUser, cTable
   LOCAL aData := {}
   LOCAL aCursor, bChange, bGetConnUsers
   LOCAL aChildData := {}
   LOCAL bDelete
   LOCAL oSelf := Self
   LOCAL oDlg, oGet1, oBrw, oChildBrw, oSay, oChkBx
   LOCAL isLoginsEnabled := !ADSDDGetDatabaseProperty( ADS_DD_LOGINS_DISABLED )

   IF ifOpenOption( 'CONNECTEDUSERS' )
      RETURN NIL
   ENDIF

   aCursor := ExecuteSqlScript( cSql, .f. )
   aEval( aCursor, { |e,n| aadd( aData, { e[ 3 ], e[ 4 ], e[ 1 ] } ) } )

   bGetConnUsers := { || aCursor := ExecuteSqlScript( cSql, .f. ), ;
         aData := {}, ;
         aEval( aCursor, { |e,n| aadd( aData, { e[ 3 ], e[ 4 ], e[ 1 ] } ) } ),;
         oBrw:aArrayData := aData,;
         oBrw:Refresh() ,;    //needed here in case nArrayAt was past eof. to avoid run time error
         Eval( bChange ),;
         oDlg:Update(),;
         oBrw:Refresh() }

   bChange := { || oChildBrw:aArrayData := ;
         IF( Len( oBrw:aArrayData ) > 0 .and. Len( oBrw:aArrayData[ oBrw:nArrayAt ] ) > 2, ;
            ExecuteSqlScript( "EXECUTE PROCEDURE SP_mgGetUserTables('" + ;
            alltrim( oBrw:Computer:Value() ) + "' );", .f. ),;
            {} ),;
         aEval( oChildBrw:aArrayData, { |e,n| hb_FNameSplit( e[ 1 ],,@cTable ),;
               oChildBrw:aArrayData[ n,1 ] := Lower( cTable ) } ),;
         oChildBrw:Refresh()  }

   bDelete := { || IF( len( oBrw:aArrayData ) > 0 .and. ;
         ( cUser := alltrim( oBrw:aArrayData[ oBrw:nArrayAt, 3 ] ),;
         MsgYesNo( "Disconnect User " + cUser ) ), ;
         ( ExecuteSqlScript( "EXECUTE PROCEDURE sp_mgKillUser( '"+cUser+"' );", .f. ),;
         Eval( bGetConnUsers ) ), NIL ) }

   DEFINE DIALOG oDlg RESOURCE "CONNECTED_USERS" OF ::oPanelBrowse ;
      TRANSPARENT COLOR CLR_BLACK, RGB(240,240,240)

      REDEFINE XBROWSE oBrw ID 1 OF oDlg ;
         ARRAY aData AUTOCOLS AUTOSORT;
        HEADER "User", "ip", "Computer" ;
         SIZES 120,100, 100 ;
     ON CHANGE Eval( bChange )

   oBrw:nMarqueeStyle := MARQSTYLE_HIGHLWIN7
   oBrw:bClrStd    := { || If( oBrw:KeyNo % 2 == 1, { CLR_BLUE, CLR_WHITE }, { CLR_BLUE, RGB(242,242,255)} ) }

   oBrw:bKeyDown := {|nkey| IF( nkey = K_DEL, ( Eval( bDelete ), ;
                            Eval( bGetConnUsers ) ), NIL ) }

   REDEFINE GET oGet1 Var cSeek ID 101 OF oDlg WHEN .f.
   oBrw:oSeek := oGet1

   REDEFINE SAY oSay PROMPT "Total Connections: " + cValToChar( Len( aData ) ) ;
      ID 102 OF oDlg COLOR CLR_BLACK UPDATE

   REDEFINE CHECKBOX oChkBx VAR isLoginsEnabled ID 4 OF oDlg
   oChkBx:bChange := { || AdsDDSetDatabaseProperty( ADS_DD_LOGINS_DISABLED, !isLoginsEnabled ) }

   setGridProperties( oBrw, .f. )

      REDEFINE XBROWSE oChildBrw ID 2 OF oDlg ;
         ARRAY aChildData AUTOCOLS AUTOSORT;
        HEADER "Table Alias" ;
         SIZES 150

   setGridProperties( oChildBrw, .f. )
   oChildBrw:bClrStd    := { || If( oChildBrw:KeyNo % 2 == 1, { CLR_BLUE, CLR_WHITE }, { CLR_BLUE, RGB(242,242,255)} ) }

   REDEFINE BUTTONBMP ID 201 OF oDlg BITMAP "Cancel16" TEXTRIGHT ACTION Eval( bDelete )
   REDEFINE BUTTONBMP ID 202 OF oDlg BITMAP "run16" TEXTRIGHT ;
      ACTION Eval( bGetConnUsers )
   REDEFINE BUTTONBMP ID 203 OF oDlg BITMAP "exit16" TEXTRIGHT ACTION oDlg:END()

   oDlg:lHelpIcon := .F.
   FONDOBOTONES 1003, oDlg

   ACTIVATE DIALOG oDlg NOWAIT ;
      ON INIT ( oDlg:Center( oSelf:oPanelBrowse ), Eval( bChange ), oBrw:setFocus() ) ;
      VALID closeOption( 'CONNECTEDUSERS' )

   AADD( aOpenOptions, { 'CONNECTEDUSERS', oDlg } )

RETURN NIL

 

Hope that helps,


Reinaldo.

[reinaldocrespo]

Here is the source code to function ExecuteSqlScript. This is an old function that I'm no longer using in favor of a newer TAdsQuery class I wrote more recent. The function is only being used on older code such as the one I'm showing above to display connected users:

Code: Select all  Expand view

*-------------------------------------------------------------------------------------------------------------------------------
FUNCTION ExecuteSQLScript( cScript, lShowProgress, nTblType, lCursor, bProgress, cMsg )
Local cArea := ValidAlias( "SqlFunc" )
local aStruc, i
local nCount := 1
local a := {}
local xTmp, oMeter
local isGood := .f.

DEFAULT lShowProgress := .t.
DEFAULT nTblType := ADS_ADT
DEFAULT lCursor := .f.

   nErr := 0      ;cErr := ""

   if !empty( cScript )

      AdsCacheOpenCursors( 0 )
      DBSELECTAREA(0)

      if Select( cArea ) > 0 ; ( cArea )-> ( DBCLOSEAREA() ) ;endif

      IF !ADSCreateSQLStatement(cArea, nTblType ) //.or. !ADSVerifySQL( cScript )

         nErr := ADSGetLastError( @cErr )
         logfile( "SQLError.log", { nErr, cErr, cScript } )

         if Select( cArea ) > 0 ; ( cArea )-> ( DBCLOSEAREA() ) ;endif

      Else
         if lShowProgress

            if bProgress == Nil
               DEFAULT cMsg := "Working..."
               /*oMeter:= msgMeter():New( 100 )
               bProgress := { |n| oMeter:Set( n ) } /**/

               oMeter := PROGRESSBAR():New( 100, cMsg )
               ACTIVATE DIALOG oMeter:oDlg NOWAIT ON INIT oMeter:oDlg:center( WndMain() )
               bProgress := { |n| oMeter:Update( n, .t. ) }

            endif

            Register_CallBack( { | nPercent | nCount++, ;
               iif( nCount * nPercent > 100, nCount := 1, ), ;
               Eval( bProgress, nPercent ) } )

            isgood := ADSExecuteSQLDirect( cScript )

            Unregister_callback()

            if oMeter != Nil
               oMeter:end()
            endif

         else
            isgood := ADSExecuteSQLDirect( cScript )
         endif

         if isgood

            if ( nErr := ADSGetLastError( @cErr ) ) != 0
               logfile( "SQLError.log", { "ADS error:", nErr, cErr, cScript } )
            endif

            if lCursor   ;return( cArea )   ;endif
           
            a := GetCursorContents( cArea, cScript, lShowProgress, bProgress )

         Else

            logfile( "SQLError.log", { nErr := ADSGetLastError( @cErr ), cErr, cScript } )

         ENDIF

         AdsCacheOpenCursors( 0 )

         if Select( cArea ) > 0 ; (cArea)->( DBCLOSEAREA() ) ;endif

         #ifndef PATHOLOGY
            //pathlabs and billing still built with xharb 0.99.6 uncompatible with newer
            //rddads using hb_adsGetWorkAreaPointer();
            #ifndef BILLING
               AdsCloseSQLStatement()
            #ENDIF
         #ENDIF
      Endif

   endif

Return a

//------------------------------------------------------------------------------
FUNCTION GetCursorContents( cArea, cScript, lShowProgress, bProgress )
   LOCAL aStruc, e
   LOCAL a       := {}
   LOCAL nCount   := 1
   LOCAL i, xTmp, oMeter
   LOCAL nLastRec := (cArea)->( lastrec() )

   IF lShowProgress .and. bProgress == NIL
      oMeter := PROGRESSBAR():New( 100, "Downloading Result Set", 1 )
      ACTIVATE DIALOG oMeter:oDlg NOWAIT ON INIT oMeter:oDlg:center( WndMain() )
      bProgress := { |nPercent| oMeter:Update( nPercent ) }
   ENDIF    
         
   IF Select( cArea ) > 0  .AND. (cArea)->( lastrec() ) > 0

      TRY
         aStruc   := (cArea)->( dbStruct() )
         a := array( nLastRec )

         WHILE !(cArea)->( eof() )

            a[ nCount ] := array( len( aStruc ) ) //{}
            afill( a[nCount], " " )
           
            IF lShowProgress  
               EVAL( bProgress, ( nCount / nLastRec ) * 100 ) //oMeter:Update( nCount )
               IF oMeter != NIL .and. oMeter:isCancel   ;BREAK   ;ENDIF
            ENDIF

            FOR i := 1 to len( aStruc )

               xTmp := NIL

               TRY
                  xTmp := ( cArea )->( FieldGet( i ) )
                   IF xTmp == Nil      ;xTmp := blank( aStruc[ i, DBS_TYPE ] )   ;Endif
               CATCH e
                  LogError( e, i, cScript )
               END

               a[ nCount, i ]:= xTmp

            NEXT i

            nCount++

            (cArea)->( dbSkip() )

         END

      CATCH e
         LogError( e, i, cScript )
         a := {}

      END

   ENDIF

   IF lShowProgress .and. bProgress == NIL ; oMeter:end() ;ENDIF
   
   aSize( a, nCount-1 )

   RETURN a

 

I hope you can write the code you need by looking at the ShowConnectedUsers function and ExecuteSQLScript() I show here. It is a start. Not the best. I hope to have the time to clean my TAdsQuery() class to contribute here.

Regards,


Reinaldo.

[reinaldocrespo]

One last piece of information on this same subject:

http://blog.advantageevangelist.com/2011/03/limiting-users-for-database.html

Regards,


Reinaldo.