![]() | ![]() | ![]() | ![]() |
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.
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)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 (a Visual Basic keyword).
"" (fourth parameter)"" (fifth parameter)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;"
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:
sas.iomprovider.1 indicates that ADO will use the IOM Data
Provider, which gets data through SAS IOM interfaces.The workspace argument is passed directly to the IOM server and is not processed by ADO.
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")
represents a table name.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.
obRecordset!pizza = "mushroom"
obRecordset.MoveNext
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.
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.
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
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
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
This sample assumes that you have a working knowledge of the Visual Basic and SAS programming languages and environments. To run this sample, your machine must contain the following software:
Microsoft ActiveX Data Objects 2.7 Library. This library is included with your Windows operating system.
Microsoft Visual Basic 6.0, including the MSFlexGrid ActiveX control.
The SAS System for Windows Release 8.2 or higher.
The SAS Integration Technologies client component for Windows.
This can be downloaded from the SAS Software Downloads Web site.
To install this sample:
Download the sample package and unzip it into the directory of your choice. The package contains the following files:
Using Windows Explorer, open the directory that contains the sample files.
Double-click the ADOIOMSample Visual Basic project file to start Visual Basic and load the sample.
In Visual Basic, you must reference the following type libraries:
Perform the following steps to run the sample and view its source code.
To view the source code for this sample, select Main (Main.frm) in the Project window and then
select View -> Code to see the Visual Basic code for this sample.
select View -> Object to see the Visual Basic form that provides the sample's user interface.
To run the sample, select Run -> Start.
The Visual Basic form that serves as this sample's user interface is a window with a MSFlexGrid control and a menu. The data from the SAS data set displays in the grid control. You can navigate through the data as well as modify the data set by clicking a cell and entering a new value for that cell.
| Type: | Sample |
| 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 |



