If you've worked with Visual Basic or VBScript before you know that you can create system objects in your code using the CreateObject() function. What many people don't know is that you can also design your own objects for use within your Commando Script projects using Classes. Using classes is nothing new to VB programmers, they've been around for years, but now you can use them within your Commando Script projects to create some powerful objects that allow you to use encapsulation to divide your projects into functional objects.
When you create a new Commando Message processor profile a 'default' Commando Script project template is automatically loaded for you. This template does nothing by itself, that is to say it doesn't do anything to actually process an HL7 message because we have no idea what you might want to do. What it does have are some examples of how to use some of the built-in objects (the oMSG and oUTILITIES objects) and it also has an example of a simple class that can be used to connect to a SQL Server database.
Public Class MS_SQLADO 'This class is a simple example of a Commando Script that will manage an ADO 'Connection to an MS SQL database. '------------------------------------------------------------------- 'Example usage: 'Dim oADO 'Set oADO = New MS_SQLADO 'With oADO ' .ServerName = "MyServer" ' .DatabaseName = "MyDatabase" ' .UserID = "SA" ' .Password = "sapassword" ' If Not .Connect() Then ' Msgbox .LastError ' Else ' Msgbox "Connected to " & .ServerName & " DB: " & .DatabaseName ' End If 'End With 'Set oADO = Nothing '-------------------------------------------------------------------- 'Private member Variables 'Server Connection Variables Private CONSTRING
Private ms_ServerName, ms_DBName, ms_UserID, ms_Password, mn_TimeOut 'The ADO Connection Object Private oADOConnection 'Miscellaneous Variables Private sLastError, bIsError, nLastError, bConnected '---------------------------------------------------------- 'Class Constructor '---------------------------------------------------------- Private Sub Class_Initialize() 'Automatically called when class is created. Place initialization code here CONSTRING = "PROVIDER=MSDataShape;driver={SQL Server}; " & _ "server=<SERVERNAME>;UID=<USERID>;PWD=<PASSWORD>;" & _ "Database=<DBNAME>;Connect Timeout=<TIMEOUT>"
ms_ServerName = "" ms_DBName = "" ms_UserID = "" ms_Password = "" mn_TimeOut = 60 bConnected = False ClearErrors Set oADOConnection = CreateObject("ADODB.Connection") End Sub '---------------------------------------------------------- 'Public Properties And Methods '---------------------------------------------------------- Public Function Connect() 'The Main Connection Function will open the ADO Connection 'and return true or false On Error Resume Next If bConnected Then oADOConnection.Close Set oADOConnection = Nothing Set oADOConnection = CreateObject("ADODB.Connection") If Err.Number <> 0 Then Err.Clear End If
oADOConnection.ConnectionString = ConnectionString oADOConnection.Open If Err.Number <> 0 Then SetError Err.Number, Err.Description Connect = False Err.Clear Else Connect = True bConnected = True Exit Function End If End Function '---------------------------------------------------------- Public Function OpenEditableRS(strSQL) 'Opens an ADO Recordset in LockBatchOptimistic mode (use Rs.UpdateBatch to update changes) Dim adoRS 'First Are they connected On Error Resume Next If Not bConnected Then SetError 666, "Connection to database not established" Set OpenEditableRS = Nothing Exit Function End If Set adoRS = CreateObject("ADODB.RecordSet") adoRS.Open strSQL, oADOConnection, adOpenDynamic, adLockBatchOptimistic If Err.Number <> 0 Then SetError Err.Number, Err.Description Set OpenEditableRS = Nothing Exit Function End If Set OpenEditableRS = adoRS End Function '---------------------------------------------------------- Public Function OpenReadOnlyRS(strSQL) 'Opens an ADO Recordset in ReadOnly mode Dim adoRS 'First Are you connected? On Error Resume Next If Not bConnected Then SetError 666, "Connection to database not established" Set OpenReadOnlyRS = Nothing Exit Function End If Set adoRS = CreateObject("ADODB.RecordSet") adoRS.Open strSQL,oADOConnection,adOpenDynamic,adLockReadOnly If Err.Number <> 0 Then SetError Err.Number, Err.Description Set OpenReadOnlyRS = Nothing Exit Function End If Set OpenReadOnlyRS = adoRS End Function '---------------------------------------------------------- 'properties needed to set up the connection Public Property Get ServerName() ServerName = ms_ServerName End Property Public Property Let ServerName(strServerName) ms_ServerName = strServerName End Property '---------------------------------------------------------- Public Property Get DatabaseName() DatabaseName = ms_DBName End Property Public Property Let DatabaseName(strDatabaseName) ms_DBName = strDatabaseName End Property '---------------------------------------------------------- Public Property Get UserID() UserID = ms_UserID End Property Public Property Let UserID(strUserID) ms_UserID = strUserID End Property '---------------------------------------------------------- Public Property Get Password() Password = ms_Password End Property Public Property Let Password(strPassword) ms_Password = strPassword End Property '---------------------------------------------------------- Public Property Get Timeout() Timeout = mn_Timeout End Property Public Property Let Timeout(nTimeOut) mn_TimeOut = nTimeOut End Property '---------------------------------------------------------- Public Property Get ConnectionString() Dim sBUFF sBUFF = CONSTRING 'Now do some replaces to build the connection string sBUFF = Replace(sBUFF,"<SERVERNAME>",ms_ServerName) sBUFF = Replace(sBUFF,"<DBNAME>",ms_DBName) sBUFF = Replace(sBUFF,"<USERID>",ms_UserID) sBUFF = Replace(sBUFF,"<PASSWORD>",ms_Password) sBUFF = Replace(sBUFF,"<TIMEOUT>",cStr(mn_Timeout)) ConnectionString = sBUFF End Property '---------------------------------------------------------- Public Property Get IsError() IsError = bIsError End Property Public Property Get LastError() LastError = sLastError End Property Public Property Get LastErrorNumber() LastErrorNumber = nLastError End Property '---------------------------------------------------------- Public Sub ClearErrors() bIsError = False nLastError = 0 sLastError = "" End Sub
'---------------------------------------------------------- 'Private Properties and Methods '---------------------------------------------------------- Private Sub SetError(nErrorNumber,sErrorMessage) bIsError = True nLastError = nErrorNumber sLastError = sErrorMessage End Sub '---------------------------------------------------------- 'Class Destructor '---------------------------------------------------------- Private Sub Class_Terminate() 'Automatically called when class is Destroyed. Place any cleanup code here Set oADOConnection = Nothing End Sub
End Class |