In this recipe, you learn how to prepare the IOM Data Provider to process user-written formats and informats. Sample code for the ADO recipe is included.
| Applies to: | SAS IOM Data Provider |
| Implement using: | ADO or OLE DB |
You can use the SAS FORMAT procedure to create your own formats and informats, and you can use the SAS IOM Data Provider to open data sets using those formats and informats. The only requirement is that the server must have access to the catalog that contains your defined formats and informats.
The following example shows how to read a data set named books that uses formats that are contained in a catalog named formats. The data set is located in a directory named c:\storage. The catalog is located in a directory named c:\public.
Note: For information about how to open an ADO Connection object, see Opening an ADO Connection Object.
' Open a connection
Dim obConnection As ADODB.Connection
Set obConnection = New ADODB.Connection
obConnection.Provider = "SAS.IOMProvider.1"
obConnection.Properties("Data Source") = "_LOCAL_"
obConnection.Open
Dim obRecordset As ADODB.Recordset
Set obRecordset = New ADODB.Recordset
Set obRecordset.ActiveConnection = obConnection
' Use the formats persisted on the data set
obRecordset.Properties("SAS Formats") = "_ALL_"
' Assign a libname for the formats catalog
obConnection.Execute ("libname library 'c:\public' ")
' Assign a libname for the data set
obConnection.Execute ("libname mylib 'c:\storage' ")
' Open the recordset
obRecordset.Open "mylib.books", , adOpenDynamic, adLockReadOnly, adCmdTableDirect
Note: Currently, user-written formats cannot be processed in a thin-client space, which is why the IOM provider can process user-written formats but the local and SAS/SHARE providers cannot. The IOM provider delegates all format processing to the server side; however, both the local and SAS/SHARE providers do all format processing on the client side.
The example shown in the Overview uses formats that are defined in a catalog named formats. If you define the libref library to point to the location where this catalog is stored, your IOM Workspace Server will be able to access your user-written formats when you process data sets using either ADO recordsets or OLE DB rowsets.
Note: See Assigning a Libref to Use with the IOM Provider for more information about assigning librefs in an IOM workspace.
You will get a fatal error if you attempt to open an ADO recordset in which the underlying data set contains user-written format information that is not immediately accessible to your IOM workspace.
Note: This type of error is generated even if you are not attempting to use the formats when reading the data.
By default, you will get an error if you attempt to use either the local or the SAS/SHARE provider to perform a row I/O operation with a user-written format or informat. For example, if you try to read a row and apply a customized format, the attempt will fail. Likewise, an update or add row operation will fail if a user-written informat is associated with any column in the row.
You can enable processing to continue without error by changing the value of the "SAS Format Error" recordset property (the OLE DB property name is DBPROP_SAS_FMTERR). This property controls how the local and SAS/SHARE providers process format and informat names that cannot be resolved. By default, the value of this property is True, which causes unknown formats and informats to be flagged as errors. If you set this property to False when opening a recordset, the default format or informat (W.D for numeric columns and $CHAR. for character columns) will be applied when an unknown format or informat is encountered.