MDAC 2.5 SDK - OLE DB Programmer's Reference
Chapter 20: OLE DB Core Components
A connection string is a string version of the initialization properties needed to connect to a data store and enables you to easily store connection information within your application or to pass it between applications. Without a connection string, you would be required to store or pass a complex array of structures to access data. When accessing OLE DB directly, using IDataInitialize, the connection string is passed as a parameter to create an OLE DB data source object.
Note In ADO, connection strings are retrieved and set using the ConnectionString property to create a Connection object.
In some cases, rather than using the standard data link user interface, it might be desirable to build a connection string within an application—for example, if you want to provide a custom user interface. In these cases, it is important that the connection string syntax adhere to the formatting rules described in the following sections.
The basic format of a connection string is based on the ODBC connection string. The string consists of a series of keyword/value pairs separated by semicolons. The equal sign (=) connects each keyword and its value.
Example keyword1=value; keyword2=value
The Provider keyword identifies the OLE DB provider to be used. To specify your OLE DB provider, set the value of the Provider keyword to the VersionIndependentProgID value of the provider.
Example Provider=MSDASQL
The value can also be set to the ProgID of the provider, which may have a version attached to it.
Example Provider=MSDASQL.1
If two versions of a provider are installed on the same system, use the ProgID to specify exactly which version to use. If two versions are installed on a system and the VersionIndependentProgID value is specified, the most recent version of the provider is used.
If no Provider keyword is in the string, the OLE DB Provider for ODBC (MSDASQL) is the default value. This provides backward compatibility with ODBC connection strings. The ODBC connection string in the following example can be passed in, and it will successfully connect.
Example Driver={SQL Server};Server=MyServer;db=pubs;uid=sa;pwd=MyPassword
If the Provider keyword is specified but names a provider that does not exist or is invalid, as in the following example, an error is returned.
Example Provider=;Database=MyDatabase
To identify a keyword used after the Provider keyword, use the property description of the OLE DB initialization property that you want to set. For example, the property description of the standard OLE DB initialization property DBPROP_INIT_LOCATION is Location. Therefore, to include this property in a connection string, use the keyword Location.
Example Provider=MSDASQL;Location=Pubs
Note Standard OLE DB properties are documented under the OLE DB Initialization property group (DBPROPSET_DBINIT) in Appendix C, "OLE DB Properties." For information about provider-specific initialization properties, see the documentation that came with your provider.
Keywords can contain any printable character except for the equal sign (=). All of the following examples are correct.
Example Jet OLE DB:System Database=c:\system.mda
The keyword is "Jet OLE DB:System Database".
Example Authentication;Info=Column 5
The keyword is "Authentication;Info".
If a keyword contains an equal sign (=), it must be preceded by an additional equal sign to indicate that it is part of the keyword.
Example Verification==Security=True
The keyword is "Verification=Security".
If multiple equal signs appear, each one must be preceded by an additional equal sign.
Example Many====One=Valid
The keyword is "Many==One".
Example TooMany===False
The keyword is "TooMany=".
The value of a keyword must be specified as a string. The value depends on the property being set and the value that the OLE DB provider expects.
The Data Link API uses a shortened, "friendlier" version of the property values defined in the OLE DB specification. The short value of a property is derived by removing the prefix from the documented property value and replacing any underscores with spaces. For example, the OLE DB specification defines the following values for the Cache Authentication (DBPROP_AUTH_CACHE_AUTHINFO) property:
The prefix "VARIANT_" is removed and the short values are TRUE and FALSE.
In connection strings, the short property values are case-insensitive, so the values can be made more readable by using True and False.
Example Cache Authentication=True
Another example is the Prompt (DBPROP_INIT_PROMPT) property, for which the OLE DB specification defines the following values:
Again, the prefix "DBPROMPT_" is removed, and mixed case can be used for the short values, as follows:
Example Prompt=Complete
Some of the values defined for the Mode (DBPROP_INIT_MODE) property include the following:
The underscores after the prefix "DB_MODE_SHARE_" are replaced with spaces in the short values, as follows:
These shortened property values apply only to those properties that are documented in the OLE DB specification. Different OLE DB data providers may use customized properties and property values. The OLE DB Initialization Properties: Quick Reference lists the property description and the short property value (where applicable) for each OLE DB initialization property.
To set a numeric property value, use a decimal, hex, or octal value.
Examples:
Window Handle=123
Window Handle=0x123
Window Handle=0123
To include values that contain a semicolon, single-quote character, or double-quote character, the value must be enclosed in double quotes.
Example ExtendedProperties="UID=sa;pwd=sa;Databse=MyDB"
If the value contains both a semicolon and a double-quote character, the value can be enclosed in single quotes.
Example ExtendedProperties='UID=sa;pwd=sa;Databse="My DB"'
The value for the UID keyword is UID=sa;pwd=sa;Databse="My DB".
The single quote is also useful if the value begins with a double-quote character.
Example DataSchema='"MyCustTable"'
The value for the DataSchema keyword is "MyCustTable".
Conversely, the double quote can be used if the value begins with a single quote.
Example DataSchema="'MyOtherCustTable'"
The value is 'MyOtherCustTable'.
If the value contains both single-quote and double-quote characters, the quote character used to enclose the value must be doubled each time it occurs within the value.
Example NewRecordsCaption='"Company''s "new" customer"'
The value is "Company's "new" customer".
Example NewRecordsCaption="""Company's ""new"" customer"""
The value is "Company's "new" customer".
Any leading or trailing spaces around a keyword or value are ignored. However, spaces within a keyword or value are allowed and recognized.
Example MyKeyword=My Value
The value for the MyKeyword keyword is My[space]Value.
Example MyKeyword= My Value ;MyNextValue=Value
The value for the MyKeyword keyword is My[space]Value.
To include preceding or trailing spaces in the value, the value must be enclosed in either single quotes or double quotes.
Example MyKeyword=' My Value '
The value for the MyKeyword keyword is [space]My[space]Value[space][space].
Example MyKeyword=" My Value "
The value for the MyKeyword keyword is [space][space]My[space]Value[space].
If the keyword does not correspond to a standard OLE DB initialization property (in which case the keyword value is placed in the Extended Properties (DBPROP_INIT_PROVIDERSTRING) property), the spaces around the value will be included in the value even though quote marks are not used. This is to support backward compatibility for ODBC connection strings. Trailing spaces after keywords might also be preserved.
Example Extended Properties="Driver=SQL Server;uid= MyName;pwd= MyPassword"
The value for the uid keyword is [space]MyName.
The value for the pwd keyword is [space]MyPassword.
For standard OLE DB initialization properties that can return multiple values, such as the Mode property, each value returned is separated with a pipe (|) character. The pipe character can have spaces around it or not.
Example Mode=Deny Write|Deny Read
If a specific keyword in a keyword=value pair occurs multiple times in a connection string, the last occurrence listed is used in the value set.
Example Provider=MSDASQL;Location=Pubs;Cache Authentication=True;Prompt=Complete;Location=Customers
In this case, the Location property will be set to Customers.
One exception to the preceding rule is the Provider keyword. If this keyword occurs multiple times in the string, the first occurrence is used.
Example Provider=MSDASQL;Location=Pubs; Provider=SQLOLEDB
In this case, MSDASQL will be used.
To set the Window Handle (DBPROP_INIT_HWND) property in a connection string, a long integer value is typically used.
Example Window Handle=14829
Note When the string is returned, the Window Handle keyword will not be included in the string because Window Handle is not a property that should be persisted.
The connection string format has some limitations on what initialization settings it can persist and set.
COLID Properties
The connection string format does not support the ability to persist provider-specific properties that require a COLID for extra information.
Mathematical Operations
No mathematical operations will be performed on the value set. For example, setting a property to "2+3" will be set to the constant "2+3". If the property is not a string data type, it will be an illegal value. Similarly, the C technique of joining items together in a logical OR by using the pipe (|) character is not allowed.
Example DBType=3|5
Example Connection Timeout=(34*23)/8
Each of the preceding examples is illegal, assuming the property data types are not string data types. If they are string data types, they will be set to the constant specified.
If you are writing an OLE DB data provider, your implementation is independent of any of the formatting rules for connection strings. The OLE DB Data Link core component implements and validates these rules for you.
See Also