| Home | Previous Lesson: Starting/Stopping DPB Server Next Lesson: Connecting from Client App to Middle Tier App |
Now, we need to write code to display connected users information in dw_users DataWindow. Declare the following function at w_pms_server.
// Function: of_RefreshUserInfo()
// Access: Private
// Return Value: Integer
// Arguments: None.
ConnectionInfo lci_ConInfo[]
Connection lcn_Connection
int li_RowNo, li_rc
long ll_Rows
// Create a connection object.
lcn_Connection = CREATE Connection
// Set connection object properties.
lcn_Connection.Application = gtrp_transport.Application
lcn_Connection.Driver = gtrp_transport.Driver
lcn_Connection.Location = gtrp_transport.Location
lcn_Connection.UserId = "DBA"
lcn_Connection.Password = "SQL"
// Connect to the server.
lcn_Connection.ConnectToServer ()
// Check for errors.
IF lcn_Connection.ErrCode <> 0 THEN
MessageBox("Error", string(lcn_Connection.ErrCode)&
+ ": " + lcn_Connection.ErrText, &
StopSign!,OK!,1)
DESTROY lcn_Connection
Return
END IF
// Check the users information.
ll_rows = lcn_Connection.GetServerInfo(lci_ConInfo)
// We are done. Disconnect from the server.
lcn_Connection.DisconnectServer()
DESTROY lcn_Connection
dw_users.Reset()
For li_RowNo = 2 to ll_rows
// The following IF condition is filtering connections
// that are connected to monitor connections.
// NOTE: Your connection is ALWAYS the first one
// in the ConnectionInfo structure array.
dw_users.InsertRow(0)
if lci_ConInfo[li_RowNo].busy then
dw_users.object.busy[li_RowNo - 1] = "Yes"
else
dw_users.object.busy[li_RowNo - 1] = "No"
end if
dw_users.object.connecttime[li_RowNo - 1] = &
lci_ConInfo[li_RowNo].connecttime
dw_users.object.lastcalltime[li_RowNo - 1] = &
lci_ConInfo[li_RowNo].lastcalltime
dw_users.object.callcount[li_RowNo - 1] = &
lci_ConInfo[li_RowNo].callcount
dw_users.object.clientid[li_RowNo - 1] = &
lci_ConInfo[li_RowNo].clientid
dw_users.object.location[li_RowNo - 1] = &
lci_ConInfo[li_RowNo].location
dw_users.object.userid[li_RowNo - 1] = &
lci_ConInfo[li_RowNo].userid
dw_users.object.connectuserid[li_RowNo - 1] = &
lci_ConInfo[li_RowNo].connectuserid
dw_users.object.connectstring[li_RowNo - 1] = &
lci_ConInfo[li_RowNo].connectstring
Next
Return
In the above function, we are creating an instance of a connection variable and populating with values�basically same values as transport object�and connecting to the server. Once connected, we are calling GetServerInfo() function�available at connection object�to get connected users information. This function returns an array of ConnectionObject objects. We are disconnecting immediatley after the GetServerInfo() function call, however, GetServerInfo() lists this connection also because this connection is active at that time. Please note that, this connection will get administration privilege�look in the Application Object's ConnectionBegin event�and that's why GetServerInfo() returns all users info, otherwise, it will return only your connection information. Then we are reading properties of each ConnectionInfo object and populating the dw_users DataWindow. Please note that, we are not displaying this connection�connected to pull user info.
We are done with function declaration. Now call this function from cb_refresh CommandButton's clicked event.
Parent.of_RefreshUserInfo()
Remember we are firing the timer event every 60 seconds. So, call this function from window's timer event also as shown below.
This.of_RefreshUserInfo()
The following code prevents closing the window when the server is running. It prompts the user to stop the server before (s)he close the server window.
// Event: CloseQuery
// Object: w_pms_server window
If cb_start_or_stop_server.text = "&Stop Server" Then
MessageBox("Warning!", "Please Stop the server before " + &
"Closing the Server Monitor Window!", StopSign!, OK!,1 )
Return 1 // Stop window from closing.
End If
Return 0 // Everything is Okay. Let the window close.
We are displaying all connected users information. Don't you think it is a good idea to allow the administrator to disconnect the selected user or all users by invoking a popup menu on the dw_users DataWindow?
Paint a menu m_ServerMenu as shown below.
Write the following code for Clicked event of Disconnect and Disconnect All menu options.
// Event: Clicked
// Object: m_disconnect of m_ServerMenu menu
ParentWindow.Dynamic Function of_Disconnect('CURRENT')
// Event: Clicked
// Object: m_disconnect_all of m_ServerMenu menu
ParentWindow.Dynamic Function of_Disconnect('ALL')
We need to declare an instance variable for this menu in w_pms_server.
m_ServerMenu im_dw
We need to create it in the window's open event. Append the following code to the w_pms_server open event
im_dw = CREATE m_ServerMenu
We need to destroy it in the window's close event.
DESTROY im_dw
We need to display the popup menu at the mouse pointer position whenever user clicks with the right mouse button on dw_users DataWindow.
// Event: rButtonDown
// Object: dw_users DataWindow in w_pms_server window
im_dw.m_users.m_Disconnect.Enabled = ( Row > 0 )
im_dw.m_users.m_DisconnectAll.Enabled = ( This.RowCount() > 0 )
im_dw.m_users.PopMenu(Parent.PointerX(),Parent.PointerY())
Return 0
Observe parenthesis�plays important role here�around the expression in the right hand side. Those expressions return TRUE or FALSE and enable or disable menu options. The first line is equal to:
If Row > 0 Then
im_dw.m_users.m_Disconnect.Enabled = True
Else
im_dw.m_users.m_Disconnect.Enabled = False
End If
I know the code is cryptic, but is compact and also you will get some exposure to this kind of coding and won't be surprised while debugging some one's crazy code. Remember calling of_Disconnect() function from the menu m_ServerMenu, but we did not get any compilation errors, because, we are using DYNAMIC key word. Now, let's define that function.
We are basically calling RemoteStopConnection() function that is available at the connection object. If the argument is a user id, then we are disconnecting the specified user�actually we are not using this feature, however, code is there. If the option is CURRENT then we are finding the Clientid from the current row of the DataWindow and disconnecting him/her. If 'ALL' argument is passed, then we are disconnecting all users one at a time.
How do you know that the connection is your connection? For example, there are 10 user connections and additionally you opened 3 more connections to the server from different terminals with the same user id and password. When you connect one more time to list connected users information, you will be connecting to the server and disconnecting immediately. That means by that time you display the connection information in the DataWindow, your connection was already disconnected from the server. Don't you think it is a good idea not to display the connection information that used to get connected users list. The question here is how do you identify your connection�the one used to get the connection information�from your other three connections? The answer is, the connection that is making GetServerInfo() function is always the first one in the array.
If you want to disconnect connections automatically when the connection is idle for more than a specified time, do you know what you should do? Check the last call time in the ConnectionInfo structure array and act on it.
One more important thing to note here is that the Userid member of the ConnectionInfo structure refers to the Userid that you used to log on to your MS-Windows 95/NT system. The Clientid member in the ConnectionInfo structure contains the user id you passed to the ConnectionBegin event using Connection object's Userid property.
Add the following line to load the server info when you start the application.
// Event: Open
// Object: w_pms_server window
of_GetServeroptions()
Finally, we need an application for the server. So, create an application object pms and save it in product-server.pbl. open w_pm_server from Application Open event.
// Event: Open
// Object: pms Application Object located in product-server.pbl
Open( w_pms_server )
As explained earlier, every time a client connects to the server, ConnectionBegin event is fired at the server. In that event, you need to assign privileges to the client. This event has three arguments, userid, password and ConnectString. You may want to validate userid and password arguments and also use them to connect to the database server. CommandString argument is similar to the argument available to the Application object's open event in a regular PowerBuilder application. If you pass a command line argument to the server application at startup, it will be available to the server in its application object open event. However, when a client connects to the server, application object's open event is not fired, instead ConnectionBegin event is fired.
// Event: ConnectionBegin
// Object: Application Object located in product-server.pbl
CONSTANT STRING LS_REGKEY = "HKEY_LOCAL_MACHINE\Software\PMS\dbinfo"
String ls_Temp, ls_privilege
Integer li_Pos
SQLCA.UserID = UserID
SQLCA.LogID = UserID
SQLCA.LogPass = Password
SQLCA.DBPass = Password
RegistryGet( LS_REGKEY, "DbParm", SQLCA.DbParm )
RegistryGet( LS_REGKEY, "Database", SQLCA.Database )
RegistryGet( LS_REGKEY, "DBMS", SQLCA.DBMS )
CONNECT USING SQLCA ;
If SQLCA.SQLCode <> 0 Then
RETURN NoConnectPrivilege!
END IF
SELECT privilege INTO :ls_privilege
FROM dpb_privileges
WHERE userid = :SQLCA.USERID;
CHOOSE CASE Upper(ls_privilege)
CASE "ADMINISTRATION"
RETURN ConnectWithAdminPrivilege!
CASE "CONNECTION"
RETURN ConnectPrivilege!
CASE ELSE
RETURN NoConnectPrivilege!
END CHOOSE
Return NoConnectPrivilege!
Please note that the datatype of the return value for this event is ConnectPrivilege, which is an enumerated datatype. There are three types of privileges available, ConnectPrivilege!, NoConnectPrivilege!, and ConnectWithAdminPrivilege!. We are assigning administrative privilege if the userid is 'Administrator', just connect privilege otherwise. We just hard-coded the checking to make this example simple, in real life you may want to validate these against a database.
Now the server is ready to run. Make sure you put the following code also to make it perfect code�I know the PowerBuilder 7.0 does automatic garbage collection, but, I don't depend on that.
// Event: Close
// Object: w_pms_Server window
// Destory the transport because the window which
// is the main visible component is being closed.
DESTROY gtrp_transport
This completes building the server application and is the time to create a project object and generate executable files.
| Home | Previous Lesson: Starting/Stopping DPB Server Next Lesson: Connecting from Client App to Middle Tier App |