25630 - Using ADO Objects with a SAS IOM Server

Sample 25630: Using ADO Objects with a SAS IOM Server

Overview

This sample uses ADO objects to communicate with a SAS IOM Server. It illustrates how a program that is not a SAS application can access and manipulate data on a local SAS server. The program creates a SAS workspace (session) and then submits a SAS DATA step to create a SAS data set. The program then modifies the data using an ADO recordset. The resulting data set displays in a MSFlexGrid control.

Setting Up a Workspace

This example uses the CreateWorkspaceByServer method to create the Workspace object. The value that the method returns is a reference to the Workspace object and is assigned to the obSAS object variable.

Set obSAS = obWM.Workspaces.CreateWorkspaceByServer("", _
   VisibilityProcess, Nothing, "", "", xmlInfo)

The CreateWorkspaceByServer method has six parameters. The parameter values used in this sample are defined below. For additional information, see the class documentation for CreateWorkspaceByServer and Using the Workspace Manager.

"" (first parameter): specifies the name of the Workspace object. This value can be used at a later time to get this Workspace object from the WorkspaceManager object. Because this sample will not need to retrieve this Workspace object, a null value is specified.
VisibilityProcess: indicates whether the WorkspaceManager object should keep track of this Workspace object in the Workspaces collection. The value VisibilityProcess, indicates that the WorkspaceManager object should maintain a reference to the Workspace object (so that the SAS IOM Data Provider can get the Workspace object when necessary). Specifying a value of VisibilityNone instructs the WorkspaceManager object not to track the Workspace object.
After specifying a value of VisibilityProcess you should release the Workspace object from the WorkspaceManager object when you are finished using the Workspace object.
Nothing: specifies a ServerDef object that identifies the machine to which the workspace connects. Because the server and workspace are located on the same Windows machine you do not need a ServerDef object and therefore, this sample uses a value of Nothing. The workspace manager defaults to using the local machine if you specify a value of Nothing (a Visual Basic keyword).
"" (fourth parameter): specifies the user ID for the server machine. A null value is used because the workspace is on the same machine as the server; therefore, we do not need additional authorization to access the server.
"" (fifth parameter): specifies the password for the server machine. A null value is specified because the workspace is on the same machine as the server and you do not need additional authorization to access the server.
xmlInfo: the name of a variable that identifies a string that will be used for returning error information. The error information is presented in an XML table and it identifies the machine to which we are connected. The string is used to notify you when a connection attempt fails. If all attempts fail, the information is stored in err.description and the value of the xmlInfo parameter is ignored. Visual Basic does not allow you to use return parameters when a method fails.

Creating a SAS Data Set

When the workspace has been created you can then create a temporary SAS data set for the program to use. To create a SAS data set, use the Submit method of the SAS.LanguageService class to submit a SAS DATA step.

obSAS.LanguageService.Submit _
   "data a; " & _
   "do customer=1 to 100; " & _
   "quantity=customer*customer; pizza='Pepperoni'; output; " & _
   "end;" & _
   "run;"

Creating a Connection Object

Before this program will work with the SAS data set that is created, you must create an ADO Connection object (see the MSDN Online Library for more information about the ADO Connection object).

Dim obConnection As New ADODB.Connection

This Connection object identifies the Workspace object to use to invoke the Connection object's Open method (see the MSDN Online Library for more information about the Open method).

obConnection.Open "Provider=sas.iomprovider.1;SAS Workspace ID=" & _
   obSAS.UniqueIdentifier

The Open method has four optional parameters. The first of these parameters is ConnectionString. The sample code specifies two arguments in the connection string:

Provider: specifies the data provider for ADO to use. A value of sas.iomprovider.1 indicates that ADO will use the IOM Data Provider, which gets data through SAS IOM interfaces.
SAS workspace ID: specifies the unique identifier for the Workspace object. This argument is necessary because the sample code uses an existing Workspace object that is created by the CreateWorkspaceByServer method. The workspace ID is generated when the WorkspaceManager object creates the Workspace object.
The workspace argument is passed directly to the IOM server and is not processed by ADO.

Creating a Recordset Object

In order to get to the data in the SAS data set we created, you must create an ADO Recordset object to represent the data set (see the MSDN Online Library for more information about the ADO Recordset object).

Private obRecordset As New ADODB.Recordset

When the Recordset object is created you can use the Open method to associate the Recordset object with the SAS data set (see the MSDN Online Library for more information about the Open method).

obRecordset.Open "work.a", , dOpenDynamic, _
   adLockPessimistic, ADODB.adCmdTableDirect

