Programming with Visual Basic
This section describes the typical steps that are necessary to develop applications using the SAS Integration Technologies client in the Visual Basic environment.
ActiveX components can contain numerous classes, each with one or more programming interfaces. These interfaces can have methods and properties. The components can also have many enumeration constants which are symbolic names for constants that are passed or returned over the interface. For name scoping and management, each application defines its own group of these definitions into a type library. The type library is used by programming languages to check the correctness of calls to the component and it is used by COM when it creates the data packet which conveys a method call from one Windows process to another. (This data packet creation is called marshalling.)
You must reference at least two type libraries to access IOM servers. One is for Base SAS software and the other is for the object manager. These type libraries get installed when you install Base SAS or when you install the SAS Integration Technologies client. However, before you can write Visual Basic code to call the Base SAS IOM interfaces, you must reference these type libraries from within your Visual Basic project.
To reference these type libraries from Visual Basic:
After referencing these libraries, you can use them immediately. Also, if you reopen the references dialog box, you will see that these libraries have been moved up to the top of the list with the other referenced libraries.
Each type library has a name, known as the library name, that is used in programming to qualify (or scope) all names (such as components, methods, constants, and so on) used within it. (This name is not necessarily the same as the name of the file containing the type library.) Visual Basic will look up the names in your program by going through the referenced type libraries in the order that they are listed in the references dialog box. If two type libraries contain the same name, then Visual Basic will get its definition from the first library in the list, unless the name is qualified with a library name.
The Base SAS IOM library name is "sas". You can use this to qualify any identifier defined in that library. For example, the Base SAS library has a FormatService component. If another library in your application has its own identifier with the same name, then you would reference the SAS component by using sas.FormatService.
The Visual Basic Development Environment
After the type library is referenced, its definitions become available to the Visual Basic Object Browser. From the Visual Basic development environment, the Object Browser component is typically available via a toolbar icon, a menu selection, or the F2 key.
The Object Browser can show all referenced type libraries at one time or can focus on any one of them. To focus on the Base SAS IOM library, select "SAS" from the drop-down list in the upper left. The left panel of the Object Browser will then show only the component classes, interfaces, and enumerations for SAS components. If you select a component (such as FileService) in the left panel, its methods and properties will be shown in the right panel. When you select an item in the right pane, more complete information (such as the parameters to pass to a method and a brief description of the method) are shown at the bottom.
Referencing a type library also activates Visual Basic Intellisense for all programming language names in the library. As you begin to type a name, like SAS.FileService, you will see the Visual Basic editor show you the list of possible names to complete your typing. As you code a method call, Visual Basic will show you each parameter that you need to provide.
For VB6 and later or Microsoft Office 2000 VBA or later, the Object Browser and the Visual Basic editor are connected to the Base IOM interface Help file. In the Object Browser, if you click the "?" button or press the F1 function key, you will see the Help page for the selected item. In the editor, if you press F1 with your cursor on a name, you will get Help for that name.
Working With Object Variables and Creating a Workspace
Your Visual Basic program's interaction with SAS begins by creating a SAS.Workspace object. This object represents a session with SAS and is functionally equivalent to a SAS Display Manager session or the execution of Base SAS software in a batch job. Using a workspace from Visual Basic requires that you declare an object variable in order to reference it. An example declaration is
Dim obSAS as SAS.Workspace
Note that this statement only declares a variable to reference a workspace. It does not create a workspace or assign a workspace to the variable. For creating workspaces, the SAS Integration Technologies client provides the object manager. The object manager is an ActiveX component that can be used to create SAS Workspaces, either locally or on any server that runs the SAS Integration Technologies product.
If the server is remote, you can create a workspace using either the server's DNS name (such as "unx03.abccorp.com") or a logical name (like "FinanceDept") given to you by your system administrator. See SAS Object Manager for more information about creating workspaces on remote servers.
If SAS is installed on your local PC and you want to create a workspace that runs locally, it is very easy to use the object manager to create the workspace and assign it to your object variable. The example below shows you how this is done:
Dim obObjectFactory As New SASObjectManager.ObjectFactory Dim obSAS As SAS.Workspace Set obSAS = obObjectFactory.CreateObjectByServer( "", True, Nothing, "", "")
By using the keyword new, you instruct COM to create a SAS Object Factory and assign a reference to it when the object variable (obObjectFactory in this case) is first used.
After you create the instance of the object factory, you can use it to create a workspace (SAS session) on the local machine. The object factory has a CreateObjectByServer method for creating a new workspace.
The first parameter to this method is the name of your choice. The name can be useful when your program is creating several objects and must later distinguish among them. The next parameter indicates whether to create the workspace synchronously. If this parameter equals true, CreateObjectByServer does not return until a connection is made. If this parameter equals false, the caller must get the created workspace from the ObjectKeeper.
The next parameter is an object to identify the desired server for the workspace. Because we are creating a workspace locally, we pass the Nothing (a Visual Basic keyword) to indicate that there is no server object. The next two strings are for a user ID and password. Again, we do not need these to create a local workspace. This method returns a reference to the newly created workspace.
To get error information about the connection, you can use the following code:
Dim obSAS As SAS.Workspace Dim obObjectFactory As New SASObjectManager.ObjectFactory obObjectFactory.LogEnabled = True Set obSAS = obObjectFactory.CreateObjectByServer("", True, Nothing, "", "") Debug.Print obSAS.Utilities.HostSystem.DNSName Debug.Print "Log:" & obObjectFactory.GetCreationLog(True, True)
For more information about logging errors, see Object Manager Error Reporting.
Note: Whenever you assign a reference to an object variable, the assignment uses the Set keyword. If you do not use Set, Visual Basic will try to find a default property of the object variable and set that default property. This is very different from assigning an object reference to the variable itself.
You can prove that the previous example program code works by instructing the server to print its Internet Domain Name System (DNS) name. The following program creates a SAS workspace on the local computer, then prints the DNS name, and finally, closes the newly created workspace.
Dim obObjectFactory As New SASObjectManager.ObjectFactory Dim obSAS As SAS.Workspace Set obSAS= obObjectFactory.CreateObjectByServer( "", True, Nothing, "", "") Debug.print obSAS.Utilities.HostSystem.DNSName ObSAS.Close
As you use other features of SAS, you will need additional object variables to hold references to objects created within your SAS Workspace. These are declared similarly. For example, if you want to assign SAS FILEREFs, then you might want an object variable to reference your workspace's FileService. The declaration would be:
Dim obFS as SAS.FileService
Such declarations never use the keyword new because doing so asks COM to create the object. Object variables for objects within the workspace will always reference objects created by the workspace, not COM. In fact, when you use new in a declaration, you will see that Visual Basic Intellisense provides a much shorter list of possibilities because it lists only those classes of objects that COM knows how to create.
Then, to use the obFS variable to hold a reference to the FileService for the workspace referenced by obSAS, the statement would be:
Set obFS = obSAS.FileService
Again, note that the assignment of an object variable must use the Set keyword.
Basic Method Calls
After you create an object variable and set it with an object reference, you can make calls against the object and access the object's properties.
The first example assigns a SAS libref using the IOM DataService.
' Create a workspace on the local machine using the SAS Object Manager Dim obObjectFactory As New SASObjectManager.ObjectFactory Dim obSAS As SAS.Workspace Set obSAS = obObjectFactory.CreateObjectByServer( "My workspace", Nothing, "", "") ' Get a reference to the workspace's DataService. Dim obDS as SAS.DataService Set obDS = obSAS.DataService ' Assign a libref named "mysaslib" within the new workspace ' (This is an example of a method call that returns a value) ' Note: you must have a "c:\mysaslib" directory for this to work Dim obLibref as SAS.Libref Set obLibref = obDS.AssignLibref( "mysaslib", "", "c:\mysaslib", "") ' Should print "mysaslib" Debug.Print obLibref.Name ' De-assign the libref; this is an example of a method call that does ' not return a value obDS.DeassignLibref obLibref.name ' Close the workspace; this is another example of a method call that ' does not return a value. ObSAS.Close
The previous example illustrates three method calls and two property accesses.
In Visual Basic, method calls that use a return value are coded differently from those that do not. The AssignLibref call returns a reference to a new Libref object. Because the method returns a value and that value is being used in this call, the method's arguments must be enclosed in parentheses. The calls to DeassignLibref and Close, on the other hand, do not return a value. (Neither of these methods can return a value.) Thus, no parentheses are used following the method name. This is true even for DeassignLibref which passes a parameter in the method call.
When passing parameters, you must first understand whether the parameter is for input or output (also known as ByVal and ByRef in Visual Basic). Unfortunately, the Visual Basic Object Browser does not provide this information. In many cases, understanding the role of the parameters will make it obvious. For example, the library name string passed to DeassignLibref is used by that method to know which libref to de-assign but the method does not update the parameter. Thus, it is an input parameter. If you are not sure which type of parameter is required, then you can consult the Help or documentation.
Input parameters can be passed by a constant value (such as mysaslib for a string parameter or 0 for a numeric parameter) or an expression. They can also be passed using a variable whose value is taken as input to the method. This technique is used in the DeassignLibref call in the example. Output parameters require the use of variables that will be updated at the time the call returns.
Understanding the data type of the parameter is just as important as understanding the direction. The data type is listed in the Object Browser. If the provided constant, variable, or expression has the same type as the parameter, then no conversion is necessary. Otherwise, Visual Basic might try to convert the value. If you do pass a different type than the method actually defines for that parameter, make sure that you understand Visual Basic's data conversion rules.
The parameters in IOM methods have a range of data types. Many are standard types such as String, Long, Short, Boolean and Date. Others are object types defined by a type library (such as SAS.Libref in the previous example). Most parameters are designed to accept or return a single type of object. In these cases, it is best to use an object variable of that specific type. It is possible, however, to use variables declared with the generic object type Object. In some cases, methods can return more than one type of object, depending on the situation. For example, the GetApplication method for a SAS Workspace might return different types of objects (based on the application name that was requested). It is declared to return the generic object type.
When a parameter can take some fixed set of integer values, its declaration will use an enumeration defined in the type library. For example, the Fileref:OpenBinaryStream method takes a parameter indicating the open mode of the stream. The type of this parameter is SAS.StreamOpenMode. In the current version of Visual Basic, you cannot declare a variable of this type. You must use a Long instead. The declaration is still important because it tells you the set of possible values and provides a constant for each. For example, if you want to open a stream for writing, you would pass a constant of SAS.StreamOpenModeForWriting.
Some Visual Basic procedures and subroutines take optional parameters. When calling such routines, you often pass the parameters by specifying their name (such as "color:="Red"). This feature of Visual Basic is not used by SAS IOM methods because it does not work naturally with other types of client programming environments including Visual C++. Therefore, it is best to simply list parameters in order without showing their names.
Many objects have properties that can be obtained simply by naming the property. Libref objects, for example, have a Name property, which is used in the previous example. Some properties are read-only, but others can also be set. Such properties can be set using a normal assignment statement. Keep in mind, however, that if the property type is an object type, then you will need to use Set with your assignment statement (as described in the previous section).
Because SAS servers will often be located on a platform that is remote from the machine running your Visual Basic program, much better performance is achieved by keeping the number of function calls to a minimum. This means that many IOM methods have a large number of parameters and accept arrays for those parameters. Visual Basic Intellisense technology is very helpful for keeping track of parameters as your code them. The next section describes how to pass arrays in Visual Basic method calls.
Passing Arrays in IOM Method Calls
When SAS generates a listing, it might contain thousands of lines. If the IOM calls to read the listing returned only one line at a time, then many thousands of calls would be needed to fetch all of the output. In order to get better performance in situations like this, IOM methods make heavy use of arrays.
By returning an array of lines, the FlushListLines() method can potentially return all LIST output in one call. Similar situations occur in many other places in IOM including reading and writing files, getting and setting options and listing SAS librefs and filerefs. It is important, therefore, to understand when arrays are being used and how to declare and pass them.
Visual Basic arrays can be fixed-size or dynamic. An array dimensioned with a size is fixed-size. An array dimensioned with no size (empty parentheses) or declared with the Redim statement is dynamic. For array output parameters you must use dynamic array variables because the size that SAS will return is not known when the array variable is declared.
The following example lists all librefs in the workspace.
' Create a workspace on the local machine using the SAS Object Manager Dim obObjectFactory As New SASObjectManager.ObjectFactory Dim obSAS As SAS.Workspace Set obSAS = obObjectFactory.CreateObjectByServer( "My workspace", True, Nothing, "", "") Dim obDS as SAS.DataService Dim vName as variant ' Declare a dynamic array of strings to hold libnames Dim arLibnames() as string Set obDS = obSAS.DataService ' Pass the dynamic array variable to "ListLibrefs"; upon return, ' the array variable will be filled in with an array of strings, ' one element for each libref in the workspace obDS.ListLibrefs arLibnames ' Print each name in the returned array For Each vName in arLibnames debug.print vName Next vName ' Print the size of the array debug.print "Number of librefs was: " & Ubound(arLibnames)+1 ObSAS.Close
In the object browser, you can tell that the ListLibrefs names parameter is an array of strings because it is shown as follows:
names() as string
The empty parentheses indicate an array. A few of the array parameters in IOM require a two-dimensional array. The Object Browser does not distinguish the number of dimensions. You must consult the class documentation to determine the number of dimensions in an array.
After the ListLibrefs call returns, there will be one array element for each libref assigned in the workspace. The For Each statement can be used to iterate through these elements. You must use variant as the type of the loop control variable.
In many cases, you will need to know the size of the returned array. Visual Basic allows arrays to be created with a particular lower and upper bound in each dimension In order to allow better compatibility with other client programming languages, all IOM calls require that the lower bound be zero. Input arrays will not be accepted if their lower bound is not zero. Output arrays will always be returned with a lower bound of zero.
The size of the array can be determined by checking its upper bound. For a one-dimensional array, you can get the upper bound by passing the array name to the Ubound function. Because the array is zero-based and Ubound returns the number of the last element, you must add one to get the size of the array.
You can get the Ubound for each dimension of a two-dimensional array by passing a dimension index. The following example illustrates this technique:
Dim table() Redim table(5,2) ' 6 rows (0 through 5) and 3 columns (0 through 2) debug.print "Number of rows: " & Ubound(table, 1)+1 debug.print "Number of columns: " & Ubound(table, 2)+1
When passing input arrays, you can pass a fixed size array if you know the size in advance. The following code provides an example:
' Create a workspace on the local machine using the SAS Object Manager Dim obObjectFactory As New SASObjectManager.ObjectFactory Dim obSAS As SAS.Workspace Set obSAS = obObjectFactory.CreateObjectByServer( "My workspace", True, Nothing, "", "") Dim obLS as SAS.LanguageService ' Declare a fixed-size array of strings to hold input statements Dim arSrc(2) as string ' Declare a dynamic array of strings to hold the list output Dim arList() as string ' These arrays will return line types and carriage control Dim arCC() as SAS.LanguageServiceCarriageControl Dim arLT() as SAS.LanguageServiceLineType Dim vOutLine as variant arSrc(0) = "data a; x=1; y=2;" arSrc(1) = "proc print;" arSrc(2) = "run;" Set obLS = obSAS.LanguageService ObLS.SubmitLines arSrc ' Get up to 1000 lines of output ObLS.FlushListLines 1000, arCC, arLT, arList ' Print each name in the returned array For Each vOutLine in arList debug.print vOutLine Next vOutLine ObSAS.Close
The number of input statements are known in advance, so the arSrc array variable can be declared as a fixed size. (A dynamic array could also have been used.)
For some methods, you might want to pass an input array with no elements. Unfortunately, Visual Basic does not have syntax for creating either a fixed-size array or a dynamic array with zero elements.
To work around this deficiency in Visual Basic, IOM methods allow you to pass an uninitialized dynamic array variable. When you do this, it is the same as if you had created an array with no elements.
Visual Basic does handle output arrays with no elements properly. When an array has no elements, the Ubound() function will return -1 and the For Each statement will not execute the body of the loop.
Older versions of Visual Basic (such as VBA in Microsoft Office 97) do not support defining variables as enumeration types. For these older versions, you have to define them as type long. From the previous example, consider the statement:
Dim arCC() as long, arLT() as long
Here, the variables arCC and arLT are being used as arrays of LanguageServiceCarriageControl and LanguageServiceLineType, respectively. However, because this program was written to execute in a Visual Basic for Applications (Microsoft Office 97) environment, the variables are defined as arrays of long integers.
For newer versions (such as Visual Basic 6) you need to use enumeration types. To execute this program in a VB6 environment, the line would be changed to:
Dim arCC() as LanguageServiceCarriageControl Dim arLT() as LanguageServiceLineType
Note that the enumeration constants themselves are available for use in both environments. Given that arCC and arLT are defined appropriately for the environment, the following statements are valid in both the old and new versions.
arCC(0) = LanguageServiceCarriageControlNormal arLT(0) = LanguageServiceLineTypeSource
You should use the Workspace.Close method to close a workspace when you are finished with it. This will also delete all objects within the workspace. For some types of objects, such as streams, you can be finished with the object long before you are finished with the workspace. These objects typically have their own Close method that removes the object and performs other termination processing (such as closing the file against which the stream is open). The lifetime of objects that do not have a Close (or similar) method is managed by the workspace in which the object is created.
In Visual Basic, you can release the reference held by an object variable by assigning the keyword Nothing to that variable. The syntax is
ObStream = Nothing
Using this technique, it is possible to make Visual Basic release its references to an object. Doing so does not, however, delete the object because the workspace still references the object.
As a special case, if your program releases its references to all of the objects on a SAS server, then the server will delete the workspace and all of the objects in it. This behavior is implemented to prevent excess SAS processes from being left on a machine when clients fail to close their workspaces properly. If a client program terminates abruptly or is killed by the user from the Task Manager, then COM notifies SAS of this at a later time. In current versions of COM this notification takes approximately six minutes.
Method calls sometimes fail. When they do, Visual Basic raises an error condition. If you want to write code that responds to error conditions, then you should code On Error Goto Next after each call. If you want to centralize the code that responds to error conditions, then code an On Error Goto with a label and place your error handling code at the label.
When an error occurs, COM and Visual Basic store the error in the err predefined variable of the type VBA.ErrObject. The two most important fields in this variable are Err.Number and Err.Description. Err.Number can be used by your program logic to determine what kind of error occurred.
Err.Description provides a text description of the error. It can also provide important details about the error. For example, in the case of an error in opening a file, the description will provide the name of the file that could not be opened. SAS 9 servers return the error description in XML format. This allows multiple messages from the server to be packed into one string. The format is:
<Exceptions> <Exception> <SASMessage>ERROR: First message</SASMessage> </Exception> <Exception> <SASMessage>ERROR: Second message</SASMessage> </Exception> </Exceptions>
Use an XML parser to break the XML into individual messages to display to a user. You can use the V8ERRORTEXT objectserverparm to suppress this XML.
Different errors are assigned different codes. IOM method calls can return many different codes. The code that they return is listed in various enumerations whose names end in "Errors". For example, the IOM DataService defines an enumeration called DataServiceERRORS in which the constants for all of the possible errors returned by the DataService are defined. See the documentation and Help for more information about the common errors for a particular call. In addition to errors that listed explicitly for the method, SAS.GenericError can always be raised.
Some IOM objects can generate events. For example, The LanguageService generates the events DatastepStart, DatastepComplete, ProcStart, ProcComplete, SubmitComplete and StepError. In the Object Browser, you can recognize objects that generate events because the event procedures are marked using a lightning bolt icon.
Your Visual Basic program can implement procedures that receive these events if you declare the interface using the WithEvents keyword.
Dim WithEvents obLS As SAS.LanguageService
This declaration must be outside any procedure.
After you have declared an interface to receive events, the Visual Basic development environment will provide empty definitions for the event procedures. The following code segment is a definition that is provided for the ProcStart event, along with a line that has been added to print a debug message tracing the start of the procedure.
Private Sub obLS_ProcStart(ByVal Procname As String) Debug.Print "Starting PROC: " & Procname End Sub
Note that the event procedure name is of the form Object_Event.
In your routine that initializes your workspace variable, you should initialize the LanguageService variable using a statement such as:
set obLS = obSAS.LanguageService
After this initialization has occurred, your events procedures will be called whenever SAS fires an event.