Chapter Contents
Previous
Next
|
Chapter Contents |
Previous |
Next |
| LIBNAME Statement: OLE DB Specifics |
| Valid: | in a DATA or PROC step |
Syntax |
|
LIBNAME libref
SAS/ACCESS-engine-name
SAS/ACCESS-engine-connection-options
< SAS/ACCESS-LIBNAME-options>; |
OLEDB. SAS/ACCESS
engines are implemented differently in different operating environments. The
engine name is required.Some SAS/ACCESS LIBNAME options have the same names as SAS/ACCESS-engine data-set options. When you specify an option in the LIBNAME statement, it applies to the particular data source (which is accessed by the libref). A SAS/ACCESS data set option applies only to the data set on which it is specified. If a like-named option is specified in both the SAS/ACCESS LIBNAME statement and after a data set name (which represents a data source table or view), the SAS System uses the value that is specified after the data set name. For more information, see SAS/ACCESS LIBNAME Statement .
| Details |
The LIBNAME statement associates a libref with a SAS/ACCESS engine in order to access a data source. The SAS/ACCESS engine enables you to connect to a particular data source and, therefore, to specify a DBMS table or other file name in a two-level SAS name.
For example, in
mydblib.q2_employees,
mydblib is a SAS
libref that points to a particular data source, and
q2_employees is a DBMS table name. When you
specify
mydblib.q2_employees
in a DATA step or procedure, you dynamically access that DBMS table. Version
8 the SAS System supports reading, updating, creating, and deleting DBMS tables.
(However, it does not support altering a database by adding or deleting a
column. For these tasks, you must use the DBMS-specific SQL statements.)
See SAS/ACCESS LIBNAME Statement for more information about arguments and options that you can use in the LIBNAME statement.
| Connecting to OLE DB |
There are many SAS/ACCESS connection
options available when you use the SAS/ACCESS interface to OLE DB, and these
options have several interactions. Basically, there are two separate ways
to connect to a data source: using OLE DB Services or connecting directly
to the provider. Each has its advantages and its limitations, as described
here.
Often the fastest and easiest way to connect to a data provider is by using OLE DB Services. Examples of data providers include Microsoft Access, Microsoft SQL Server, or ORACLE. For each of the listed providers, the data source would be a relational database that contains database objects, such as tables and views, whose data you could then access in your SAS programs. OLE DB accepts other sources of data as well, including email files and text files.
OLE DB Services provides some performance optimizations and scaling features, including resource pooling. It also displays dialog windows to prompt you for connection information, where the dialogs are consistent regardless of which provider you use.
As described in Connecting Directly with the Data Provider, you can specify the SAS/ACCESS connection options, PROVIDER= and PROPERTIES=, to connect to a data provider and its data sources. In PROPERTIES=, you supply the connection information; for example, if the data source were a relational database, you might supply the user ID, password,and schema. The default action for SAS/ACCESS software is to use OLE DB Services, so you can omit the OLEDB_SERVICES= option and still get the benefits of the Services.
Using OLE DB Services enables you to be prompted for the provider name and properties during an interactive SAS session. To be prompted, submit your libref and a SAS/ACCESS LIBNAME statement for OLE DB:
libname mydblib oledb;
In this case, the SAS/ACCESS engine for OLE DB assumes that you want to be prompted (and therefore sets PROMPT=YES). It directs OLE DB Services to display the prompting dialog windows so that you can supply the provider name and connection information for the data source. If you choose, you can specify the PROPERTIES= option in the LIBNAME statement, supply some of the connection information, and still be prompted to supply the rest of the information in the dialog windows.
OLE DB Services Dialog Window, First Tab shows you the initial prompting dialog window and some of the providers from which you can choose:
OLE DB Services Dialog Window, First Tab
![[IMAGE]](./images/01329721.gif)
OLE DB Services Dialog Window, Second Tab shows where you enter the data source name and other server information:
OLE DB Services Dialog Window, Second Tab
![[IMAGE]](./images/01329722.gif)
After you have been
prompted for the connection information and made
a successful connection, the SAS/ACCESS engine for OLE DB and OLE DB Services
enable you to retrieve the connection information and re-use it in batch jobs
and automated connections. For details, see the SAS/ACCESS connection option, INIT_STRING=.
See also the option, OLEDB_SERVICES=.
If you know the data provider's name and properties, you can connect directly to the provider and its data source(s). Examples of data providers include Microsoft Access and ORACLE, where the sources of data that you might access could be tables in a relational database, email files, or text files.
OLE DB does not have an administrator, like the ODBC Data Source Administrator, to help you create and list data sources. Therefore, you have to determine the data providers and the properties that they accept. To make the connection to the provider's data source, SAS requires connection information, such as the user ID, password, schema, and server name.
If you connect directly to the provider, your minimum set of SAS/ACCESS connection options are PROVIDER=, and OLEDB_SERVICES=NO, assuming that you have default values set for the provider's properties. (The default action for OLEDB_SERVICES= is YES, so you have to indicate that you do not want to use the OLE DB Services. For more information about OLE DB Services, see Connecting with OLE DB Services.)
After you connect to your provider once (for example, by using OLE DB Services or through a prompted session), you can use a special OLE DB query called PROVIDER_INFO() to make subsequent non-prompted connections easier. You can submit this special query as part of a PROC SQL query in order to display all of the available provider names and properties. For an example, see Examples.
If you are connecting to MS SQL Server and specifying the SAS/ACCESS option BCP=YES, then you must connect directly to the provider.
If you know only the provider name and you are running an interactive SAS session, you can be prompted for the provider's properties. Specify PROMPT=YES to direct the provider to prompt you for properties and other connection information. The provider displays its own dialog window for prompting; these dialogs vary from provider to provider.
In addition to the SAS/ACCESS connection options already listed, you can specify the SAS/ACCESS options, COMPLETE= and REQUIRED=. Use these options to try to connect to the data source using the information that you have already supplied with a PROPERTIES= options. If more information is needed to make the connection, the dialog windows prompt you for it.
If you run SAS in a batch environment, you must supply the provider name and all of the connection information in the PROPERTIES= option. In this case, you specify only the following SAS/ACCESS connection options: PROVIDER=, PROPERTIES=, and OLEDB_SERVICES=NO.
The following table summarizes how you can use the SAS/ACCESS-engine connection options:
| Minimum options required | All options allowed | |
|---|---|---|
| Connect through OLE DB Services | none | provider=, oledb_services=yes |
| properties=, provider_string=, init_string=, prompt= | ||
| Connect directly to OLE DB | provider= * , oledb_services=no | provider=, oledb_services=no, properties=, provider_string=, prompt=, complete=, required= |
| *You can omit the PROPERTIES= option if you have default properties set for your provider. | ||
| SAS/ACCESS-Engine Connection Options |
The SAS/ACCESS-engine connection options are as follows:
| COMPLETE= | |
| INIT_STRING= | |
| OLEDB_SERVICES= | |
| PROMPT = | |
| PROPERTIES= | |
| PROVIDER= | |
| PROVIDER_STRING= | |
| REQUIRED= |
Default value: NO.
If you specify COMPLETE= NO or do not have enough information to connect, you are prompted for the connection options with a dialog window.
COMPLETE= is used only when you connect directly to OLE DB using the PROVIDER= and OLEDB_SERVICES=NO in a SAS/ACCESS LIBNAME statement.
See also these options: PROPERTIES=, PROMPT=YES, PROVIDER=, OLEDB_SERVICES=.
Default value: option is not set. However, if you specify the option
with a null argument, (
INIT_STRING=""), OLE DB connects to ODBC with a default set of properties. See the
Microsoft OLE DB documentation for more information about these default values.
In this example, you submit the basic connection information so that you will be prompted for the rest of the information. Using OLE DB Services is the default value, so you can omit the OLEDB_SERVICES= option.
libname mydblib oledb;
Through dialog windows, OLE DB Services prompts you for the provider and properties' values. The advantage of being prompted is that you do not need to know any special syntax to set the properties'. Prompting also enables you to set more options than you might when connecting directly to the provider (and not using OLE DB Services).
After connecting to the data source, the SAS/ACCESS engine for OLE DB returns the initialization string to the SYSDBMSG macro variable. To write this string to the SAS log immediately after connecting to the data source, submit the following:
%put &SYSDBMSG; OLEDB: Provider=SQLOLEDB;Password=dbmgr1;Persist Security Info=True; User ID=rachel;Initial Catalog=users;Data Source=DBPC6;
The SYSDBMSG information mirrors all of the options that you chose during
your prompted connection. Notice that the initialization string is prefixed
with
OLEDB:. When you store
the string for later use, delete this prefix and any initial spaces before
the first listed option.
By storing the initialization string, you can re-use it in the INIT_STRING= option to make automated connections or to specify this option in batch jobs:
init_string="Provider=SQLOLEDB;Password=dbmgr1;Persist Security Info=True;User ID=rachel;Initial Catalog=users;Data Source=DBPC6";Using INIT_STRING= enables you to bypass the prompting window but still gives you the advantages of the OLE DB Services, such as performance optimizations.
Specifying INIT_STRING= overrides any values that were set with the SAS/ACCESS connection options PROVIDER= and PROPERTIES=.
Alias: INIT=.
Default value: YES.
Generally, OLE DB Services is easier to use and more consistent. When OLEDB_SERVICES=YES and a successful prompted connection is made, the complete connection string is returned in the SYSDBMSG macro variable. For more information, see the option INIT_STRING=.
OLEDB_SERVICES= interacts with other connection options. If you have set PROMPT=YES, OLEDB_SERVICES=YES enables you to set more options than you would be able to set by being prompted by the provider's dialog window. If OLEDB_SERVICES=NO, you must specify PROVIDER= first in order for the provider's prompt dialogs to be used. If PROVIDER= is omitted, the SAS/ACCESS engine uses OLE DB Services, regardless of how the OLEDB_SERVICES= option is set.
If the BCP=YES option is set for Microsoft SQL Server data, then OLEDB_SERVICES=NO. OLEDB_SERVICES= also interacts with the PROMPT=, REQUIRED= , and COMPLETE= options. See these options for more information: PROVIDER=, BCP=YES, PROMPT=, REQUIRED=, COMPLETE=.
Default value: NO, if the provider is specified; otherwise, YES.
If a provider name is specified and OLEDB_SERVICES= NO, the OLE DB provider displays a dialog window that contains the connection information and property attributes. If the provider name is omitted or OLEDB_SERVICES=YES, the OLE DB Services displays a dialog window that enables you to select a provider and to specify connection information and property attributes. The dialog window for OLE DB Services is generally preferred over the provider's dialog window because the OLE DB Services window enables you to set options more easily.
If PROMPT=YES, properties that were set with PROPERTIES= will be displayed in the dialog window. This applies both to the provider dialog window and to the OLE DB Services dialog window. You can edit any field before you connect to the data source.
If the provider name is omitted, the SAS/ACCESS engine for OLE DB tries to prompt you for the connection information by using the OLE DB Services dialog window. This applies even if PROMPT=NO and OLEDB_SERVICES= NO. If the provider name is omitted in batch mode, the connection fails.
If you are unsure what to specify for various provider properties, use the PROMPT= option to guide you through the connection process.
See the following options for more information: PROVIDER=,OLEDB_SERVICES=, PROPERTIES=.
Default value: none.
In this example, you specify a user ID and password to connect to a Microsoft SQL Server data source, you would enter:
libname mydblib oledb provider=sqloledb
properties=("User ID"=shala
Password="mypw@hr");
Note: See your provider's documentation for a list and description of
all the properties that your provider supports. ![[cautionend]](../common/images/cautend.gif)
Aliases: PROPS= and PROP=.
Default value: none.
There is no restriction on the length of the name. Put names with non-standard SAS characters (such as spaces, colons, @ signs) in single or double quotations marks.
If you omit this option, you are prompted for the provider name. It is recommended that, if possible, you use the dialog prompts to connect to your data source. The prompts enable you to use an interactive interface to enter the name of the provider, properties, and connection options.
Alias: PROV=.
Default value: none
Microsoft uses a provider-string for its Jet provider in order to determine the type of data source to which it connects. MS Jet currently accepts the following providers; this list is not all-inclusive and is subject to change by Microsoft:
| dBase III, IV, 5.0 | |
| Excel 3.0, 4.0, 5.0, 8.0 | |
| Exchange 4.0 | |
| HTML Export, HTML Import | |
| Jet 2.x, Jet 3.x | |
| Lotus WJ2, WJ3 | |
| Lotus WK1, WK2, WK3, WK4 | |
| Outlook 9.0 | |
| Paradox 3.x, Paradox 4.x, Paradox 5.x, Paradox 7.x | |
| Text |
Providers other than MS Jet also accept other provider-strings.
In the following
SAS/ACCESS LIBNAME statement example, you use the Microsoft
Jet 4.0 provider to access the spreadsheet
Y2Kbudgetworksheet.xls. Notice that you must specify the provider-string
"Excel 8.0" so that MS Jet recognizes
the file as an Excel 8.0 worksheet.
libname y2kbudget oledb provider="Microsoft.Jet.OLEDB.4.0"
properties=('data source'='d:\excel80\Y2Kbudgetworksheet.xls')
provider_string="Excel 8.0"; Default value: NO.
If you specify REQUIRED= YES, the SAS/ACCESS engine for OLE DB tries to connect to the data source by using the properties that were specified in PROPERTIES= . If you specify enough information in order to make a connection, then the connection is made without prompting. Otherwise, a dialog window is displayed to prompt you for the connection options. Options in the dialog window that are not related to the connection are disabled.
REQUIRED= is used only when you connect to the provider directly using the options PROVIDER= and OLEDB_SERVICES=NO in a SAS/ACCESS LIBNAME statement.
For more information, see PROPERTIES=, OLEDB_SERVICES=, and PROVIDER=.
| SAS/ACCESS LIBNAME Options |
When you specify any of the following options on the LIBNAME statement, the option is applied to the data source that the libref represents. When the data source is a relational database, the LIBNAME option applies to all of the objects in the database (such as its tables, views, and indexes) that the libref represents.
The SAS/ACCESS interface to OLE DB supports all of the SAS/ACCESS LIBNAME options listed in SAS/ACCESS LIBNAME Options, except for DBPROMPT=. In addition to the supported options, the following LIBNAME options are used only in the interface to OLE DB or have OLE DB-specific aspects to them:
Default value: NO.
If AUTOCOMMIT=YES, updates are committed (that is, saved) to table as soon as they are submitted, and no rollback is possible. If AUTOCOMMIT=NO, the SAS/ACCESS engine automatically performs the commit when it reaches the end of the file.
For the SAS/ACCESS LIBNAME engine, the default value is NO, if the data source provider supports transactions and it is a connection for updating data. Otherwise, the default value is AUTOCOMMIT= YES (that is, YES for the SQL Procedure Pass-Through Facility and read-only connections).
Default value: NO.
BCP is Microsoft's Bulk Copy facility, and it enables you to efficiently insert rows of data into a DBMS table as a unit. As SAS/ACCESS sends each row of data to BCP, the data is written to an input buffer. When you have inserted all the rows or the buffer reaches a certain size (as determined by the DBCOMMIT= data set option), all of the rows are inserted as a unit into the table, and the data is committed to the table.
Alternatively, you can set the DBCOMMIT=n option to commit rows after every n insertions. See DBCOMMIT= .
If an error occurs, a message is written to the SAS log, and any rows that have been inserted in the table before the error will be rolled back.
If you specify BCP=YES and the PROVIDER= option is set, the SAS/ACCESS engine for OLE DB uses the specified provider. If you specify BCP=YES and PROVIDER= is not set, the engine assumes the value PROVIDER=SQLOLEDB. If you specify BCP=YES, connections that are made through OLE DB Services are not allowed; that is, specifying BCP=YES means that OLEDB_SERVICES=NO.
Default value: <'>value<'>.
For example, if you were to issue an MDX command and the resulting data
set contained a column named
SALARY, the column could contain data values of two types. It could
contain numeric values, such as
50000, or it could contain formatted values, such as
$50,000. Setting the CELLPROP= option determines
how the values are defined and the value of the column.
If CELLPROP=<'>value<'>, the SAS/ACCESS engine for OLE DB tries to return the actual data value; if the value is numeric, then the column is defined as NUMERIC.
If CELLPROP= 'formatted-value', the SAS/ACCESS engine for OLE DB defines the column as CHARACTER and it returns the formatted data values.
It is possible for a column in a result set to contain both NUMERIC
and CHARACTER data values. For example, a data set might return the data values
of
50000,
60000, and
UNKNOWN. SAS data sets cannot contain both types of data. Therefore,
even if you specify CELLPROP=<'>value<'>,
the SAS/ACCESS engine defines the problematic column as CHARACTER and returns
formatted values for that column.
For more information about MDX commands, see Accessing OLE DB for OLAP Data.
Default value: 0 (no timeout).
Alias: TIMEOUT=.
Default value: None.
When your data source is a relational database, not all DBMS drivers support all cursor types. An error is returned if the specified cursor type is not supported.
If CURSOR_TYPE=DYNAMIC, then the cursor reflects all of the changes that are made to the rows in a result set as you scroll around the cursor. The data values and the membership of rows in the cursor can change dynamically on each fetch. The OLE DB properties that are applied to an open row set are DBPROP_OTHERINSERT=TRUE and DBPROP_OTHERUPDATEDELETE=TRUE.
If CURSOR_TYPE=KEYSET_DRIVEN, then the cursor determines which rows belong to the result set when the cursor is opened. However, changes that are made to these rows will be reflected as you scroll around the cursor. The OLE DB properties that are applied to an open row set are DBPROP_OTHERINSERT=FALSE and DBPROP_OTHERUPDATEDELET=TRUE.
If CURSOR_TYPE=STATIC, then the cursor builds the complete result set when the cursor is opened. No changes made to the rows in the result set after the cursor is opened will be reflected in the cursor. Static cursors are read-only. The OLE DB properties that are applied to an open row set are DBPROP_OTHERINSERT=FALSE and DBPROP_OTHERUPDATEDELETE=FALSE.
By default, CURSOR_TYPE= is not set and the provider will use a default. See your provider documentation for more information. See OLE DB programmer reference documentation for details about these properties.
Alias: CURSOR.
Some providers do not handle the following DBMS SQL statement well,
and therefore delete more than the current row with this statement:
DELETE ... WHERE CURRENT OF CURSOR. DELETE_MULT_ROW=
enables SAS/ACCESS to continue if multiple rows were deleted.
Default value: NO
Default value: NO for most data sources. For the following data sources, the default value is YES: MS Access, MS Excel, and MS SQL Server.
For a full description of this option, refer to PRESERVE_COL_NAMES= .
Default value: NO for most data sources. For the following data sources, the default value is YES: MS Access, MS Excel, and MS SQL Server.
For a full description of this option, refer to PRESERVE_TAB_NAMES= .
QUALIFIER= is optional. If it is omitted, you use the default DBMS qualifier name, if any. QUALIFIER= can be used for any DBMS that allows three-part identifier names, such as qualifier.schema.object.
The following LIBNAME statement connects to an MS SQL Server table.
The QUALIFIER= option causes any reference in SAS to
mydblib.employee to be interpreted by OLE DB
as
pcdivision.raoul.employee.
libname mydblib oledb provider=SQLOLEDB
properties=("user id"=dbajorge
"data source"=SQLSERVR)
schema=raoul qualifier=pcdivision;
proc print data=mydblib.employee;
run; "). This is a LIBNAME-only option.
The provider usually specifies the delimiting character. However, when there is a difference between what the provider allows for this character and what the data source allows, the QUOTE_CHAR= option enables you to override the character returned by the provider.
Default value: the option is not set, and it uses the quotation character returned by the provider.
Default value: Set by the data provider.
The arguments for READ_ISOLATION_LEVEL= indicate the following: S = Serializable, RR = Repeatable Read, RC = Read Committed, and RU = Read Uncommitted.
OLE DB supports five levels of isolation, which are described in detail in READ_ISOLATION_LEVEL=. The degree of isolation identifies
The READ_ISOLATION_LEVEL= option applies only when reading a data source, such as a DBMS table or view. By default, this option is not set. The provider sets the default value. If READ_LOCK_TYPE= is not set to ROW, then READ_ISOLATION_LEVEL= is ignored. See also READ_LOCK_TYPE=.
Alias: RIL=.
The value ROW specifies that a row or set of rows will be locked. SAS/ACCESS software uses the READ_ISOLATION_LEVEL= option to determine which rows will be locked. Currently, the only valid locking that is allowed is through the READ_ISOLATION_LEVEL= option; therefore, READ_LOCK_TYPE= only allows a value of ROW. See also READ_ISOLATION_LEVEL=.
For a full description of this option, refer to READ_LOCK_TYPE= .
Default value: 1, so that only one row is retrieved at a time.
The higher the value for ROWSET_SIZE=, the more rows that the SAS/ACCESS engine for OLE DB retrieves in one fetch operation. This option reduces the amount of I/O that is used and can help improve performance. However, because SAS software stores the rows in memory, higher values for ROWSET_SIZE= use more memory. In addition, if too many rows are selected at once, then the rows that are returned to the SAS application might be out of date.
Alias: ROWSET=.
SCHEMA= is both a LIBNAME and data set option. SHEMA= is optional. If it is omitted, you connect to the default schema.
In the following LIBNAME statement, the SCHEMA= option causes any reference
in SAS to
mydblib.employee
to be interpreted by the OLE DB provider (ORACLE) as
raoul.employee.
libname mydblib oledb provider=msdaora
properties=("user id"=dbajorge password=dbajorge99
"data source"="oracle_loc")
schema=raoul qualifier=pcdivision;
proc print data=mydblib.schedule;
run;
Alias: OWNER=.
Default value: NO.
If STRINGDATES=YES, SAS/ACCESS reads datetime values as character strings. If STRINGDATES=NO, SAS/ACCESS reads datetimes values as numeric date values.
Alias: STRDATES.
The arguments for UPDATE_ISOLATION_LEVEL= indicate the following: S = Serializable, RC = Read Committed, and RR = Repeatable Read.
OLE DB supports four isolation levels, which are described in detail in UPDATE_ISOLATION_LEVEL=. The degree of isolation identifies
By default, UPDATE_ISOLATION_LEVEL= in not set. The provider sets the default value. If UPDATE_LOCK_TYPE= is not set to ROW, then UPDATE_ISOLATION_LEVEL= is ignored. See also UPDATE_LOCK_TYPE=.
Alias: UIL=.
Default value: ROW.
The UPDATE_ISOLATION_LEVEL= option applies only when updating a DBMS table or view. The value ROW specifies that a row or set of rows will be locked. To determine which rows will be locked, SAS/ACCESS software uses the UPDATE_ISOLATION_LEVEL= option. See also UPDATE_ISOLATION_LEVEL=.
Default value: NO.
Some providers do not handle the following DBMS SQL statement well,
and therefore update more than the current row with this statement:
UPDATE ... WHERE CURRENT OF CURSOR. UPDATE_MULT_ROW=
enables SAS/ACCESS to continue if multiple rows were updated.
| Examples |
In this example, the libref MYDBLIB uses the OLE DB engine to connect to an MS SQL Server database. The SAS/ACCESS-engine connection options are PROVIDER= and PROPERTIES=, and the LIBNAME options are SCHEMA= and QUALIFIER.
libname mydblib oledb provider=sqloledb
properties=("User ID=shala" Password=myhrpw
"data source"=dept203
"initial catalog"=mgronly)
schema=rfcmgrs qualifier=hrdiv;
proc print data=mydblib.customers;
where state='CA';
run;
In this example, the libref MYDBLIB uses the SAS/ACCESS engine for OLE DB to connect to an ORACLE database. Prompting is enabled, and when the dialog window opens, you supply information for the user ID, password, and data source. The SAS/ACCESS-engine connection options are PROVIDER=, PROPERTIES=, and PROMPT=.
libname mydblib oledb provider=msdaora properties=("User ID"=fred
password=freddie "data source"="v2o7223.world")
prompt=yes preserve_tab_names=yes preserve_col_names=yes;
proc print data=mydblib.customers;
where state='CA';
run;For an example of the OLE DB Services dialog window, which prompts
you for more information, see Connecting with OLE DB Services.
|
Chapter Contents |
Previous |
Next |
 Top of Page |
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.