You can create and open multiple Recordset objects using the same Connection object.

The Open method has five parameters. The parameter values used in this sample are defined below.

"work.a": specifies the data source file name. For this sample, the data source is the SAS data set that was created in the sample.
obConnection: specifies the object variable for the Connection object.
adOpenDynamic: indicates that the IOM Data Provider will use a dynamic cursor when it opens the Recordset object. Using a dynamic cursor enables you to traverse up and down through the recordset.
adLockPessimistic: indicates that the IOM Data Provider will use pessimistic locking when it opens the Recordset object. This type of locking enables you to write to the SAS data set that you created.
ADODB.adCmdTableDirect: indicates that the name value supplied in the first parameter ("work.a") represents a table name.

Modifying Your Data Through ADO

This sample shows that you can use an IOM server connection to modify the data that is in the SAS data set. To do so, follow these steps.

Change the value of the pizza field in the first record.
```
obRecordset!pizza = "mushroom"
```
Move to the next record.
```
obRecordset.MoveNext
```
Change the value of the pizza field in the second record.
```
obRecordset!pizza = "cheese"
```

When you run the sample, you will notice that the pizza field contains the values mushroom and cheese in the first and second records, respectively.

Populating Controls with the Data

Copy the data into the MSFlexGrid for display.

Define how many columns you have in the Recordset.
```
grdData.Cols = obRecordset.Fields.Count + 1
```
An extra column is added so that the row numbers can be displayed.
Highlight the Recordset Fields collection and copy the name of each column into the cells of the first row in the grid.
```
grdData.Row = 0
   For i = 0 To grdData.Cols - 2
   grdData.Col = i + 1
   grdData.Text = obRecordset.Fields(i).Name
  Next
```
Copy the cell values from the Recordset to the grid. This is accomplished in the LoadRecordset sub.
```
Private Sub LoadRecordset()
```
LoadRecordset also saves a bookmark for each row so that updates can be performed when the user clicks the grid.

Editing Cell Values

When the data is loaded into the MSFlexGrid control the sample code must accept updates from the user. When the user clicks a cell or begins typing, the sample code should display a box or other interface in which to store the updates. When the user presses enter, clicks a different cell, or begins scrolling, the new value should be saved in the Recordset.

To begin the process of editing the cell, call the BeginEdit sub. This sub is responsible for saving the row and column number for the cell being edited and for moving a TextBox over that cell to enable the user to enter a new value.

Private Sub BeginEdit()

When the user has finished editing a cell the EndEdit sub is called. This sub is responsible for copying the changes from the TextBox into the MSFlexGrid. It then calls SaveCurrentCell to save the change from the grid to the Recordset.

Private Sub EndEdit()

The SaveCurrentCell sub handles the actual transfer of data from the grid to the Recordset. The array of bookmarks created in LoadRecordset is used to position the Recordset on the appropriate row. The data is then copied. If the data cannot be saved then the current cell's colors are changed to indicate that an error occurred.

SaveCurrentCell

Cleaning Up

Before the program ends, make sure that you remove all references to the Workspace object so that the process does not continue to run and consume resources. You can remove these references during the Form's Unload event in the Form_Unload sub.

To remove a reference to the Workspace object from the WorkspaceManager object, call the RemoveWorkspaceByUUID method of the Workspaces collection.

obWM.Workspaces.RemoveWorkspaceByUUID obSAS.UniqueIdentifier

The RemoveWorkspaceByUUID method does not actually destroy the Workspace object. Use the Close method in the Workspace class to end the Workspace object and free any associated resources, such as files or memory.

obSAS.Close

Close the ADO Connection and Recordset objects that have been opened while running the sample.

If obRecordset.State = adStateOpen Then obRecordset.Close
   Set obRecordset = Nothing
If obConnection.State = adStateOpen Then obConnection.Close
   Set obConnection = Nothing

Date Modified:	2005-06-18 03:00:01
Date Created:	2005-06-07 17:54:32

Product Family	Product	Host	SAS Release
			Starting	Ending
SAS System	SAS Integration Technologies	Windows	n/a	n/a

Support

Sample 25630: Using ADO Objects with a SAS IOM Server

Overview

Explaining the Code

Setting Up a Workspace

Creating a SAS Data Set

Creating a Connection Object

Creating a Recordset Object

Modifying Your Data Through ADO

Populating Controls with the Data

Editing Cell Values

Cleaning Up

Further Reading

Software Requirements

Install the Sample

Run the Sample

Operating System and Release Information