Connection String Syntax
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.
Basic Connection String Format
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.
keyword1=value; keyword2=value
The Provider Keyword
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.
Provider=MSDASQL
The value can also be set to the ProgID of the provider, which may have a version attached to it.
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.
Driver={SQL Server};Server={localhost};Trusted_Connection={Yes};Database={Northwind};
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.
Provider=;Database=MyDatabase
Specifying a Keyword
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.
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.
Jet OLE DB:System Database=c:\system.mda
The keyword is "Jet OLE DB:System Database".
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.
Verification==Security=True
The keyword is "Verification=Security".
If multiple equal signs appear, each one must be preceded by an additional equal sign.
Many====One=Valid
The keyword is "Many==One".
TooMany===False
The keyword is "TooMany=".
Setting Keyword Values
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.
Short Property Values
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:
VARIANT_TRUE
VARIANT_FALSE
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.
Cache Authentication=True
Another example is the Prompt (DBPROP_INIT_PROMPT) property, for which the OLE DB specification defines the following values:
DBPROMPT_PROMPT
DBPROMPT_COMPLETE
DBPROMPT_COMPLETEREQUIRED
DBPROMPT_NOPROMPT
Again, the prefix "DBPROMPT_" is removed, and mixed case can be used for the short values, as follows:
Prompt
Complete
CompleteRequired
NoPrompt
Prompt=Complete
Some of the values defined for the Mode (DBPROP_INIT_MODE) property include the following:
DB_MODE_SHARE_DENY_READ
DB_MODE_SHARE_DENY_WRITE
DB_MODE_SHARE_EXCLUSIVE
The underscores after the prefix "DB_MODE_SHARE_" are replaced with spaces in the short values, as follows:
Deny Read
Deny Write
Share Exclusive
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.
Setting Numeric Values
To set a numeric property value, use a decimal, hex, or octal value, as in the following examples.
Window Handle=123
Window Handle=0x123
Window Handle=0123
Setting Values That Use Reserved Characters
To include values that contain a semicolon, single-quote character, or double-quote character, the value must be enclosed in double quotes.
ExtendedProperties="Data Source='localhost';Integrated Security='SSPI';Initial Catalog='Northwind'"
If the value contains both a semicolon and a double-quote character, the value can be enclosed in single quotes.
ExtendedProperties='Integrated Security="SSPI";Databse="My Northwind DB"'
The value for the ExtendedProperties keyword is Integrated Security="SSPI";Databse="My Northwind DB"
The single quote is also useful if the value begins with a double-quote character.
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.
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.
NewRecordsCaption='"Company''s "new" customer"'
The value is "Company's "new" customer".
NewRecordsCaption="""Company's ""new"" customer"""
The value is "Company's "new" customer".
Setting Values That Use Spaces
Any leading or trailing spaces around a keyword or value are ignored. However, spaces within a keyword or value are allowed and recognized.
MyKeyword=My Value
The value for the MyKeyword keyword is My[space]Value.
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.
MyKeyword=' My Value '
The value for the MyKeyword keyword is [space]My[space]Value[space][space].
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.
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.
Returning Multiple Values
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.
Mode=Deny Write|Deny Read
Listing Keywords Multiple Times
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.
Provider=MSDASQL;Location=Northwind;Cache Authentication=True;Prompt=Complete;Location=Customers
In this case, the Location property will be set to Customers.
Setting the Window Handle Property
To set the Window Handle (DBPROP_INIT_HWND) property in a connection string, a long integer value is typically used.
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.
Format Limitations
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.DBType=3|5 Connect Timeout=(34*23)/8Each 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.
A Note to Provider Writers
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.